CakePHP search in hasMany relations

Question

Hello,

I have a model Content which belongsTo Categories, hasMany Publishers, and Publisher belongsTo city. There is also a search form where someone selects from a drop-down box which category to view and which city.

But how can I combine these two in a single paginate condition? I mean I cannot do something like:

$this->paginate('Content',array('conditions' =>array('Category.id'=>$category,
'City.id'=>$city)));

because to get cities cake performs a different query.

Nor can I do something like:

$this->paginate('Content',array('conditions' =>array('Category.id'=>$category),
 'contain'=>array('Publisher.City'=>array('conditions'=>array(City.id'=>$city)))));

because this will search according to category and filter the city results according to $city.

I know I can do something like:

$this->Content->Publisher->City->find(...)

but this will change the output of my paginated data.

What I usually do is write my custom query where I LEFT join all models and filter the results in WHERE. But I wanted to aks if there is a more cake (sic) way!

thanks

Answer 1

Hello gong.

Check out the Containable behavior of cakephp. This could solve your problem. In addition, if you are playing around with associations, you maybe want to read about the bindModel() and the unbindModel() functions.

Kind regards, Benjamin.

Edit 1: Just as an example of how to approach such an issue:

function index() {
  $this->User->recursive = 0;
  $this->User->unbindModel(array('hasOne'=> array('Remark'),'belongsTo' => array('Assoc')));
  $result = $this->User->find('all');
  $this->set('users', $this->paginate());
  $this->set('result',$result);
}

Of course you have to tailor the code snippet to your needs (e.g. use bindModel)). With the recursive parameter you can determine the depth of your following search to a certain degree. Make sure you check the outcome of a changed value of recursive with a debug($result).

Edit 1: As your inputs via the search form are category_id and city_id (indirectly), you could try formulating your call from the publishers controller instead of the contents controller. Your publishers tables has a *city_id* as every publisher *belongs_to* a city. As the hasMany relationship should be available by default, you should then be able to use something like Content.category_id in your conditions (you could check by debug(), and let me know if it works).

Edit 2: @Gong: thanks for your last comment. I have to overhaul the whole answer here, I think. In belongsTo and hasOne relationships cakephp establishes the joins by default. In hasManyBelongsTo and hasMany relationships this is not the case, but as you work with pagination, you could do something like the snippet below. If you need another reference: joining tables in cakephp

$this->paginate = array(
  'joins'=>array(
             array(
               'table'=>'accounts',
               'alias'=>'Account',
               'type' =>'inner',
               'conditions' =>array('User.id = Account.user_id')
             )
           ),
   'conditions'=>array('Account.name'=> $this->params['named']['nickname'])
);

$users = $this->paginate();
$this->set('users',$users);
debug($users);
$this->render('/users/index');

The first 'conditions' states where the join should occur, whereas the second 'conditions' is the actual selection.

Of course this shows only the framework of what you could write in your controller action. Instead of inner, you could also provide left or right.