cakephp logo

Custom group by pagination and a calculated field

This is a basic example of how to use the CakePHP paginator helper with the group by condition, it took a little mucking around but this is how I got it working.

In this example we have a Comment controller which I want to paginate a custom query displaying an IP address and a calculated count field of the number of spam comments from these particular IP addresses.

In the Controller

The CommentController code looks like the following :-

Show Plain Text
PHP code
  1.  
  2. var $helpers = array('Paginator');
  3. var $paginator = array('limit' => 20);
  4.  
  5. function admin_index($filter=null) {
  6.     $conditions = array();
  7.     $this->Comment->recursive = 0;
  8.     if ($filter == 'count') {
  9.         $conditions = array('Comment.status = 0');
  10.         $this->paginate['Comment'] = array(
  11.             'fields' => array(
  12.                 'Comment.id', 'Comment.ip', 'Count(Comment.ip) as Count'
  13.             ),
  14.             'conditions' => array(
  15.                 'Comment.status = 0'
  16.             ),
  17.             'group' => array(
  18.                 'Comment.ip'
  19.             ),
  20.             'order' => array(
  21.                 'Count' => 'desc'
  22.             )
  23.         );
  24.         $data = $this->paginate('Comment', $conditions);
  25.        
  26.     } else {
  27.         if ($filter == 'spam') {
  28.             $conditions = array('Comment.status = 0');
  29.         } else {
  30.             $conditions = array('Comment.status > 0');
  31.         }
  32.         $this->paginate['Comment'] = array(
  33.             'order' => array(
  34.                 'Comment.id' => 'desc'
  35.             )
  36.         );
  37.     }
  38.     $data = $this->paginate('Comment', $conditions);
  39. }
  40.  

I did have the order by condition set in the var paginator initialization array() but for some reason this was always being ignored, I didn't investigate why but found that setting the condition through $this->paginate['Comment'] worked a treat.

In the Model

The next key part with the group by condition is to override the models paginateCount() method in the Comment model as follows :-

Show Plain Text
PHP code
  1. function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
  2.     $parameters = compact('conditions');
  3.     $this->recursive = $recursive;
  4.     $count = $this->find('count', array_merge($parameters, $extra));
  5.     if (isset($extra['group'])) {
  6.         $count = $this->getAffectedRows();
  7.     }
  8.     return $count;
  9. }

For my particular group by pagination I actually wanted the $count to represent the number of rows returned by the count query.

In the view

Also if your passing arguments in your url you need to tell the paginator to pass them along as well, so in your view you need this line somewhere :-

Show Plain Text
PHP code
  1. $paginator->options(array('url' => $this->passedArgs));

The Calculated field

The other thing to note is that the calculated field is added by CakePHP to the zero index of the array. So for this example the calculated Count field will be found under ['0']['Count'], if you want to move this field to your main index you will need to write a afterFind() function.

Anyway thats how I got it to work, hope its of help to someone.

Filed under: Cakephp  Tags: Pagination

36 Responses to “Custom group by pagination and a calculated field”

Many thanks for posting this, your custom paginateCount function has just saved me hours of frustration.

@Nebbian
No problem. Glad the example was of use to someone else.

OMG Thank you! I found your solution at https://trac.cakephp.org/ticket/5123... You saved me hours of frustration as well. Luckily I found this right away.

Dude. I heart you so much right now! You have no idea how much hair I've lost over this issue (and believe me, I didn't have much to begin with)! Seriously though, this is a real lifesaver, and (unfortunately) it me many hours of searching to come across your site. Google, here comes a vote-up!

Ran into a memory problem with this solution. The query we're using returns a row for each row we want to count (which I guess is what makes this work), but if you're counting 100,000s of rows, as in my case, it starts to use up a lot of memory. I use LIMIT to return only the top 20 results when doing my paginate query, but obviously that doesn't apply to the paginateCount. At just over 100,000 rows my application spikes to ~130mb memory usage. Unfortunately this causes some of my pages to crash. :(

Think I found a solution that accomplishes the same thing with less memory. It only works with one GROUP BY field. Feedback would be much appreciated.

public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
	$parameters = compact('conditions', 'recursive');
	if (isset($extra['group'])) {
		$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;
}

Total life saver. This should really be fixed in the main release though especially given how simple it is. To fix the count location problem, add this method to your model:

function afterFind($results, $primary=false) {
		if($primary == true) {
			if(Set::check($results, '0.0')) {
				$fieldName = key($results[0][0]);
				foreach($results as $key=>$value) {
					$results[$key][$this->alias][$fieldName] = $value[0][$fieldName];
					unset($results[$key][0]);
				}
			}
		}

		return $results;
	}

@steve
Thanks for you suggestion, I'll have to take a look when I get a chance, have been flat out with work for a while now, also need to find a suitable dataset for my local server.

@CakeNoob
Thanks for the example, I also came across a one line example from Tarquie Sani to use in the afterFind(), I haven't tried it however :-

array_walk($results, create_function(’&$v’, ‘$v["Photo"]["rownum"] = $v[0]["rownum"]; unset($v[0]);’));

Thank you!! I was searching exactly this :)

@Steve Your paginateCount function worked like a charm. Thank you so much for the post

I was trying this for last 2 days but couldn't make it to work, thankyou, it really worked as if it was made for me. This should be fixed in cakes release or many more will keep on wasting time on it.

Thanks Steve! Thanks to this site too, you saved me

try this one in your current Model :

