cakephp and SQL_CALC_FOUND_ROWS

Question

I am trying to add the SQL_CALC_FOUND_ROWS into a query (Please note this isn't for pagination)

please note I am trying to add this to a cakePHP query the code I currently have is below:

return $this->find('all', array(
                'conditions' => $conditions,
                'fields'=>array('SQL_CALC_FOUND_ROWS','Category.*','COUNT(`Entity`.`id`) as `entity_count`'),
                'joins' => array('LEFT JOIN `entities` AS Entity ON `Entity`.`category_id` = `Category`.`id`'),
                'group' => '`Category`.`id`',
                'order' => $sort,
                'limit'=>$params['limit'],
                'offset'=>$params['start'],
                'contain' => array('Domain' => array('fields' => array('title')))
            ));

Note the 'fields'=>array('SQL_CALC_FOUND_ROWS',' this obviously doesn't work as It tries to apply the SQL_CALC_FOUND_ROWS to the table e.g. SELECTCategory.SQL_CALC_FOUND_ROWS,

Is there anyway of doing this? Any help would be greatly appreciated, thanks.

Answer 1

You may want to look at cakephp paginate using mysql SQL_CALC_FOUND_ROWS. The person had similar syntax as you have and it worked for him.

If that doesn't help you can always use $this->find('count', $params); (http://book.cakephp.org/view/1020/find-count) or $this->query('YOUR SQL QUERY HERE'); (http://book.cakephp.org/view/1027/query).

Besides that you should not use 'joins' together with 'contain'. According to the documentation that "could lead to some SQL errors (duplicate tables), so you need to use the joins method as an alternative for Containable".

Answer 2

Maybe you can make your field parameter as below:

'fields'=>array('SQL_CALC_FOUND_ROWS *','COUNT(`Entity`.`id`) as `entity_count`')