Zend framework group by

Question

Hey folks!

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.

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`