add Count to SQL query in Propel

Question

I have in my project old version (1.2) Propel. I would like make query:

SELECT city_id, COUNT(*) FROM House GROUP BY city_id

I tried:

  $c = new Criteria;
  $c->addAsColumn('count', 'Count(' . HousePeer::CITY_ID . ')'); 
  $this->results = HousePeer::doSelect($c);

but this not working - return me only first record from database, without count etc.

i tried also:

$con = Propel::getConnection();
$sql = "SELECT city_id, COUNT(*) FROM House GROUP BY city_id";
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
$this->results = HousePeer::populateObjects($rs);

but this throw me

Error populating House object [wrapped: Invalid resultset column: 3]

I would like only receive same as in SQL table:

city_id | count
1       | 2
2       | 4
3       | 3

etc

or get name City from CityPeer (i have correctly relations between City and House) for example:

city       | count
New York   | 2
Paris      | 4
Washington | 3

but i can't use this query in Propel.

j0k j0k · Accepted Answer · 2012-07-17T15:20:22

You have a great example in the old snippet from symfony.

Try:

$c = new Criteria;
$c->clearSelectColumns()->addSelectColumn(HousePeer::CITY_ID);
$c->addGroupByColumn(HousePeer::CITY_ID);
$c->addAsColumn('numCity', 'COUNT('.HousePeer::CITY_ID.')');
$c->addJoin(HousePeer::CITY_ID, CityPeer::ID);

$rs = HousePeer::doSelectRS($c);

while ($rs->next())
{
  // etc ...
}

add Count to SQL query in Propel

1 Answers