Hi,

I have a few questions concerning weblogs, that I hope someone can answer for me:

1) It appears that multiple weblogs can be placed on any page. Is this correct? If so, this appears to be a relatively unique capability among CMS packages. I have found one CMS package that has this capability. Is this a relatively unique capability featured of EE, or am I missing something?

2) It looks like that it is possible to do an equi-join (relationship) between weblogs (or tables). It also looks like you can add expressions to the relationship? Does EE generate sargable SQL statements that can be easily optimized by the SQL RDBM optimizer, when expressions are used?

3) Where can I find the most complete documentation on defining and using weblogs.

Thanks for your help.

Rich

It’s fairly unique- but not entirely.  Of course- EE’s conceptualization of a ‘weblog’ is different.  I prefer to think of them as ‘data containers’ since ‘weblog’ carries a lot of assumptions.  But short answer- it’s definitely a bonus in EE, but it’s not entirely unique.

You lost me a bit on the ‘add expressions to the relationship’- but check out this kblog entry for a better understanding of what’s going on with relationships.  Basically?  When you create one, EE using the exp_relationships table to store a serialized array of the data for the joined entries.  When the data are displayed- it’s pulling from that- rather than doing those queries on the fly.

As for documentation on the weblog tag- the weblog tag docs and Weblog Admin CP docs would be the place to start.  But to get a really good grasp of things- I’d recommend running through the whole Getting started section- including working through the tutorial.  You need to understand EE as a whole- the templates, the url structure, etc- to really start getting a feel for the flexibility EE offers.

If you haven’t already- I’d recommend taking one of the trial options for a spin.  If you’re curious about the backend, the Core is free and the code open, so you can poke around and see what all is going on there.

That help?

Robin Sowell - 04 May 2008 09:05 AM

You lost me a bit on the ‘add expressions to the relationship’- but check out this kblog entry for a better understanding of what’s going on with relationships.  Basically?  When you create one, EE using the exp_relationships table to store a serialized array of the data for the joined entries.  When the data are displayed- it’s pulling from that- rather than doing those queries on the fly.

Lots of help Robin. Thanks.

Does the EE engine create a single SQL statement, for a relationship) that joins the tables, and can be optimized by the SQL RBMS engine (e.g. MySQL)? Is it possible to relate three tables? It is not obvious from the documenation. Thanks for your assistance.

Rich

Hey Rich -

A few comments as a former MSAccess/VB guy.

EE isn’t a RDMS even though it shares some characteristics of one.

The structures you create using the EE control panel to hold content (weblogs, categories, fields etc) don’t map 1:1 to the MySQL database that EE runs on.  For example - all weblog content is stored in the same MySQL tables, regardless if you use 1 or 10 weblogs on your site.

I think the best route for you to take is to install EE and look the underlying database structure.  I almost wonder if an investigation of CodeIgniter might also be worthwhile - as a way to save some of the PHP development time you’ve mentioned but also be more in control of things at the database level.

Michael Boyink - 04 May 2008 09:50 AM

Hey Rich -

A few comments as a former MSAccess/VB guy.

EE isn’t a RDMS even though it shares some characteristics of one.

The structures you create using the EE control panel to hold content (weblogs, categories, fields etc) don’t map 1:1 to the MySQL database that EE runs on.  For example - all weblog content is stored in the same MySQL tables, regardless if you use 1 or 10 weblogs on your site.

I think the best route for you to take is to install EE and look the underlying database structure.  I almost wonder if an investigation of CodeIgniter might also be worthwhile - as a way to save some of the PHP development time you’ve mentioned but also be more in control of things at the database level.

Hi Michael,

I guess some understanding of EE’s architecture may help me a bit.

1) It is my understanding that EE is a development platform (CMS), that accesses data from a MySQL database.

2) Each weblog, as far as I understand, maps to a single MySQL table. In other words, a weblog is a user view of the table.

3) Since most applications require a logical database of multiple SQL tables in order to efficiently store data, most EE applications will require the definition of multiple weblogs, which appear to be in actuality multiple MySQL relational tables. This is commonly referred to as the the logical design. Weblogs (tables) are related to each other by unique keys in the MySQL tables.

4) EE allows the definition of views (same as a SQL View), by relating two (or more?) weblogs (tables).

5) EE will generate the necessary SQL statements in order to retrieve the data from multiple tables. If this is true, I am attempting to find out if the generating SQL statement is a series of single table SQL statements or SQL join statements, since joins can be optimized and yield far better performance than single table SELECT statements.

6) The weblog relationship views can be easily displayed on an EE form, without programming code.

7) For more advanced manipulation/filtering of data, it may be necessary to use CI programming code, in the same what Access may require Visual Basic.

