SUBSCRIBE VIA RSS


Subscribe to our feed

Symfony Experts

Symfony Experts
If you have an urgent question for a symfony-related issue, this is the place to ask.

Topics

TWITTER

Stack Overflow


The old fashioned way

RECENT TUNES

December 13, 2007 – 12:54am Propel Set Distinct: setDistinct()

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!

Posted in  Web Development   |     |  delicious  Digg

11 Responses to “Propel Set Distinct: setDistinct()”

  1. 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

  2. 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

  3. 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

  4. @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

  5. 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

  6. @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):

     
    public static function getTypes() {
      $c = new Criteria();
      $c->addSelectColumn(CategoryPeer::TYPE);
      $c->setDistinct();
      $c->addAscendingOrderByColumn(self::TYPE);
      $stmt = CategoryPeer::doSelectStmt($c);
     
      $types = array();
      while($res = $stmt->fetchColumn(1)) {
        $types[] = $res;
      }
     
      return array_combine($types, $types);
    }

    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

  7. 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

  8. 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

  9. @Lawrence, Thanks! I’m glad it is still relevant.

    By Scott Meves on Apr 29, 2010

  10. 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

1 Trackback(s)

  1. Dec 23, 2007: Symfony.es » Blog Archive » Una semana con Symfony #23 (10 - 16 diciembre 2007)

Post a Comment

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word