CakePHP Pagination with conditions on has_many

Question

So I have a User table and a History table with User hasMany Histories, and I'm trying to implement pagination on the user table.

My problem is that I have search, and some of the things one can search by are things in the History table. Is there a way to filter pagination results based on data in a table associated by hasMany? Containable, which initially seemed like a solution, allows such filtering but only in the retrieval of associated data, not the records themselves (unless I'm missing something?)

Has anyone had to solve this before?

Answer 1

Since it's a hasMany relationship, that means Cake will need to make 2 separate queries: 1 on the users table, and one on the histories table to retrieve all the associations. Since the History data isn't being retrieved until the 2nd query, then your 1st query cannot be filtered via WHERE conditions for fields found in the History model.

To resolve this, you can do one of two things:

1. Perform pagination on History using Containable (since History belongsTo User, meaning only 1 query will be performed).

2. Perform pagination on User the way you're already doing, except perform an ad-hoc join to History such that it's no longer a hasMany relationship.

e.g.:

$this->User->bindModel(array('hasOne' => array('History')));
$this->paginate['User']['contain'][] = 'History';
$this->paginate('User', array('History.some_field' => 'some_value'));