I think that EE’s data handling is probably rudimentary compared to Access, however, it appears that EE can define Views via the relationship specification.

I hope this breaks it down a bit, and if you can tell me where my understanding is wrong, it will help facilitate my evaluation process. Thanks again for your assistance.

Rich

richrf - 04 May 2008 10:06 AM

1) It is my understanding that EE is a development platform (CMS), that accesses data from a MySQL database.

Yes.

richrf - 04 May 2008 10:06 AM

2) Each weblog, as far as I understand, maps to a single MySQL table. In other words, a weblog is a user view of the table.

No.  As mentioned above all weblog data - regardless of the number of weblogs comprising a site - get stored in the same MySQL tables.  This is why I suggested a look at the EE data structure.

richrf - 04 May 2008 10:06 AM

3) Since most applications require a logical database of multiple SQL tables in order to efficiently store data, most EE applications will require the definition of multiple weblogs, which appear to be in actuality multiple MySQL relational tables. This is commonly referred to as the the logical design. Weblogs (tables) are related to each other by unique keys in the MySQL tables.

Yes and no, as per above.  Weblogs do not map 1:1 to MySQL tables.

richrf - 04 May 2008 10:06 AM

4) EE allows the definition of views (same as a SQL View), by relating two (or more?) weblogs (tables).

EE allows templates to pull content from multiple weblogs. I’m not sure if that’s what you would call a “view”.

Additionally, those weblogs being pulled to the same template may or may not use EE’s relationship feature.  They could be “related” using categories, or may not have a data relationship at all.

richrf - 04 May 2008 10:06 AM

5) EE will generate the necessary SQL statements in order to retrieve the data from multiple tables. If this is true, I am attempting to find out if the generating SQL statement is a series of single table SQL statements or SQL join statements, since joins can be optimized and yield far better performance than single table SELECT statements.

EE does generate the necessary SQL statements to pull the content as you’ve requested in a template.  However you will not - at least from within the EE interface -  be able to do any query optimization on the SQL statements it generates.  If you want to change those SQL statements you’d be into hacking EE core code, which I would not recommend.  I also have great faith in the EE developers that those queries are as optimized as they can be.

richrf - 04 May 2008 10:06 AM

6) The weblog relationship views can be easily displayed on an EE form, without programming code.

Displaying related content on a EE template requires the use of EE tags - so I’m not sure if that is or isn’t “programming code”.  It won’t require PHP but will require knowledge of the EE tagset.

richrf - 04 May 2008 10:06 AM

7) For more advanced manipulation/filtering of data, it may be necessary to use CI programming code, in the same what Access may require Visual Basic.

The current version of EE is not CI based - that’s what 2.0 brings to the table.  However if you need to get content out of the EE database in a way that the default EE tags won’t provide you can use the current query module to store SQL statements directly in your EE template.

richrf - 04 May 2008 10:06 AM

I think that EE’s data handling is probably rudimentary compared to Access, however, it appears that EE can define Views via the relationship specification.

Again, EE is not a RDMS.  The current relationship abilities can really be described as letting you establish multiple 1:1 joins between weblog posts.  So in the case of a company with sales, you’d have a weblog holding company information and a weblog to hold sales information (and again, from a MySQL perspective all this data is in the same table).  From an EE perspective you’d chose the parent company from each post in the sales weblog.  Each sale would have only one company.  Then you can use either related entries (to show which company is related to the sale) or reverse related entries (to show sales for a given company). 

richrf - 04 May 2008 10:06 AM

I hope this breaks it down a bit, and if you can tell me where my understanding is wrong, it will help facilitate my evaluation process. Thanks again for your assistance.

I know the mindset you’re coming from having done 5-6 years of Access and VB work.  But I find that ~98% of the time I just work in EE’s world, and pay no attention to the underlying database structure or worry about the SQL statements that EE is generating.  The other 2% I’ve been able to use the query module and mine the EE database directly to get the content that I’ve needed.

Hi Michael,

Thanks for a reply. Do you know of any EE documentation which describes in detail:

1) The internal relationships between weblogs and underlying SQL tables. I think we are having some semantic issues with definitions, and it would help to have the actual technical documentation.

2) SQL code generation by EE.

Weblog capabilities are important to because it defines to the extent that EE can automatically manage multiple table relationships, and to what extent how may have to revert to CI/PHP coding for my application (this relates directly to the initial and ongoing costs of maintaining the application).

SQL optimization is important to me, because my site may scale to have tens of thousands of unique visitors a day. Poorly optimized SQL commands will imper the ability for me to scale the application without relying on expensive hardware resources. I’ve already run into this problem with other applications that I have reviewed as a possible basis for my project.

Thanks again for all of your help.

The available developer docs are here.

For more specific questions than those answer I’ll have to hope one of the devs can respond…;)