/**
		* Overridden paginateCount method
		*/
		function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
			$parameters = compact('conditions', 'recursive');
			
			if (isset($extra['group'])) {
				unset($extra['group'], $extra['fields'], $extra['order'], $extra['limit'], $extra['conditions']);
				$extra['fields'] = array(
								'COUNT(DISTINCT HomeArticle.id) as count'
							);
			}else{
				unset($extra);
			};
			
			$this->recursive = $recursive;
			$results = $this->find('all', array_merge($parameters, $extra));
			return ($results[0][0][count]); //count($results);
		}

In controler :
cake/libs/controller.php
$count = $object->paginateCount($conditions, $recursive, $defaults);

wow.. thx.. :)

Thanks alot for this, real life saver!

This is lifesaver, thank you very much. But I still have a problem with $paginator->sort in the view to generate a sorting link for the `Count´ field. I have tried around but I can get it work. Is there any solution?

gr8

Really thanks for the function, it worked out really just fine! did you sent it to the bakery or report as bug to the CakePHP staff? i think that cakephp should support this in a native way. Thanks again, it saved my life!

This article really helps me alot. Keep posting dude!

Thanks a lotttt... that paginateCount function just made my day ;)

thank you very much for your post you save me

thanke

Thanks for this nice tutorial.

Thank you so much! Like for the others, you saved me hours of work :)

Thanks a lot for this!

Paginate a Find Result Hello, im looking for a solution to this little problem of mine, i need to paginate tha data resulting from a custom find, bellow you can see the code that im using, at this point i get all the data that i need, but i"d like to paginate it because sometimes it returns a large amount of data. Anyone have a suggestion?? THX in advance PS: if you anything else code wise or me being more specific let me know

//THIS FORCES TO DO THIS "SELECT * FROM table1 LEFT JOIN table2 WHERE....."

$this->Equipo->unbindModel(array('hasMany'=>array('Storages')));
$this->Equipo->bindModel(array('hasOne'=>array('Storages'=>array(
                                                       'foreignKey'=>false,
                                                        'conditions'=>array('Equipo.id_almacen = storages.id')
                                                                               )
                                                               )
                                               )
                                       );
//IN THIS PART $ DATA GETS THE VALUE OF "SELECT * FROM table1 LEFT JOIN table2 WHERE....."
$data=$this->Equipo->find('all',array('conditions'=>array(
                                               'part_number LIKE'=>"%".$this->data['Equipo']['part_number']."%",
                                               'id_almacen LIKE'=>"%".$this->data['Equipo']['id_almacen']."%",
                                               'equipo_central LIKE'=>"%".$this->data['Equipo']['equipo_central']."%",
                                               'descripcion LIKE'=>"%".$this->data['Equipo']['descripcion']."%"
                                               ),
                       )
               );

great!! solutions for pagination count problem with group by

Thank you so much! YOU SAVE MY LOST OF HOURS

Thanks so much!

I was also working in this issue for a long time.. In my paginate function it uses join function and it was not working with this method also.. ie the order array remains as empty... But when I change the syntax as follows it is working for me... 'order' => 'flag_count ASC' instead of given it as array()

Thanks, seeing this has just saved me hours of custom calculation with pages :D

Thankyou very much.

Many thanks for this post. Saved many of my hours. Cheers!

Thanks!

Thanks so much, this saves me 2 days of searching. If there's a CakePHP Noble prize, you should get it!

Thanks.. you save me a lot of hours looking the bug.. youre awesome!!:.

Sorry, comments have been closed for this post.
(default) 11 queries took 3 ms
NrQueryErrorAffectedNum. rowsTook (ms)
1DESCRIBE `posts`17171
2DESCRIBE `comments`11110
3DESCRIBE `tags`220
4DESCRIBE `categories`220
5DESCRIBE `posts_tags`220
6DESCRIBE `categories_posts`221
7SELECT `Post`.`id`, `Post`.`url`, `Post`.`title`, `Post`.`icon`, `Post`.`metadesc`, `Post`.`metakeys`, `Post`.`categories`, `Post`.`tease`, `Post`.`body`, `Post`.`private_body`, `Post`.`created`, `Post`.`modified`, `Post`.`status`, `Post`.`allow_comments`, `Post`.`tags`, `Post`.`hitcount`, `Post`.`hitcount_rss` FROM `posts` AS `Post` WHERE `Post`.`url` = 'custom-group-by-pagination-and-a-calculated-field' LIMIT 1110
8SELECT `Comment`.`id`, `Comment`.`post_id`, `Comment`.`body`, `Comment`.`author`, `Comment`.`url`, `Comment`.`email`, `Comment`.`ip`, `Comment`.`status`, `Comment`.`junk_score`, `Comment`.`created`, `Comment`.`modified` FROM `comments` AS `Comment` WHERE `Comment`.`status` = 2 AND `Comment`.`post_id` = (65) 36361
9SELECT `Tag`.`id`, `Tag`.`tag`, `PostsTag`.`post_id`, `PostsTag`.`tag_id` FROM `tags` AS `Tag` JOIN `posts_tags` AS `PostsTag` ON (`PostsTag`.`post_id` = 65 AND `PostsTag`.`tag_id` = `Tag`.`id`) 110
10SELECT `Category`.`id`, `Category`.`category`, `CategoriesPost`.`post_id`, `CategoriesPost`.`category_id` FROM `categories` AS `Category` JOIN `categories_posts` AS `CategoriesPost` ON (`CategoriesPost`.`post_id` = 65 AND `CategoriesPost`.`category_id` = `Category`.`id`) 110
11UPDATE `posts` AS `Post` SET `Post`.`hitcount` = Post.hitcount + 1 WHERE `Post`.`id` = 6510