CakePHP: multiple joins, belongs and hasMany relationships performed in two queries

Question

I need help with CakePHP 2.2.3.

What i have

I have the following setup at the moment:

Post hasMany Attachment

It works fine, and the page is generated using two requests:

 SELECT *, `Post`.`id` FROM `posts` AS `Post` WHERE 1 = 1 ORDER BY `Post`.`created` DESC SELECT `Attachment`.`id`, `Attachment`.`post_id`, `Attachment`.`created` FROM `attachments` AS `Attachment` WHERE `Attachment`.`post_id` IN (1, 2, 3, ..., n) 

What I want

I want to expand the ratio as follows:

Post hasMany Attachment ; each Attachment belongs to Type

And I don't know how to get CakePHP to follow it.
Basically, I need:

 SELECT *, `Post`.`id` FROM `posts` AS `Post` WHERE 1 = 1 ORDER BY `Post`.`created` DESC SELECT `Attachment`.`id`, `Attachment`.`post_id`, `Attachment`.`created`, `Type`.`title`, `Type`.`icon` FROM `attachments` AS `Attachment` LEFT JOIN `types` AS `Type` ON (`Attachment`.`type_id`=`Type`.`id`) WHERE `Attachment`.`post_id` IN (1, 2, 3, ..., n) 

Check out the added LEFT JOIN types .

So, I get the corresponding type data in the second query. I know that I can get the data in a loop or by calling ->query() , but I want this to be as efficient and flexible as possible.

Problem

I tried Containable , the Unbinding trick model ( and this one ), but failed. I tried different combinations of parameters, I believe that I even deleted the connections. This is what my PostsController looks like.

 class PostsController extends AppController { public function index() { $this->Post->unbindModel(array('hasMany' => array('Attachment'))); $this->Post->Attachment->unbindModel(array('belongsTo' => array('Type'))); $this->Post->bindModel(array( 'hasMany' => array( 'Attachment' => array( 'className' => 'Attachment', // when uncommented, throws the "Unknown column Post.id" SQLSTATE error // 'conditions' => array('Post.id' => 'Attachment.post_id'), 'foreignKey' => false, ), ), )); $this->Post->Attachment->bindModel(array( 'belongsTo' => array( 'Filetype' => array( 'className' => 'Filetype', // 'conditions' => array('Type.id' => 'Attachment.type_id'), 'foreignKey' => false, ), ), )); $all = $this->Post->find('all', array( 'joins' => array( array( 'table' => 'users', 'prefix' => '', 'alias' => 'User', 'type' => 'INNER', 'conditions' => array( 'User.id = Post.user_id', ) ), ), 'contain' => array('Attachment', 'Type'), 'conditions' => array(), 'fields' => array('*'), 'order' => 'Post.created ASC' )); var_dump($all);exit; } } 

But it just launches an additional request for each iteration in the loop and receives all the attachments:

 SELECT `Attachment`.`id`, ... FROM `attachments` AS `Attachment` WHERE 1 = 1 

When I uncomment the condition for this association, it throws me an SQLSTATE "Post.id column not found error" - I think because there is no message table here.

I need help setting this up.

Please, help! Thanks

UPDATE

I changed the controller as follows. Please note that there is no bindModel / unbindModel , the relation is established in the model classes (is this true in this case?).

 class PostsController extends AppController { public function index() { $options = array( 'contain' => array( 'Post', 'Type' ), 'order' => 'Post.created DESC', 'conditions' => array( // 'Post.title LIKE' => 'my post' ) ); // The following throws "Fatal error: Call to a member function find() on a non-object" // $posts = $this->Attachment->find('all', $options); // So I had to use $this->Post->Attachment instead of $this->Attachment $posts = $this->Post->Attachment->find('all', $options); $this->set(compact('posts')); } } 

This is the Attachment model:

 class Attachment extends AppModel { public $belongsTo = array( 'Type' => array( 'className' => 'Type', 'foreignKey' => 'type_id', ), 'Post' => array( 'className' => 'Post', 'foreignKey' => 'post_id', ), ); } 

The above code uses this query:

 SELECT `Attachment`.`id`, `Attachment`.`type_id`, `Attachment`.`post_id`, `Attachment`.`created`, `Type`.`id`, `Type`.`title`, `Post`.`id`, `Post`.`text`, `Post`.`created` FROM `attachments` AS `Attachment` LEFT JOIN `types` AS `Type` ON (`Attachment`.`type_id` = `Type`.`id`) LEFT JOIN `posts` AS `Post` ON (`Attachment`.`post_id` = `Post`.`id`) WHERE 1 = 1 ORDER BY `Post`.`created` ASC 

Here is all about attachments. I mean, messages are attached to attachments, so if a message has no attachments, it will not be returned. This is probably due to the fact that the call is Attachment->find() , so that it is in terms of joining. I guess this should be:

 // ... FROM `posts` AS `Post` LEFT JOIN `attachments` AS `Attachment` ON (`Attachment`.`post_id` = `Post`.`id`) LEFT JOIN `types` AS `Type` ON (`Attachment`.`type_id` = `Type`.`id`) // ... 

But that doesn't work, does it? You see that there are posts , attachments and types , but they have different types of relationships. I originally posted these two separate requests that CakePHP launches - there must be a reason for this.

UPDATE2

I still think that all this relates to changing the second request to the Attachment model in the initial setup (see the “What I Want” section). Therefore, I get attachment types along with attachments. I mean that LEFT JOINING in the types table in attachments will not break the logic of the database relationship, right? I just want to make sure that there is no way to do this with one complex but single call to find() .

Answer 1

Whenever Cake sees a hasMany relationship, it automatically creates multiple queries to pull the data. When building these queries, he searches for relationships that can be associated with LEFT (hasOne and belongs to).

Since Cake cannot do this for you, you will need to combine them yourself.

 public function index() { $posts = $this->Post->find('all'); // get all attachments for all found posts $attachments = $this->Post->Attachment->find('all', array( 'contain' => array('Type'), 'conditions' => array('Post.id' => Set::extract('/Post/id', $posts) )); // now join them to the posts array foreach ($posts as $key => $data) { $postId = $data['Post']['id']; // append any data related to this post to the post array $posts[$key] += Set::extract("/Attachment[post_id=$postId]/..", $attachments); } $this->set(compact('posts')); } 

This is not the most efficient way to do this, as you will iterate over the $attachments array several times, but I'm sure you understand this idea.

Answer 2

Try finderQuery in hasMany. For example: In the Post model,

 public $hasMany = array( 'Attachment' => array( 'className' => 'Attachment', 'foreignKey' => 'post_id', 'dependent' => false, 'conditions' => '', 'fields' => '', 'order' => '', 'limit' => '', 'offset' => '', 'exclusive' => '', 'finderQuery' => ' SELECT `Attachment`.`id`, `Attachment`.`post_id`, `Attachment`.`created`, `Type`.`title`, `Type`.`icon` FROM `attachments` AS `Attachment` LEFT JOIN `types` AS `Type` ON (`Attachment`.`type_id`=`Type`.`id`) WHERE `Attachment`.`post_id` IN (1, 2, 3, ..., n) ', 'counterQuery' => '' )