Here’s a quick Propel tip. While working on a site recently, I had to figure out a way to randomly pull some records from a database using Propel from within a Symfony application. This is a bit of a hack as it will only work if you’re using MySQL, but it’s an elegant way (considering the alternatives) to get some random results. I assume you can use this method with another database. You’d just have to figure out what functions to call instead of the MySQL specific ones like RAND() and DAYOFWEEK().
From within an action, you can do something like this:
$c = new Criteria();
$c->addAscendingOrderByColumn('RAND()');
$c->setLimit(20);
$this->photos = PhotoPeer::doSelect($c);
… which will get you a set of 20 random photo objects.
Something a bit more interesting is using a custom criteria. This basically lets you use any MySQL specific function from within propel without resorting to building a query completely from scratch. Take this example:
$c->add(PodcastPeer::DATE, 'DAYOFWEEK('.PodcastPeer::DATE.')='.$this->filters['day_of_week'], Criteria::CUSTOM);
Basically, I wanted to be able to use a symfony-type filter (just like the ones that are generated automatically by the propel admin generators) to filter podcasts by certain days of the week. So someone could, for example, choose only to listen to those podcasts that were released on Wednesdays. This snippet of code basically allows me to run this sql query:
SELECT * FROM podcast WHERE DAYOFWEEK(podcast.DATE)=1
… where 1 = Sunday, 2 = Monday, etc.
Cool but here you loose the advantage of database abstraction as these instructions are mysql specific. Anyway this can greatly help.
Yeah, that may be a disadvantage, but really, how often do you switch database types in the middle of a project? Database abstraction is a great selling point for tools like Propel, but I think the reality is that it’s highly unlikely that most people will ever switch a project over to a new database once they’ve chosen something. Even so, these are small things and I think the benefit of writing them this way are much greater (both from a performance and coding standpoint) than the hassle it would be to write it so that it’s fully abstracted. I’d love to be proven wrong though! If there’s an easy way to do this without tying it to MySQL specifically, please let me know.
Very nice tip ! Thanks alot !
I didn’t know about those random criteria
I fully agree with you, Mark. I never switched the database during a project - and honestly, those small adjustments like changing some SQL queries would net be the most problematic part in the migration process.
you should better subclass the propel criteria and add a orderByRand() method or sth like that..
so you only have one place where you have to change the code, if you move from one dbtype to another.
Markus,
I think that’s a good idea. It might make even more sense though to make it a Propel Behavior so that every object in your model can have the orderByRand() method without writing any additional code.
Hi Mark,
I think a Propel Behavior would not be the right direction for your case..
This feature should be implemented directly in the criteria, so it can be used with all classes of your domain model.. (This features is independet from specific model classes)