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.