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`
Any idea how to solve this last bit?
Best,
Mario
On Sep 29, 4:36 pm, brian <[email protected]> wrote:
> Try something like this:
>
> $this->Message->find(
> 'all',
> array(
> 'joins' => array(
> array(
> 'type' => 'LEFT',
> 'table' => 'users',
> 'alias' => 'Sender',
> 'conditions' => array(
> 'Message.user_id' => 'Sender.id'
> )
> ),
> array(
> 'type' => 'LEFT',
> 'table' => 'users',
> 'alias' => 'Recipient',
> 'conditions' => array(
> 'Message.recipient_id' =>
> 'Recipient.id'
> )
> )
> )
> )
> );
>
> You might need to add a couple more arrays to the joins array for the
> Profiles. Maybe something like this:
>
> array(
> 'type' => 'LEFT',
> 'table' => 'profiles',
> 'alias' => 'SenderProfile',
> 'conditions' => array(
> 'SenderProfile.user_id' => 'Sender.id'
> )
> ),
> array(
> 'type' => 'LEFT',
> 'table' => 'profiles',
> 'alias' => 'RecipientProfile',
> 'conditions' => array(
> 'RecipientProfile.user_id' => 'Recipient.id'
> )
> )
>
>
>
> On Tue, Sep 29, 2009 at 8:22 AM, Mario <[email protected]> wrote:
>
> > Hi John,
>
> > thank you!
> > Is just very basic, no control over depth of joins.
>
> > <?php
> > class MessagesController extends AppController {
>
> > var $name = 'Messages';
>
> > function index() {
> > $this->data = $this->Message->find('all');
> > debug ($this->data);
> > die;
> > }
> > ?>
>
> > It seems to me that joins are not in place because of somehow missing
> > aliases association, not because I have cut them somewhere.
>
> > The underlying SQL is:
> > SELECT `Message`.`id`, `Message`.`user_id`, `Message`.`recipient_id`,
> > [...],
> > `Sender`.`id`, `Sender`.`username`, `Sender`.`password`, [...],
> > `Recipient`.`id`, `Recipient`.`username`, `Recipient`.`password`,
> > [...]
> > FROM `messages` AS `Message`
> > 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
>
> > Best,
>
> > Mario
>
> > On Sep 29, 1:55 pm, John Andersen <[email protected]> wrote:
> >> Thanks, a little more information is needed!
> >> Please show the code from the controller, where you are using
> >> $this->Message->find.
>
> >> Are you using the Containable behaviour or just the recursive option?
> >> Enjoy,
> >> John
>
> >> On Sep 29, 1:43 pm, Mario <[email protected]> wrote:
>
> >> > Hi John,
>
> >> > thank you for your reply.
>
> >> > As stated, is done exactly as in the Cookbook:
>
> >> > <?php
> >> > class Message extends AppModel {
> >> > var $name = 'Message';
> >> > var $belongsTo = array(
> >> > 'Sender' => array(
> >> > 'className' => 'User',
> >> > 'foreignKey' => 'user_id'
> >> > ),
> >> > 'Recipient' => array(
> >> > 'className' => 'User',
> >> > 'foreignKey' => 'recipient_id'
> >> > )
> >> > );}
>
> >> > ?>
>
> >> > <?php
> >> > class User extends AppModel {
> >> > var $name = 'User';
> >> > var $hasMany = array(
> >> > 'MessageSent' => array(
> >> > 'className' => 'Message',
> >> > 'foreignKey' => 'user_id'
> >> > ),
> >> > 'MessageReceived' => array(
> >> > 'className' => 'Message',
> >> > 'foreignKey' => 'recipient_id'
> >> > )
> >> > );
>
> >> > var $hasOne = 'Profile'; // this for the Profile association}
>
> >> > ?>
>
> >> > Best,
>
> >> > Mario
>
> >> > On Sep 29, 12:35 pm, John Andersen <[email protected]> wrote:
>
> >> > > Please provide information on how you relates the message model with
> >> > > the other models?
> >> > > Enjoy,
> >> > > John
>
> >> > > On Sep 29, 12:52 pm, Mario <[email protected]> wrote:
>
> >> > > > Hi all,
>
> >> > > > probably a not so difficult question, but I am a newbie to CakePHP
> >> > > > and
> >> > > > would really appreciate some advice.
>
> >> > > > I have coded something exactly following the situation described
> >> > > > inhttp://book.cakephp.org/view/851/Multiple-relations-to-the-same-model
> >> > > > that is: a User model, a Message model, Sender and Recipient aliases.
> >> > > > Everything works smoothly.
>
> >> > > > Then I have a Profile model one-to-one associated with User, here as
> >> > > > well following the Cookbookhttp://book.cakephp.org/view/80/hasOne
> >> > > > <?php
> >> > > > class User extends AppModel {
> >> > > > var $name = 'User';
> >> > > > var $hasOne = 'Profile';}
>
> >> > > > ?>
> >> > > > andhttp://book.cakephp.org/view/81/belongsTo
> >> > > > <?php
> >> > > > class Profile extends AppModel {
> >> > > > var $name = 'Profile';
> >> > > > var $belongsTo = 'User';}
>
> >> > > > ?>
> >> > > > Relationship working fine except for the following...
>
> >> > > > The problem I have is that $this->Message->find('all') in
> >> > > > MessagesController doesn't return the Profile data for the Sender and
> >> > > > the Recipient. They are aliases of User, so I can expect that maybe
> >> > > > some extra code is needed somewhere.
>
> >> > > > I tried to modify the Profile model into something like:
> >> > > > var $belongsTo = array(
> >> > > > 'User',
> >> > > > 'Sender' => array(
> >> > > > 'className' => 'User',
> >> > > > 'foreignKey' => 'user_id'
> >> > > > ),
> >> > > > 'Recipient'...
> >> > > > );
>
> >> > > > Doesn't work, half expected it, because it seems more important in
> >> > > > this case to declare that the Sender (Recipient) 'hasOne' Profile.
> >> > > > But
> >> > > > where and how, not having a Sender (Recipient) model?
>
> >> > > > Thank you in advance,
>
> >> > > > Mario
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
