Using Views with Doctrine

Tags: tutorial, views

Posted over 2 years 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 (25) [ add comment ]

Very cool thing Posted by annis about over 2 years 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 over 2 years 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 over 2 years 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 over 2 years 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 over 2 years 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 over 2 years 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 over 2 years 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 over 2 years 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 over 2 years 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 about 1 year 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 about 1 year 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 about 1 year 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 about 1 year 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 about 1 year 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 about 1 year 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 year 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 about 1 year 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");

Re: Using existing views Posted by Krues8dr about about 1 year ago.

I have also been trying to find a way to use existing views, as many of the other posters have. If you create a model for the view in the usual manner that you do with tables, you can use that to query the view as if it were a normal table. However, if your view does not have an id (as in le6o's example), you'll encounter issues.

By default, Doctrine queries for the table's id, even if you don't explicitly request it. To prevent it from doing so, open up the table model (you should have two objects in the doctrine model dir for each table, this is the one that ends in Table) and add the following line after the initial class definition:

public function initIdentifier() {}

This overrides the default identifier initialization, so no id column is added to queries if there isn't one. Once this is done, you should be able to use a standard doctrine query to get your data back out. The big drawback of this method is that you have to use Array hydration, as the object hydration requires the id to iterate. (It looks like maybe the RecordDriver Hydrator keys the records against the id column?)

Mr. Wage may know of a better way to avoid this requirement, but this is the only way I've been able to get this to work so far. Hope that helps some of you!

Doctrine Views - Do It Right! Posted by FloaydThreepwood about about 1 year ago.

It really is devastating that there is no HowTo or advice besides this blog entry.

Doctrine has ists quirks, we all know that, but Views are such a basic concept in todays database management and surely provide much better performance than raw querys in reporting modules, that there should be a better aproach and documentation for this.

I really see no reason to not make use of Doctrine Models with this. Why can't I flag my tables to become a View with the folowing conditions? Why do I have to fake models and use them as a excuse for Doctrines misconception?

@FloaydThreepwood Posted by jwage about about 1 year ago.

No offense but your comment is kind of rude. This is an open source project. Nothing HAS to be implemented. Most of the features are built by a need from someone. If its not there, then nobody has needed and donated their time to implement the feature. If you really want the feature then I think a better approach would be to try and contribute instead of criticizing in the way you did.

Why views? Posted by Tahsin Hasan about about 1 year ago.

Hello,

I may be wrong, but Doctrine is an ORM. and it provides an abstraction layer itself. so, why should I use views with doctrine? plz leave answers on tahSin's gaRage.

Add an ID of sorts Posted by MikeF about 11 months ago.

My suggestion is to always select something unique as an id and treat it like any other table in doctrine, just don't ever try to write to it :)

ALSO

Instead of DROP VIEW test_view;

With MySQL, you can do..

CREATE OR REPLACE VIEW test_view AS SELECT ....

Mike

View abstraction model Posted by jesus.farias@gmail.com about 11 months ago.

Hi, i've been using doctrine from about six months, i'm not an expert but i know the basics and this has been enough for me and my web-app requirements. The problem begins cause i need a kind of "dynamic table model" in other words an specific one table's abstraction, i thought implement a view for this purpose, but i can't figure out how define the BaseModel for the view to use it like a table, thus allowing the use of methods like save(), find() and build (logicals) relationships with others entities. in few words: can i build a table model from a query/view?, it is possible? i read the posts from above but this issue still being not realy clear at all for me.

me realy will apreciate any help, thanks in advance.

Regards.

Getting some specific data from a view Posted by Bob about 3 months ago.

Man, so many people have asked the question and no one could answer apparently: how do we actually get some specific data from the view that has been created? I could not find any answer to that question online so I wonder what is the point of having this page here...

re: Get some specific data from a view Posted by Bob about 3 months ago.

Ok, if anyone need an answer to my question on how to retrieve specific data from a view, just use a raw query. That's how it goes with Symphony 1.4.9:

$z = Doctrine_Manager::connection(); $result = $z->execute("SELECT c1__1 as name, * FROM MyView WHERE ...');

return $result;

Create Comment