CakePHP: few joins, belongsTo and hasMany relations done in two queries
I need some 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 with 2 queries:
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 extend the relation to be as follows:
Post hasMany Attachment; every Attachment belongsTo Type
And I don't know hot to make CakePHP follow it.
Basically, what I need is:
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)
Note the LEFT JOIN types added.
So I get the corresponding type data in the second query. I know I could get the data in a loop or using a ->query() call, but I want this to be as much effective and flexible as possible.
The problem
I tried the Containable, Model Unbinding trick (and this one) but no success. I tried different combinations of the options, I believe I've even removed joins. Here's what my PostsController looks like now.
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 runs an extra query per each iteration in a loop and gets all the attachments:
SELECT `Attachment`.`id`, ...
FROM `attachments` AS `Attachment`
WHERE 1 = 1
When I uncomment the condition for this association, it throws me the SQLSTATE "Column Post.id not found error" - I guess because there's no Post table joined here.
I need a hand in setting this up.
Please help! Thanks
UPDATE
I've changed the controller as follows. Please note there's no bindModel/unbindModel code, the relation is set in the models classes (is that correct 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 runs 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
Everything is about the attachments here. I mean the posts are joined to attachments, so if the post has no attachments, it's not returned. This is probably because the call is Attachment->find() so it's from the attachment's point of view. I guess it just 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 it's not going to work, is it? You see there are posts, attachments and types, but they do have the different relation types. Originally, I've posted those two separate queries CakePHP runs - there must be reasons for that.
UPDATE2
I still believe that it's all about changing the second query to the Attachment model in the initial setup (please see the What I Want section). So I will get attachments types along with attachments themselves. I mean in that case LEFT JOINing the types table to attachments is not going to break any database relation logic, is it?
I just want to make sure there's no way to do that with one complex, but single find() call.
Answer
Whenever Cake sees a hasMany relationship, it will automatically create multiple queries to pull the data. While constructing those queries, it looks for relationships that can be LEFT joined to it (hasOne and belongsTo).
Since Cake can't do this for you, you will need to merge 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's array
$posts[$key] += Set::extract("/Attachment[post_id=$postId]/..", $attachments);
}
$this->set(compact('posts'));
}
This is not the most efficient way to do it since you'll iterate through the $attachments array multiple times, but I'm sure you get the idea.