Home    Articles

 

ZF2 COUNT using greaterThanOrEqualTo,lessThanOrEqualTo and GROUP BY

 

We all know that in any query GROUP BY goes to the end of the query. But what you need to do when you need to use a date range then GROUP BY? In ZF1 your code would look something like this: 

$db->select()
       ->from('your_table')
       ->where('dateCreated <= ?',date('Y-m-d'))
       ->group('county');
    

And all would have looked fine. But in ZF2 when you use ->where->lessThanOrEqualTo('dateCreated',date('Y-m-d')) the $select OBJECT is changed and group() method no longer exists, in case your are doing something like this:

$this->db->select = new \Zend\Db\Sql\Select();
$select =  $this->db->select
                            ->columns(array('id','county','orders'=>new \Zend\Db\Sql\Expression('COUNT(id)')))
                            ->from('orders')
                            ->where(array('userId'=>1))
                            ->where->lessThanOrEqualTo('dateCreated',date('Y-m-d'))
                            ->group('county');

Fatal error: Uncaught Error: Call to undefined method Zend\Db\Sql\Where::group()

To make this work, use GROUP BY before Where:

$this->db->select = new \Zend\Db\Sql\Select();
$select =  $this->db->select
                            ->columns(array('id','county','orders'=>new \Zend\Db\Sql\Expression('COUNT(id)')))
                            ->from('orders')
                            ->group('county')
                            ->where(array('userId'=>1))
                            ->where->lessThanOrEqualTo('dateCreated',date('Y-m-d'));
                            

This will generate this query: 

SELECT `orders`.`id` AS `id`, `orders`.`county` AS `county`, COUNT(id) AS `orders` FROM `orders` WHERE `userId` = '1' AND `dateCreated` <= '2017-03-22' GROUP BY `county`

In case i am wrong with something, please add a comment.

I hope it helps.

by