Pagination with Containable conditions work with hasOne, but not with hasMany

Question

For example, I have this relationship:

UserContact hasMany Contact
Contact hasOne Info
Contact hasMany Response

And I need to paginate Contact, so I use Containable:

$this->paginate = array(
            'limit'=>50,
            'page'=>$page,
            'conditions' =>array('Contact.id'=>$id),
            'contain'=>array(
                'Response',
                'Info'
                )
            );

I want to add search by Info.name, and by Response.description. It works perfect for Info.name, but it throws an error if I try using Response.description, saying that the column doesn't exist.

Additionally, I tried changing the relationship to Contact hasOne Response, and then it filters correctly, but it only returns the first response and this is not the correct relationship.

So, for example, if I have a search key $filter I'd like to only return those Contacts that have a matching Info.name or at least one matching Response.description.

score 3 · Accepted Answer · answered Jan 08 '12 at 21:29

If you look at how CakePHP constructs SQL queries you'll see that it generates contained "single" relationships (hasOne and belongsTo) as join clauses in the main query, and then it adds separate queries for contained "multiple" relationships.

This makes filtering by a single relationship a breeze, as the related model's table is already joined in the main query.

In order to filter by a multiple relationship you'll have to create a subquery:

// in contacts_controller.php:
$conditionsSubQuery = array(
  'Response.contact_id = Contact.id',
  'Response.description LIKE' => '%'.$filter.'%'
);
$dbo = $this->Contact->getDataSource();
$subQuery = $dbo->buildStatement(array(
    'fields' => array('Response.id'),
    'table' => $dbo->fullTableName($this->Contact->Response),
    'alias' => 'Response',
    'conditions' => $conditionsSubQuery
), $this->Contact->Response);
$subQuery = ' EXISTS (' . $subQuery . ') ';

$records = $this->paginate(array(
    'Contact.id' => $id,
    $dbo->expression($subQuery)
));

But you should only generate the subquery if you need to filter by a Response field, otherwise you'll filter out contacts that have no responses.

PS. This code is too big and ugly to appear in the controller. For my projects I refactored it into app_model.php, so that each model can generate its own subqueries:

function makeSubQuery($wrap, $options) {
    if (!is_array($options))
        return trigger_error('$options is expected to be an array, instead it is:'.print_r($options, true), E_USER_WARNING);
    if (!is_string($wrap) || strstr($wrap, '%s') === FALSE)
        return trigger_error('$wrap is expected to be a string with a placeholder (%s) for the subquery. instead it is:'.print_r($wrap, true), E_USER_WARNING);

    $ds = $this->getDataSource();

    $subQuery_opts = array_merge(array(
        'fields' => array($this->alias.'.'.$this->primaryKey),        
        'table' => $ds->fullTableName($this),        
        'alias' => $this->alias,   
        'conditions' => array(),     
        'order' => null, 
        'limit' => null,
        'index' => null, 
        'group' => null
    ), $options);

    $subQuery_stm = $ds->buildStatement($subQuery_opts, $this);
    $subQuery = sprintf($wrap, $subQuery_stm);
    $subQuery_expr = $ds->expression($subQuery);
    return $subQuery_expr;
}

Then the code in your controller becomes:

$conditionsSubQuery = array(
    'Response.contact_id = Contact.id',
    'Response.description LIKE' => '%'.$filter.'%'
);
$records = $this->paginate(array(
    'Contact.id' => $id,
    $this->Contact->Response->makeSubQuery('EXISTS (%s)', array('conditions' => $conditionsSubQuery))
));

That looks great! I'll definitely give a shot in the following days(before bounty expires), and let you know. — Nick Zinger, Jan 09 '12 at 12:33
Turn out I won't be using this code, this time, since I actually need to search for multiple things at the same time, so removing Contacts without Responses will cause problems. Nevertheless, the answer is correct(there isn't a cake way) and the code provided will definitely come in handy in another situation, so I'll reward you the bounty. Thanks! — Nick Zinger, Jan 12 '12 at 18:00

score 0 · Answer 2 · answered Jan 06 '12 at 22:51

0

I can not try it now, but should work if you paginate the Response model instead of the Contact model.

answered Jan 06 '12 at 22:51

kaklon

2,422
2
26
39

Pagination would work then, but wouldn't do what I need. I need pagination for all the Contacts (with Responses grouped by Contact). – Nick Zinger Jan 07 '12 at 18:09

Pagination with Containable conditions work with hasOne, but not with hasMany

2 Answers2