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

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 by in  Web Development   |  

14 Responses to Propel Set Distinct: setDistinct()

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

  2. vitriolix says:

    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.

  3. Scott Meves says:

    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!

  4. Jack says:

    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.

  5. Scott Meves says:

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

  6. Michael says:

    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

  7. Scott Meves says:

    @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

  8. Ouach says:

    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.

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

  10. Scott Meves says:

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

  11. Yes, still relevant. One of my clients has a 1.0 version Symfony site, which we are adding a few features to.

  12. staratnight says:

    Ok, this blog entry is kind of outdated, but it was the only useful snippet google could find about Propel and setDistinct().

    For Propel 1.6 the following code will do the job:
    public static function getTypes() {
    $c = new Criteria();
    $c->addSelectColumn(CategoryPeer::TYPE);
    $c->setDistinct();
    $c->addAscendingOrderByColumn(self::TYPE);
    $stmt = CategoryPeer::doSelectStmt($c);

    return $stmt->fetchAll();
    }

  13. Kiran says:

    Hi,

    Thanks for the post its good, but addSelectColumn() is not working for me.

  14. kiran says:

    I am trying to fetch count of distinct values for a particular column. The above mentioned statements are fetching distinct rows, but I want to fetch distinct rows of a particular column.

    Thanks in Advance.