Cakephp 2 how to paginate a model but restrict the results to only those with a conditional associated model

Question

I’m working on a legacy Cakephp 2 system and having some issues figuring out how the pagination is supposed to work.

I have a Users model, a SystemSessions model and a Locations model. Users hasmany SystemSessions and each SystemSession has a location_id. I’m trying to find out how I can paginate Users to only show Users who have SystemSessions with a particular location_id.

If I try:

$conditions['conditions']['User.role'] = 'User';
$conditions['contain'] = array(
    'SystemSession' => array(
        'conditions' => array(
            'SystemSession.location_id' => '34'
        )
    )
);

That will pull back every User and under those users, there will be SystemSessions with the location_id of 34, however, the Users who don't have SystemSessions from that location appear in the results too (just with empty SystemSessions array).

What I want is to only pull back Users who have SystemSessions from that location, Users who haven't got a SystemSessions from that particular location, shouldn't appear in the results.

Normally I could just loop through and remove the ones I don't want, but because I'm trying to paginate the results, that would throw off all the paging and so on.

Can anyone give me any pointers?

Answer 1

You have to replicate what you'd do on SQL level to solve this, that is for example using a join to pull in and filter by the associated data. You can't solve this with contain alone, as hasMany associations are being retrieved in a separate query, hence why your condition only affects the associated data.

Assuming $conditions are your finder options:

// ...
$conditions['joins'] = array(
    array(
        'table' => 'system_sessions',
        'alias' => 'SystemSession',
        'type' => 'INNER',
        'conditions' => array(
            'SystemSession.user_id = User.id',
            'SystemSession.location_id' => 34,
        )
    )
);
$conditions['group'] = 'User.id';

This would apply an INNER join on the system_sessions table with your desired conditions, eg something like:

// ...
INNER JOIN
    system_sessions SystemSession ON 
        SystemSession.user_id = User.id AND
        SystemSession.location_id = 34
GROUP BY
    User.id

and thus filter out all users for which no match exists. To retrieve the associated data you'd still need to use contain!

See also

• Cookbook > Models > Associations: Linking Models Together > Joining tables