How to use MySQL Views in Symfony?
Although MySQL views tend to be sometimes (ok, let’s be sincere - they usually are) ineffective, we can face a situation where we want to use them. Neither Symfony, nor Propel support natively views, but I found recently a little workaround for it.
Let’s assume we’ve got in our schema.yml:
entry:
id:
title: varchar(255)
body: longvarchar
pub_date: { type: timestamp, index: true }
which, after running propel-build-all, will give us two following classes (files) in lib/model:
Entry.php
EntryPeer.php
We want to use a view, that shows entries from recent week, we so run the query:
CREATE VIEW entry_recent AS SELECT * FROM entry WHERE pub_date > CURRENT_DATE - INTERVAL 7 DAY;
The question is how to get objects of Entry type from entry_recent table?
Solution.
Add an aditional table in schema.yml with skipSql=true attribute – it will result in generating php classes, but propel won’t generate any SQL code.
entry_recent:
_attributes: { skipSql: true, readOnly: true }
id:
title: varchar(255)
body: longvarchar
pub_date: { type: timestamp, index: true }
It’s important that entry_recent has always the same columns as entry.
After propel-build-all we have two additional classes:
EntryRecent.php
EntryRecentPeer.php
Now we open EntryRecent.php and change the base class from BaseEntryRecent to Entry.
class EntryRecent extendsBaseEntryRecentEntry { }
And that’s it.
When we want to use our view, we simply use EntryRecentPeer methods to retrieve objects – doSelect(), doSelectOne() methods will return us EntryRecent objects, but their superclass is Entry, so we can use all methods that we had previously wrtiten in Entry.php.
What’s more we can update our objects (normally we are not able to update views), because they will use BaseEntry class instead of BaseEntryRecent.
