Zend framework group by

Question

I'm trying to do a group by using Zend framework. Here's my code:

$table = new TableClass();
$select = $table->select();
$select->from ("table", array("date", "column1" => "sum(column1)"));
$select->group ( array ("date") );
$results = $table->fetchAll ($select);
$result = $results[0];
$date = $result->date;
$column1 = $result->column1;

TableClass extends 'Zend_Db_Table_Abstract'.

I can see the query by looking at the mysql query log. The query is well formed - column1 is named in the query and the results look correct if I run the query in mysql workbench.

I cannot access the data in 'column1' - I always get this exception:

Uncaught exception 'Zend_Db_Table_Row_Exception' with message 'Specified column "column1" is not in the row'

I can however access the date column without issue.

I tried:

• accessing the columns by array index: $result[0] but you get an exception (can't access the columns by index).

• not using a column alias: $select->from ("table", array("date", "sum(column1)")); $column1 = $result["sum(column1)"]; but you get an exception (no such column "sum(column1)").

• throwing in a Zend_Db_Expr: "column1" => new Zend_Db_Expr ( "sum(column1)" ) but this doesn't help.

Some other examples I have seen suggest the use of the column names without aggregate functions, ie. "column1" instead of "sum(column1)" but that doesn't seem to me to be the answer - the query doesn't have any aggregate functions in it so mysql won't know what to do with it.

Any help appreciated.

Ponton

Answer 1

Firstly, a quick tip for working with Zend_Db_Select (and by extension Zend_Db_Table_Select), you can view the generated SQL by invoking the toString method. It is vital to verify that your code generates the correct query before working with a result set:

$select = $table->select();
$select->from ("table", array("date", "column1" => "sum(column1)"));
$select->group ( array ("date") );

$sql = (string) $select; //Retrieve SQL as a string

Or simply

die($select); //print SQL

I wrote the following test script using your example and have no problems:

class Table extends Zend_Db_Table_Abstract 
{
    protected $_primary = 'id';
    protected $_name = 'table';
}

$db = Zend_Db::factory('Pdo_Mysql', array(
    'dbname' => 'test',
    'username' => 'root',
    'password' => '',
    'host' => 'localhost'
));

$table = new Table($db);

$select = $table->select();
$select->from ($table, array("date", "column1" => new Zend_Db_Expr("sum(column1)")));
$select->group ( array ("date") );
$sql = (string) $select;

echo $sql;

$results = $table->fetchAll ($select);
$result = $results[0];
$date = $result->date;
$column1 = $result->column1;

echo '<br>' . $date . ': ' . $column1;

Use Zend_Debug::dump($result); to inspect data inside the Zend_Db_Table_Row if necessary.

In my case the SQL generated is as follows:

SELECT `table`.`date`, sum(column1) AS `column1` FROM `table` GROUP BY `date`

Canonist

I tried your code and I had the same problem as before even though it works for you. It made me think there's something wrong with my setup. I'm using Zend Studio 7.2 to develop. When I run either your or my code under PHP 5.2.10 in Zend Studio, I get the problems I outlined. When I run either code under PHP 5.3.0, I get no problems. Perhaps PDO doesn't work so well these days under PHP 5.2? Problem solved - thanks for your help David! – Ponton

I did test it with 5.3 but I generally work under 5.2. There are some mysql.dll issues if you're using MySQL 5.1 with PHP 5.2 but they usually result in total failure! – Canonist

Good suggestion to check the SQL.I find the Zend DB API's often generate bogus SQL that doesn't do what you'd expect or might reasonably want (especially where multiple joins are involved). I've never had that issue with any other DB API, it's the only thing in Zend I think is really buggy. – Brogdon