Dec 22, 2008
08:12

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
 

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

1. Nebbian
Feb 01, 2009

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

2. Brett Wilton
Feb 03, 2009

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

3. Steve
Feb 19, 2009

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.

4. Geoff Garbers
Mar 08, 2009

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!

5. Steve
Apr 09, 2009

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. :(

6. Steve
Apr 09, 2009

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;
}

7. CakeNoob
Apr 10, 2009

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;
	}

8. Brett Wilton
Apr 23, 2009

@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]);’));

9. mischorradas
Jul 02, 2009

Thank you!! I was searching exactly this :)

10. Omar
Jul 24, 2009

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

 

Post a comment