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