eZ Community » Forums » Discussions » Make life easier with SQL Views
expandshrink

Thursday 15 November 2012 12:39:06 am - 7 replies

» Read full blog post

Introduction

Every now and then, I find myself exploring the database behind eZ Publish, trying to do some obscure queries, or just because it is quicker to do the queries in a database tool than to write a script to do it and use the regular fetch functions.

There are more than 100 tables, and you often have to combine a few of them. You may have to write complex and long queries. Good SQL tools have the option to store your favourite queries, it saves you the typing, but the queries are still long and complex.

Using SQL views, you can use the result set of such a query and  create a virtual table with always up-to-date data. Now you can treat a complex query as if it were a simple table. 

Thursday 15 November 2012 10:25:26 am

Not sure how this would evolve with the introduction for new storage engine, but views can be great both for readability of your code and for speedups (materialized views are spported in "some" databases which are actually copied tables with transparent refresh).

The main thing is to make sure that all the different where conditions are properly taken into account (such as eg. the content class status for the view used for content classes) when creating the views.

Maybe we could share interesting views here on the forums (or as an extension on projects.ez.no) and have the community comment/improve them...

Friday 16 November 2012 1:55:48 pm

What's the difference between the first view and the second one? Looks like it is the same, but with only different names.

Friday 16 November 2012 11:42:57 pm

Quote from Thiago Campos Viana :

What's the difference between the first view and the second one? Looks like it is the same, but with only different names.

You are right, the second should have read  "CREATE TABLE..." , it has been edited and corrected now.

Thanks for you mentioning!

Friday 16 November 2012 11:46:19 pm

Quote from Gaetano Giunta :

...

Maybe we could share interesting views here on the forums (or as an extension on projects.ez.no) and have the community comment/improve them...

It seems that projects.ez.no is in hibernation, no new projects approved, no response...

Monday 19 November 2012 11:22:48 am

Quote from Harry Oosterveen :
Quote from Gaetano Giunta :

...

Maybe we could share interesting views here on the forums (or as an extension on projects.ez.no) and have the community comment/improve them...

It seems that projects.ez.no is in hibernation, no new projects approved, no response...

Hi Harry and sorry for the radio silence.

Your project has now been approved!

Cheers,

Thursday 22 November 2012 11:26:10 pm

Quote from Nicolas Pastorino :
Quote from Harry Oosterveen :
Quote from Gaetano Giunta :

Hi Harry and sorry for the radio silence.

Your project has now been approved!

Cheers,

Good, thanks you, and keep up the good work.

Now I can start working on the next project happy.gif Emoticon

Groeten,

Harry

Modified on Thursday 22 November 2012 11:26:36 pm by Harry Oosterveen

Friday 23 November 2012 9:18:11 am

Quote from Harry Oosterveen :
Quote from Nicolas Pastorino :
Quote from Harry Oosterveen :
Quote from Gaetano Giunta :

Hi Harry and sorry for the radio silence.

Your project has now been approved!

Cheers,

Good, thanks you, and keep up the good work.

Now I can start working on the next project happy.gif Emoticon

Groeten,

Harry

Looking forward to this happy.gif Emoticon

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from