HABTM with Pagination

Question

At first take a look at the following model structure:

Model Building:

id
name

Model BuildingRange:

id
building_id
postalcode

Ok, so BuildingRange $belongsTo Building and Building $hasMany BuildingRange. Should be clear til' here.

Now let

$current_postalcode="12345";

I know want to do something like this in the BuildingController:

$this->paginate('Building',array('Building.BuildingRange.postalcode'=>$current_postalcode));

In text: I want to select all buildings for that an entry "BuildingRange" with $current_postalcode exists. How do you do that?

I appreciate your help!

score 1 · Accepted Answer · answered Jan 13 '13 at 19:35

When dealing with such a hasMany association, CakePHPs auto-magic needs two queries, one on the Building table, and one on the BuildingRange table. When passing conditions via the pagiante method, these conditions will be passed to the first query, and thus this it will fail since the associated models table isn't joined.

This problem can be solved on a few different ways, one would be using an ad-hoc join, for example:

$this->paginate = array
(
    'joins' => array
    (
        array
        (
            'table' => 'building_ranges',
            'alias' => 'BuildingRange',
            'type' => 'LEFT',
            'conditions' => array('BuildingRange.building_id = Building.id')
        )
    )
);
$this->paginate('Building', array('BuildingRange.postalcode' => $current_postalcode));

This would result in a query that looks something like this:

SELECT `Building`.`id`,
       `Building`.`name`
FROM   `buildings` AS `Building`
       LEFT JOIN `building_ranges` AS `BuildingRange`
              ON ( `BuildingRange`.`building_id` = `Building`.`id` )
WHERE  `BuildingRange`.`postalcode` = '12345'
LIMIT  20

Note that in the conditions passed to the paginate method there is no need to reference the BuildingRange model through the Building model, ie no need to use Builduing.BuildingRange (that wouldn't work anyway).

ps, it's always good to mention the CakePHP version you are using!

thanks that ad-hoc solution seems to work like a charm. I would like to mention that - of course - it would also have been possible to use the BuildingRangeController and then everything would work without any additional joins. But I wanted the controller to be `Building`so your solution is perfect. — androidavid, Jan 15 '13 at 09:09

HABTM with Pagination

1 Answers1