phpcakephppaginationcakephp-2.0

Cakephp pagination with Having Clause


Is it possible to use below query with Cakephp pagination??

select u.email,(select count(*) from relatives as iu where iu.user_id=u.id ) as iu_count,(select count(*) from friends as ff where ff.user_id=u.id ) as ff_count from users as u having (iu_count>0 OR ff_count>0)

i tried to do this using cakephp pagination but it gives an error as "iu_count" and "ff_count" are not available in selection part of query but they are available in Having clause. My cakephp pagination query is as below.

SELECT COUNT(*) AS `count` FROM `abc`.`users` AS `User` WHERE 1 GROUP BY `User`.`id` having (iu_count>0 OR ff_count>0) 

Need a help/guidance to work with this.


Solution

  • today i googled more and find a solution which works for me. The solution is to override paginateCount method in Model.

    Below is a overrided function.

    public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
            $parameters = compact('conditions', 'recursive');
    
            if (isset($extra['group'])) {
                $parameters['fields'] = array("iu_count","ff_count");
                //$parameters['fields'] = $extra['group'];
                if (is_string($parameters['fields'])) {
                    // pagination with single GROUP BY field
                    if (substr($parameters['fields'], 0, 9) != 'DISTINCT ') {
                        $parameters['fields'] = 'DISTINCT ' . $parameters['fields'];
                    }
                    unset($extra['group']);
                    $count = $this->find('count', array_merge($parameters, $extra));
                } else {
                    // resort to inefficient method for multiple GROUP BY fields
                    $count = $this->find('count', array_merge($parameters, $extra));
                    $count = $this->getAffectedRows();
                }
            } else {
                // regular pagination
                $count = $this->find('count', array_merge($parameters, $extra));
            }
            return $count;
        }
    

    Here "array("iu_count","ff_count");" are my virtual fields which i am using in HAVING clause. assign $extra['group'] to $parameter['fields'] if there are no virtual fields that needed in HAVING clause.

    Hope this solution will help some one.