5

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).

Rhys
  • 1,581
  • 2
  • 14
  • 22

5 Answers5

2

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.

jeremyharris
  • 7,884
  • 22
  • 31
  • Thanks jeremyharris. This would work, but I did have to clarify my question above to show that the real case study require more levels of belongsTo connnections. I will keep this in mind as a possibility though if I have to build my queries manually, this will save a little time. – Rhys Oct 24 '12 at 20:23
1

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.

Colby Guyer
  • 594
  • 3
  • 14
  • 1
    Hi Colby. Have you got an example of this in practice? As I think this approach may help with one of my own projects as well as the original poster’s. – Martin Bean Oct 24 '12 at 12:44
  • I believe something like this in your controller would contain your queries for the entire controller. `function beforeFilter() { parent::beforeFilter(); $this->Model->contain('RelatedModelStuff'); }//beforeFilter` – Colby Guyer Oct 24 '12 at 13:44
  • Thanks Colby, this isn't quite what I was after, but the beforeFilter contain declaration is a useful snippet, cheers. – Rhys Oct 24 '12 at 20:25
1

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

Community
  • 1
  • 1
harpax
  • 5,986
  • 5
  • 35
  • 49
1

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.

Rob Forrest
  • 7,329
  • 7
  • 52
  • 69
  • Thanks Rob. I do use custom find methods quite extensively, they are useful, but this would still not get around the situation of building extremely manual versions of the query (joins, etc) to achieve what I was to understand would be optimised automatically by the core. – Rhys Oct 31 '12 at 22:01
1

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'
    )
));
Habibillah
  • 27,347
  • 5
  • 36
  • 56
  • Thank you, though the concern I have is for a model structure that has multiple belongTo connections down the chain, rather than a model that belongsTo two different models. Eg. Returning a single query where Model1 belongsTo Model2, Model2 belongsTo Model3, and Model3 belongsTo Model4. The data can be returned in a single query using a custom statement but my understanding is the core should handle this optimisation anyway. – Rhys Nov 03 '12 at 23:30
  • I'm sorry if I don't understand your statement here. But on your question "Users has many Comments, and Comments belong to Pages" equivalent to my model definition above (see point 3 and 1). And the query result if you call from User model has been optimized by just return three query (not repeatedly query to page per comment). – Habibillah Nov 04 '12 at 00:43
  • Sorry, I think my _update_ clarified the more in-depth case study (of chained belongTos) when I realized that the solution of the original case study would be solvable without actually covered the context of my question – Rhys Nov 04 '12 at 01:11