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
Hi,
I tried your tips but it doesn’t work for me.
Assume that it’because I’m using symfony 1.27 and propel1.3.
Any idea, please?
thanks
By Michael on Jul 7, 2009
@Michael,
I think the setDistinct method still exists in propel 1.3, but the way you retrieve raw results is different. Something like this might work (untested):
This propel upgrade page has a lot of good types to help translate old examples. http://propel.phpdb.org/trac/wiki/Users/Documentation/1.3/Upgrading
By Scott Meves on Jul 7, 2009
It works for me with :
[...]
while($res = $stmt->fetchColumn(0)) {
[...]
Please pay attention that if the value of the first element retreaved is NULL (in our example here, if the first cathegory doesn’t have a type), you won’t see the others types at all!
To avoid that, you should add a criteria on Type is not null.
By Ouach on Aug 24, 2009
Scott, every time I search for anything related to Propel and Symfony, your blog comes up. I think you dug deeper into this material than anyone else.
By Lawrence Krubner on Apr 29, 2010
@Lawrence, Thanks! I’m glad it is still relevant.
By Scott Meves on Apr 29, 2010
Yes, still relevant. One of my clients has a 1.0 version Symfony site, which we are adding a few features to.
By Lawrence Krubner on Apr 29, 2010