How can I write a SQL count with group by clause using Propel ORM in Symfony?

Question

How can I write the following SQL query using the Propel ORM?

SELECT species, COUNT(*) FROM Bird GROUP BY species;

Answer 1

I've found it hard to find a single document on Propel Criteria (there doesn't seem to be an API document on it) so I usually use the list in Chapter 8 of the symfony book; but I've no idea whether or not that's comprehensive.

But what you can do is to feed SQL directly to Propel. The following is modified from an example in http://propel.phpdb.org/docs/user_guide/chapters/FindingObjects.html:

    $con = Propel::getConnection(DATABASE_NAME);

    // if not using a driver that supports sub-selects
    // you must do a cross join (left join w/ NULL)
    $sql = "SELECT species, COUNT(*) FROM Bird GROUP BY species";

    $stmt = $con->createStatement();
    $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);

    return parent::populateObjects($rs);

I don't think I've ever used it this way myself, though I might have.

Answer 2

$c = new Criteria();
$c->addAsColumn('cnt', "count(*)");
self::addSelectColumns($c);
$c->addGroupByColumn(BirdPeer::SPECIES);

but yo will need to do custom hydrating if you need to get count(*) to your populated objects.

Answer 3

This is not a query that would result in meaningful hydrated Bird objects, since you only select the species column and the count of those species. So a "raw" SQL query as Colin suggested would probably the best way to go here - but don't hydrate at the end, just get the data from the resulting PDOStatement.

If species was a reference to a Species table, you could work from there: hydrate Species objects, with an extra column for the bird counts per species. If you use Symfony up to version 1.2, I highly recommend the DbFinder plugin, as it greatly simplifies working with Criteria, and has methods to select a single supplementary column:

$speciesQuery = DbFinder::from('Species')->
  join('Bird')->
  groupBy('Bird.Id')->
  withColumn('COUNT(Bird.Id)', 'NbBirds');

foreach ($speciesQuery->find() as $species) {
  echo $species->getName() . ": " . $species->getNbBirds() . " birds\n";
}

If you use Symfony 1.3 or 1.4, you should upgrade the bundled Propel 1.4 to Propel 1.5, where François Zaniotto, creator of DbFinder, ported much of its functionality and added more, so the above code works in Propel 1.5 without an extra plugin.