Re: hasMany problems/bug - need feedback
I noticed this as well but figured I may have been doing something
incorrectly but moved on to other things.
Here is my example:
I have a users table and I also have a users_users table to facilitate
following/follower type relationships. Each users profile has a
setting to make their account public or private. If their account is
private none of their information should appear in any where on the
site.
I used this to read in a user record:
$this-User-Behaviors-attach('Containable');
$this-User-contain(array(
'Profile',
'Friend.Profile' = array(
'conditions' = array(
'Profile.public'=1
)
),
'FriendOf.Profile' = array(
'conditions' = array(
'Profile.public'=1
)
)
));
The queries cake ends up using are as follow:
8 SELECT `Friend`.`id`, `Friend`.`name`, `Friend`.`password`,
`Friend`.`group_id`, `Friend`.`created`, `Friend`.`modified`,
`Friend`.`last_login`, `UsersUser`.`id`, `UsersUser`.`created`,
`UsersUser`.`user_id`, `UsersUser`.`friend_id` FROM `users` AS
`Friend` JOIN `users_users` AS `UsersUser` ON (`UsersUser`.`user_id` =
'4c269d7f-96f8-42ac-8deb-2f5dadcb7441' AND `UsersUser`.`friend_id` =
`Friend`.`id`) LIMIT 2048 27 27 1
Found 27 friends. (I limit to 2048 in the model)
9 SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`created`,
`Profile`.`modified`, `Profile`.`prev_login`, `Profile`.`first_name`,
`Profile`.`last_name`, `Profile`.`display_name`,
`Profile`.`birthdate`, `Profile`.`public`, `Profile`.`photo_url` FROM
`profiles` AS `Profile` WHERE `Profile`.`user_id` =
'4c37bd82-15b0-45d9-a1ef-444badcb7441' AND `Profile`.`public` = 1
1
1 1
Now it begins testing the publicness of the profiles not what I
wanted. I expected it to be taken care of in the first query.
This one was public and will be returned.
10 SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`created`,
`Profile`.`modified`, `Profile`.`prev_login`, `Profile`.`first_name`,
`Profile`.`last_name`, `Profile`.`display_name`,
`Profile`.`birthdate`, `Profile`.`public`, `Profile`.`photo_url` FROM
`profiles` AS `Profile` WHERE `Profile`.`user_id` =
'4c295a6c-131c-4895-894a-3a18adcb7441' AND `Profile`.`public` = 1 0
0 0
This one was public.
This continues on for the remaining users listed.
My associations are:
Profile belongsTo user
User hasOne Profile
I'm not sure if any of this helps your research but if I'm doing it
wrong I would be glad to hear the correct way.
On Aug 27, 1:28 pm, Dan Heberden danheber...@gmail.com wrote:
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` =
Re: hasMany problems/bug - need feedback
I noticed this as well but figured I may have been doing something
incorrectly but moved on to other things.
Here is my example:
I have a users table and I also have a users_users table to facilitate
following/follower type relationships. Each users profile has a
setting to make their account public or private. If their account is
private none of their information should appear in any where on the
site.
I used this to read in a user record:
$this-User-Behaviors-attach('Containable');
$this-User-contain(array(
'Profile',
'Friend.Profile' = array(
'conditions' = array(
'Profile.public'=1
)
),
'FriendOf.Profile' = array(
'conditions' = array(
'Profile.public'=1
)
)
));
The queries cake ends up using are as follow:
8 SELECT `Friend`.`id`, `Friend`.`name`, `Friend`.`password`,
`Friend`.`group_id`, `Friend`.`created`, `Friend`.`modified`,
`Friend`.`last_login`, `UsersUser`.`id`, `UsersUser`.`created`,
`UsersUser`.`user_id`, `UsersUser`.`friend_id` FROM `users` AS
`Friend` JOIN `users_users` AS `UsersUser` ON (`UsersUser`.`user_id` =
'4c269d7f-96f8-42ac-8deb-2f5dadcb7441' AND `UsersUser`.`friend_id` =
`Friend`.`id`) LIMIT 2048 27 27 1
Found 27 friends. (I limit to 2048 in the model)
9 SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`created`,
`Profile`.`modified`, `Profile`.`prev_login`, `Profile`.`first_name`,
`Profile`.`last_name`, `Profile`.`display_name`,
`Profile`.`birthdate`, `Profile`.`public`, `Profile`.`photo_url` FROM
`profiles` AS `Profile` WHERE `Profile`.`user_id` =
'4c37bd82-15b0-45d9-a1ef-444badcb7441' AND `Profile`.`public` = 1
1
1 1
Now it begins testing the publicness of the profiles not what I
wanted. I expected it to be taken care of in the first query.
This one was public and will be returned.
10 SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`created`,
`Profile`.`modified`, `Profile`.`prev_login`, `Profile`.`first_name`,
`Profile`.`last_name`, `Profile`.`display_name`,
`Profile`.`birthdate`, `Profile`.`public`, `Profile`.`photo_url` FROM
`profiles` AS `Profile` WHERE `Profile`.`user_id` =
'4c295a6c-131c-4895-894a-3a18adcb7441' AND `Profile`.`public` = 1 0
0 0
This one was public.
This continues on for the remaining users listed.
My associations are:
Profile belongsTo user
User hasOne Profile
I'm not sure if any of this helps your research but if I'm doing it
wrong I would be glad to hear the correct way.
On Aug 27, 1:28 pm, Dan Heberden danheber...@gmail.com wrote:
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` =
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
