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!