Tuesday, September 29, 2009

Re: Associating model aliases

On Tue, Sep 29, 2009 at 12:02 PM, Mario <mario.callisto@gmail.com> 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 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
-~----------~----~----~----~------~----~------~--~---

No comments: