Zend_Db_Select - Combines and Counts - Possible Zend Error?

Question

I am having a problem getting COUNT () from an SQL query using Zend_Db_Table_Select, and I think this might be a possible error, because the SQL that it should generate does work. Here's Zend Select Query: ($ is Zend_Db_Table, renamed to table1 in this example)

  $select = $this->select(); $select->setIntegrityCheck(false); // Select Count $select->from($this, array("COUNT(*) as 'COUNT'")) ->joinLeft('users', 'table1.userID = users.userID') ->joinLeft('table2', 'users.anotherKey = table2.anotherKey'); // Add Where clause after join $select->where('users.anotherKey = ?', $anotherKeyValue); 

This gives an error:

 SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause` 

However, this request ...

 SELECT COUNT(*) AS 'count' FROM table1 LEFT JOIN users ON table1.userID = users.userID LEFT JOIN table2 ON users.anotherKey = table2.anotherKey WHERE users.anotherKey = [anotherKeyValue] 

... returns the expected results without errors when starting in the database. Any ideas what is happening, why the error and how to get around this?

Answer 1

Have you tried to see the actual request that zend_db produces?