On 23 oct, 05:32, bonecandy <[email protected]> wrote:
> Hi, this is my first post here - I'm new to CakePHP, but not to
> programming or PHP.
> Anyway, to get acquainted with Cake I've been making a blog (I know,
> crazy, huh?). So, I have my posts table and model, which has a hasMany
> relationship with comments, and my comments table and model, which has
> a belongsTo relationship with my post model.
> This works just fine - I'm using the paginate helper like so:
>
> $this->set('posts',$this->paginate('Post'));
>
> And I can access the comments just fine. However, the problem is the
> query is so inefficient!
> These are the queries Cake produces that I have issues with:
>
> SELECT `Post`.`id`, `Post`.`title`, `Post`.`url_title`, `Post`.`body`,
> `Post`.`date_created`, `Post`.`date_modified` FROM `posts` AS `Post`
> WHERE 1 = 1 ORDER BY `Post`.`id` desc LIMIT 20
>
> SELECT `Comments`.`id`, `Comments`.`user`, `Comments`.`body`,
> `Comments`.`post_id` FROM `comments` AS `Comments` WHERE
> `Comments`.`post_id` IN (22, 21, 20, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4,
> 3, 2, 1)
>
> The 'IN' MySQL operator is slow.
How slow. Feel free to propose an alternative that achieves the same
thing.
> More importantly, that extra query
> is unnecessary!
So don't ask for it :)
> Is it possible to get CakePHP to combine the two and
> produce something like this?
>
> SELECT `Post`.`id`, `Post`.`title`, `Post`.`url_title`, `Post`.`body`,
> `Post`.`date_created`, `Post`.`date_modified`,`Comments`.* FROM
> `posts` AS `Post` LEFT JOIN `comments` AS `Comments` ON
> `Comments`.`post_id` = `Post`.`id` ORDER BY `Post`.`id` desc LIMIT 20
>
> I've searched this group as well as Google and haven't really found
> much of anything about this.
Post hasOne Comment would generate that - but good luck paginating it.
As soon as any post has more than 1 comment your post index is going
to have duplicates or holes (depending on if/how you try to 'fix'
that). But why are you requesting comments at all?
AD
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
