hasMany problems/bug - need feedback

This isn't a "i cant get this or that model or relationship" question,
btw :)

(this is cake 1.3.2 an i've shortened the selects for readability
sake)

Consider these models/relationships: User, Group, Post

User belongsTo Group
User hasMany Post

With recursive set to 1, you'd think that the select would join users
to the group AND the posts. NOT the case.

Here's an example of what cake is generating:
SELECT * FROM `users` AS `User`
LEFT JOIN `groups` AS `Group` ON (`User`.`group_id` =
`Group`.`id`)

so it has a crap load of student rows. Then it creates this:

SELECT * FROM `posts` as `Post`
WHERE `Post`.`user_id` IN ( 1, 2, 3, 4 , 5)

So basically it is looping through the first record set, building the
IN query and getting them manually.

It SHOULD be doing this:

SELECT * FROM `users` AS `User`
LEFT JOIN `groups` AS `Group` ON (`User`.`group_id` =
`Group`.`id`)
LEFT JOIN `posts` AS `Post`
ON (`Post`.`user_id` = `Student`.`id` AND
`Post`.`status` < 2
AND `Post`.`status` >= 0)

Not only for network traffic and multiple sql calls, but speed. The
first call takes 140ms with the data I have. The joined one takes
74ms.

This gets even worse with containable. Instead of joining the group,
it SELECTS every group by id for every user. It's generating like 70
queries instead of one join.

I want to make sure i'm not missing something before I post this as a
bug. Have any of you noticed this?

I have tried to see if different levels of recursion affect the
generated sql, but, as you'd expect it does what it's supposed to to
in traversing levels up or down.

Another example, If I add a "Comment" model to the mix (Post hasMany
Comments) and do this:
$this->contain( array( 'Group', 'Post' => array('Course') ));

The first generated SQL is the join of the users to groups as before,
but then is ALSO generates individual lines of SQL to get each group
(again).

So it makes this: SELECT * FROM `users` AS `User`
LEFT JOIN `groups` AS `Group` ON (`User`.`group_id` =
`Group`.`id`)

Then these:
SELET * FROM `groups` as `Group` WHERE `Group`.`id` = 1;
SELET * FROM `groups` as `Group` WHERE `Group`.`id` = 2;
SELET * FROM `groups` as `Group` WHERE `Group`.`id` = 3;
SELET * FROM `groups` as `Group` WHERE `Group`.`id` = 4;

It gets the Posts as before, but then generates individual SQL calls
for EACH comment.

Messed up, right?

Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en