Using Views with Doctrine

Tags: tutorial, views

Posted about 1 year ago by jwage

I've seen a few requests recently on how you can use a view with Doctrine. This is very easy and I've also learned a few neat tricks that you can do to accomplish abnormal things while writing this article.

Creating the View

First I will demonstrate how you can turn a normal Doctrine_Query instance in to a view. This is just as easy as creating an instance of Doctrine_View and setting a reference between the query and the view.

<?php

$q = Doctrine::getTable('BlogPost')
  ->createQuery('p')
  ->select('p.*, COUNT(c.id) as num_comments')
  ->leftJoin('p.Comments c')
  ->orderBy('p.id DESC')
  ->groupBy('p.id');

$view = new Doctrine_View($q, 'test_view');

To create the view in the database you can call the Doctrine_View::create() method.

<?php

$view->create();

You can drop the view just the same by calling the Doctrine_View::drop() method.

<?php

$view->drop();

Executing the View

Now when the Doctrine_Query instance above is executed, it will execute the SQL for the view instead of parsing the DQL, generating the SQL and executing it.

<?php

$blogPosts = $q->execute();

Executing the above would execute the following SQL query.

SELECT 
* 
FROM test_view

Tweaking the View

Now here is where things get interesting. Say we wanted to take the SQL that the above Doctrine_Query generates, and modify it slightly with some custom SQL that otherwise could not make it through the DQL parser.

We can get the SQL from the query, modify it, then manually create the view in our database.

<?php

echo $q->getSql();

The above would output the following SQL.

SELECT 
b.id AS b__id, 
b.title AS b__title, 
b.excerpt AS b__excerpt, 
b.body AS b__body, 
COUNT(c.id) AS c__0 
FROM blog_post b 
LEFT JOIN comment c ON b.id = c.blog_post_id 
GROUP BY b.id 
ORDER BY b.id DESC

Now lets say we wanted to add something to the SQL that is proprietary to your DBMS, or is some complex SQL that won't make it through the DQL parser. We can modify the above SQL then re-create the view with that SQL manually. Let's make a simple change and add the USE INDEX keyword to force MySQL to use a certain index for the query.

The example I have chosen is a very simple one only to demonstrate the capabilities. This example may not be a real world scenario for you. The only purpose of me showing this is to open a door for you to solve potential problems for you in the future.

SELECT 
b.id AS b__id, 
b.title AS b__title, 
b.excerpt AS b__excerpt, 
b.body AS b__body, 
COUNT(c.id) AS c__0 
FROM blog_post b 
LEFT JOIN comment c USE INDEX (blog_post_id_idx) ON b.id = c.blog_post_id 
GROUP BY b.id 
ORDER BY b.id DESC;

Now lets take this query and manually create the view with it.

We must first drop the view as we already created it once in a previous step. This is just as easy as issuing the DROP VIEW command to MySQL. Afterward, re-create the view again with the modified SQL.

DROP   VIEW test_view;
CREATE  VIEW test_view AS SELECT 
b.id AS b__id, 
b.title AS b__title, 
b.excerpt AS b__excerpt, 
b.body AS b__body, 
COUNT(c.id) AS c__0 
FROM blog_post b 
LEFT JOIN comment c USE INDEX (blog_post_id_idx) ON b.id = c.blog_post_id 
GROUP BY b.id 
ORDER BY b.id DESC;

Now when we execute the code in the first part of this article it will execute the view which contains the customized SQL.

<?php

$blogPosts = $q->execute();

If you customize the SQL, it must maintain the same structure, aliases, etc. in order for Doctrine to be able to hydrate the data in to the object graph.

That is it! Now you can easily use some custom SQL in your queries as views. The benefit of using a view is that it is easily reusable and it is much faster than executing a normal query in most cases.


Comments (17) [ add comment ]

Very cool thing Posted by annis about about 1 year ago.

Since you said you wanted to blog more, I'm really enjoying coming here to read. Keep at this and this is just great! Thanks!

Thanks Jon Posted by J. Philip about about 1 year ago.

I normally create views with Sqlyog which has good table and field autocomplete and drag/drop tables to automatically generate joins, but I like the idea to work with DQL and the models.

Questions: if I use the query $q = Doctrine_Query::create() ->from('tblStudy s') ->select('s.name, s.abbreviation');

The view generated is: CREATE VIEW v_demo AS SELECT t.name AS t__name,t.abbreviation AS t__abbreviation FROM tbl_study t

Is there a way to query this view later with DQL? I guess this would require a model class to be generated when the view is created.

If the view is not meant to be queried by DQL later but used by someone else, I would want to be be able to force my own aliases instead of the 't__' prefixed fields.

One thing I also meant to tell you for some time, the HTML for the feed of this blog is escaped, so it is unreadable in my feed aggregator (RSS Bandit), you may want to change that.

