How can I minimize the 'contain' queries in CakePHP?

Question

I have three models, Users, Comments and Pages.

Users has many Comments, and Comments belong to Pages.

All models use the containable behavior, and default to recursive -1.

If I call a find() query on Comments, with the contain request including the Page model's field, this correctly returns the results using a single query, automagically joining the Page table to the user.

If I call a similar query from the User model (containing Comment and Comment.Page), the result is a query to source the Comments, followed by a query per comment to source the relevant Page.

Is there a way to configure the models to maintain the JOIN optimisation? I assumed the belongsTo declaration on the related model (Comments) would follow through to the host model (Users).

UPDATE

I should clarify, my question used a simplified version of my actual case study. Although the minimal solution I require would include this initial Model hasMany Model belongsTo Model structure, I am also looking for the solution at one or more additional belongsTo Models down the chain (which, I though, would automagically use LEFT JOINs, as this would be feasible).

Answer 1

Hmm that's interesting. That's a sort of optimization that should be implemented in the core :)

At any rate, I think you could get the same results (perhaps formatted differently) by building the query a little differently:

$this->User->Comment->find('all', array(
  'conditions' => array(
    'Comment.user_id' => $userId
  ),
  'contain' => array(
    'User',
    'Page'
  )
));

By searching from the Comment model, it should use two left joins to join the data since they are both 1:1 relationships. Note: The results array may look a little different than from when you search from the User model.

Answer 2

So are you asking if there is an easier way to just contain all your queries? If you want to contain everything within the current controller. You could do the contain in the beforeFilter() callback and it would apply to all your queries within that controller.

Answer 3

I am not quite sure if I understand your question, but I think you have a problem with the many sql-calls for the Comment -> Page linkage? If that is correct, then

1. try linkable behaviour which reduces sql calls and works almost as contain does
2. or if its pretty much the same data you want, then create a function in a specific model from where you are happy with hte sql calls (for example the Comment-Model) and call it from the user model by $this->Comment->myFindFct($params);

hope that helps

EDIT: one thing that comes to my mind. You were able to change the join type in the association array to inner, which made cake to single call the associated model as well

Answer 4

I find a good way to do this is to create a custom find method.

As a for instance I'd create a method inside your User model say called _findUserComments(). You'd then do all the joins, contains, etc.. inside this method. Then in your controllers, wherever you need to get all of your user's comments you would call it thusly:

$this->User->find('UserComments', array(
    "conditions" => array(
        'User.id' => $userId
    )
));

I hope this helps.

Answer 5

If model definition like bellow:

1. Comment model belongs to Page and User.
2. Page belongs to User and has many Comment.
3. User has many Page and Comment

code bellow will return one joined query:

$this->loadModel('Comment');
$this->Comment->Behaviors->attach('Containable');
$queryResult = $this->Comment->find('all', array(
   'contain' => array(
       'User', 
       'Page'
    )
));

The code bellow will return two query. Page and User joined into one query and all comment in another query

$this->loadModel('Page');
$this->Page->Behaviors->attach('Containable');
$queryResult = $this->Page->find('all', array(
   'contain' => array(
        'User', 
    'Comment'
   )
));

and also bellow code will return three query, one for each model:

$this->loadModel('User');
$this->User->Behaviors->attach('Containable');
$queryResult = $this->User->find('all', array(
    'contain' => array(
        'Page', 
        'Comment'
    )
));