On Tue, Sep 29, 2009 at 12:02 PM, Mario <[email protected]> wrote: > > Hi Brian, > > thank you for your help, getting closer to an initial solution. > > With the following find (slightly different from yours, now profiles > are joined with the user_id / recipient_id in Message, like it is done > for users) > > $this->data = $this->Message->find( > 'all', > array( > 'joins' => array( > array( > 'type' => 'LEFT', > 'table' => 'profiles', > 'alias' => 'SenderProfile', > 'conditions' => array( > 'Message.user_id' => 'SenderProfile.user_id' > ) > ), > array( > 'type' => 'LEFT', > 'table' => 'profiles', > 'alias' => 'RecipientProfile', > 'conditions' => array( > 'Message.recipient_id' => 'RecipientProfile.user_id' > ) > ) > ) > ) > ); > > I get the following SQL (where instead of '*' there is the complete > list of not NULL fields) > > SELECT * > FROM `messages` AS `Message` > LEFT JOIN profiles AS `SenderProfile` ON (`Message`.`user_id` = > 'SenderProfile.user_id') > LEFT JOIN profiles AS `RecipientProfile` ON (`Message`.`recipient_id` > = 'RecipientProfile.user_id') > LEFT JOIN `users` AS `Sender` ON (`Message`.`user_id` = `Sender`.`id`) > LEFT JOIN `users` AS `Recipient` ON (`Message`.`recipient_id` = > `Recipient`.`id`) WHERE 1 = 1 > > Still not working, because SenderProfile and RecipientProfile returns > NULL fields. > > But if in PHPMyAdmin I change the SQL to > > SELECT * > FROM `messages` AS `Message` > LEFT JOIN profiles AS `SenderProfile` ON (`Message`.`user_id` = > `SenderProfile`.`user_id`) > LEFT JOIN profiles AS `RecipientProfile` ON (`Message`.`recipient_id` > = `RecipientProfile`.`user_id`) > LEFT JOIN `users` AS `Sender` ON (`Message`.`user_id` = `Sender`.`id`) > LEFT JOIN `users` AS `Recipient` ON (`Message`.`recipient_id` = > `Recipient`.`id`) WHERE 1 = 1 > > the above mentioned fields are no longer NULL, it seems that the query > is working. > I just changed (almost impossible to detect...): > 'SenderProfile.user_id' into `SenderProfile`.`user_id` > 'RecipientProfile.user_id' into `RecipientProfile`.`user_id`
Thanks for pointing it out. I was on my 3rd pass without seeing the difference. :-) > Any idea how to solve this last bit? You could try the condition as a single string: 'Message.recipient_id = RecipientProfile.user_id' and 'Message.user_id = SenderProfile.user_id' --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