@jphilip Posted by jwage about about 1 year ago.

You could define the view, then define a model that matches the view, yes. I've tested this and it works. Of course that model is then read only :)

Interface Posted by Nicolas about about 1 year ago.

Something maybe cool : ... $view->create(); $entry = $view->addSomething('f1', 'f2', 'f3'); $entry->save(); or : sf doctrine:generate-admin app myView Maybe in relation with synchronizeWithArray(), maybe impossible ?

Only execution of an existing view in database. Howto? Posted by Mikhus about about 1 year ago.

OK, but I already have a view in my database and it contains a very complex SQL query inside. All I need is to fetch the data from the view. I mean I do not need to create or drop it, so it's not very clear how I can use Doctrine_View class to execute an existing view. I will be appreciated for any help.

Thanks in advance, Mike

Subset of records Posted by VM about 11 months ago.

And what about fetching a subset of records from the view based on some criteria in where statement?

Thanks.

Only execution of an existing view in database. Howto? Posted by dirk about 11 months ago.

The question of Mikhus is the most important about views in doctrine. Without the ability to use views that are stored in the database this class makes no sense in my opinion. Or did I miss anything? If anyone can help this would be great.

Selecting from view Posted by Vik about 8 months ago.

Funny thing, but the most used feature - SELECT is not answered clearly neither here nor in the main documentation page. Two people - Dirk and Mikhus - already asked that, but no one answered that... There isn't a clear answer anywhere: is it possible to select from already existing view or not? If it is possible, than how? I think documentation should cover essential things... Actually documentation is the only downside of Doctrine for me - it's a constant searching for 'is it possible to ...' and 'how can I...' answers.

UNION ALL statement Posted by Chris about 7 months ago.

Ive got 4 tables: tbl_user, tbl_media, tbl_user_media_comment umc, and tbl_user_media_like uml. i want to create a feed out of umc and uml based on the created_at column. should i create a view for this?

Really what I am trying to do is:

SELECT 'song_comment' as user_action, null as artist_id, user_id, media_id, null as ip_address, comment, created_at FROM tbl_user_media_comment UNION ALL SELECT 'song_like' as user_action, null as artist_id, user_id, media_id, null as ip_address, null as comment, created_at FROM tbl_user_media_like ORDER BY created_at DESC

But even the creation of a view requires a single table for the ->from() statement....

How can this be implemented in a view?

Still need to understand unanswered question of how to add a view to the model Posted by Confused New User about 5 months ago.

Hi, I'd like to see an answer to the question of how to add a view to the Doctrine model so I can join to it with a query. The example presented herein is fine, but I need to go further and actually use the view in a discriminating manner that will let me join to it and limit the selection in a new query. How can I accomplish this? If I were to manually create the view in my db and wanted to add a base class, how would I go about doing that? Would I use a setTableDefinition function, or would I use setViewDefinition? If y'all could provide more detailed and explicit documentation on views, it'd be REALLY helpful. Thanks.

@jwage about read only model Posted by kp about 5 months ago.

If you use the doctrine tasks as they are, then yes your model is read only, because you'll end up blowing away your views.

However, you could write your own custom task that adds the views after the database tables and models have been built. After the database is built, the task would drop the table, and replace it with a view using DQL as per your example.

Using an existing view Posted by Iain about 4 months ago.

Hi, I'm still not clear how to use a view that already exists in the db.

Every example I've found shows creating a view from a query, then using it by the reference to the newly created view.

Please can you clarify how to load data from a pre-existing view?

Thanks a lot!

Using an existing view Posted by Morpheu5 about 3 months ago.

Hi, sorry to bring this up again but it seems I'm having the same problem here. I set up a Doctrine_Record to match my existing view but it seems I have no success when I go for Doctrine_Core::getTable('MyView')... I'd really appreciate some documentation about this.

Hint Posted by Morpheu5 about 3 months ago.

Apparently i didn't have an 'id' attribute defined in my view. Well, that's expected, since I don't have any attribute that can qualify as an 'id'. Still, as soon as I artificially elected one as such, ::getTable('MyView') returned a bunch of records from the view.

Now: how am I supposed to define an id attribute if my view doesn't have one?

Solved? Posted by Morpheu5 about 3 months ago.

Well (sorry for the spam -.-) that was "easy". I made a primary key composed of all the attributes.

Sounds weird, but still...

Using an existing view Posted by pavel about about 1 month ago.

Hi again. Nice article. Can anybody describe how to use existing views in Doctrine?

Thanks in advance

Using an existing view Posted by le6o about 18 days ago.

I came across this problem today, where the view already existed in the db.

The confusing part was having to modify the view to contain an 'id' column to act as a primary key of sorts.

Then all I needed to do was generate a model for the view.

After that it was as simple as querying a normal table.

e.g.

$q = Doctrine_Query::create()->from("OhWhatALovelyView");

Create Comment