Propel’s use of the Criteria class has many unknown features documented deep within the propel API. One of my favorites is setDistinct(), which will add the DISTINCT keyword into the start of your query. This is very useful when you want to create a select menu that presents a list of pre-existing values for a column in your database.
In this example, I wanted to create a select menu to use as a filter in the admin generator within symfony. We have a category table, and the categories are themselves have a “type” column, which is a plain text field that contains the “type” of category, like documentation, podcasts or faqs. To create a nice drop down with these options, we want to select all of the distinct values of the “type” column in this table. We can accomplish this easily with a Criteria object. Check it out:
// from our CategoryPeer class public static function getTypes() { $c = new Criteria(); $c->addSelectColumn(self::TYPE); $c->setDistinct(); $c->addAscendingOrderByColumn(self::TYPE); $rs = BasePeer::doSelect($c); $types = array(); while ($rs->next()) { $types[] = $rs->getString(1); } return array_combine($types, $types); }
This ends up generating the following SQL statement:
DISTINCT category.TYPE, UPPER(category.TYPE) FROM category ORDER BY UPPER(category.TYPE) ASC
Perfect!
Thanks very much for these blog posts, several time’s I’ve ended up here from my googling for ideas when I get stuck trying to do some advanced stuff with propel.
By vitriolix on Feb 8, 2009
Vitriolix — You’re very welcome! Glad it helps someone else out there too. If you run into any other propel issues that you think might be useful for a post let me know. There aren’t too many good resources out there so we have to make our own. Also, if you are working with the latest version of symfony and you find that some of the posts need updating for the new version, leave a note!
By Scott Meves on Feb 8, 2009
Great post, thanks a ton. Quick question: for the line “$rs = BasePeer::doSelect($c);” why do you use “BasePeer”, rather than “self”. Why wouldn’t self work? And what is this BasePeer?
Thanks again.
By Jack on Jul 2, 2009
@Jack, self:: would work just fine! BasePeer is the “base” class that all of the other propel __Peer classes descend from. So if you called “self::doSelect”, it actually is calling the doSelect() method in the BasePeer class, unless you decide to override the method in your child class. I chose BasePeer just to be explicit where the method was defined, but “self” would more extendible in case you wanted to make local modifications.
By Scott Meves on Jul 2, 2009