Skip Navigation
« w00t! I’ve got wordpress spam-hacked!

paginating ad-hoc joins in CakePHP

Couple months ago, Nate published in the bakery a quick tip to do what he called ad-hoc joins in cakephp’s find()
Basically the trick was to use the joins parameter of the method find() to define the associations.
I have noticed a lot of people asking about how to paginate the results.
So This post is exactly for doing that..

I’ll be using the modified version found in the comments by Bambou

Basically His version allows to define the type of find() using a parameter called operation

just copy it to your AppModel, or the model that you want to do ad-hoc joins on:

Now for pagination to work with the new ‘matches’ type, you’ll have to write a custom paginateCount()
copy this to your AppModel or the model in question:

  1.  
  2. function paginateCount($conditions = array(), $recursive = 0, $extra = array()) {
  3.  
  4.         $parameters = compact(‘conditions’);
  5.         if ($recursive != $this->recursive) {
  6.                 $parameters[‘recursive’] = $recursive;
  7.         }
  8.         if(isset($extra[‘type’]) && ($extra[‘type’] == ‘matches’)) {
  9.                 $extra[‘operation’] = ‘count’;
  10.                 return $this->find(‘matches’, array_merge($parameters, $extra));
  11.         } else {
  12.                 return $this->find(‘count’, array_merge($parameters, $extra));
  13.         }       
  14. }

Usage is nearly identical to the normal paginate() except that instead of doing:

  1.  $markers = $this->Marker->find(‘matches’, array(
  2.        ‘operation’ => ‘all’,
  3.         ‘model’ => ‘Tag’,
  4.         ’scope’ => array(‘Tag.tag’ => $tags)
  5.    )
  6. );

You’ll do:

  1.  array_unshift($this->paginate, ‘matches’);
  2. $this->paginate[‘model’] = ‘Tag’;
  3. $this->paginate[‘operation’] = ‘all’;
  4. $this->paginate[’scope’] = array(‘Tag.tag’=>$tags);
  5. $result = $this->paginate();

The trick indeed is in array_unshift($this->paginate, ‘matches’);
and has to do with how paginate() works internally ( makes the assumption/convention that the first element in the paginate array is the type )

So that’s it, hopefully this will help some misguided cakephp souls ;)

This entry was posted on Saturday, March 28th, 2009 at 1:36 pm and is filed under CakePHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “paginating ad-hoc joins in CakePHP”

  1. I don’t know if there’s something else I need to do in the view? I’m trying to paginate a query like the way Nate is in his example:

    $this->paginate['scope'] = array(’Tag.tag’ => explode(’ ‘, $this->params['url']['q']));

    First page shows up okay, but the links to next pages are wrong.

    Author: Meo | April 6th, 2009 at 2:18 pm

  2. [...] joins in CakePHP” Today I found a site demonstrating how to paginate query results.Devmoz.com The Bakery [...]

    Author: How to paginate query results in CakePHP “ad-hoc joins in CakePHP” « Myles Kadusale’s Blog | April 30th, 2009 at 2:15 pm

  3. I tried the paginatorCount function, but I don’t seem to get it working.

    I added the improved Find Function and your paginatorCount Function in my App-Model
    (http://bin.cakephp.org/view/501904389)
    and call it like this (http://bin.cakephp.org/view/494098784) from my controller.

    Without the pagination (i.e. pure $this->MODEL->find(’matches’, $options) ) the query is fine, so I think the problem might be the paginatorCount.

    It generates this SQL Statement (http://bin.cakephp.org/view/1047565073), where Author.id is obviously not known, as there is no join supplied.

    Can you help me to solve this, either pointing out my mistake in coding or by telling me how to change the paginatorCount to make it work?

    Thank you for reading this :)

    Author: Imrael | November 12th, 2009 at 2:35 pm

  4. okay, nevermind… found my typo
    I should put the paginateCount function inside the {} of the AppModel, not outside ;)

    Author: Imrael | November 13th, 2009 at 10:14 am

  5. immobilier Bretagne…

    Article captivant, nous publions le lien vers votre article sur notre compte twitter. Cordialement….

    Author: immobilier Bretagne | April 15th, 2010 at 6:31 pm

Leave a Reply


Login Method

OpenID

Anonymous