How to write complex query for three tables?

Question

Please help me.

I am having three tables as,

Users:id,name....

Message:id,title....

Messages_Users:id,message_id,sender_id,receiver_id......

So i am using HABTM relation in message model for this as,

var $hasAndBelongsToMany = array(
    'Users' => array (
        'className' => 'User',
        'join_table' => 'messages_users',
        'foreignKey' => 'message_id',
        'associationForeignKey' => 'reciever_id',
        'conditions'=> array('MessagesUser.reciever_id => $this->Session->read("Id")')
    )
);

So now i want to write a sql query to fetch the name of all the friends of the user who has sent messages to that particular logged in use.

i,e....when the user log in he will get list of messages he got along wid the name of the senders.

how to write query for this in messages_controller??

please let me if anybody is having any idea..

score 0 · Answer 1 · answered Dec 14 '10 at 14:56

I've no idea how the ORM works in cackephp, but the query you need is....

SELECT users.name, users.id, COUNT(*)
FROM users, message_users
WHERE users.id=message_users.sender_id
AND message_users.receiver_id=$current_user_id
GROUP BY users.name, users.id
ORDER BY COUNT(*) DESC

i.e. using the messages table is unnecessary and will make your code run significantly slower.

Note that the database is NOT properly normalized - even if a user can send the same message to multiple recipients (not receivers), I can't imagine that multiple users can send the same message.

How to write complex query for three tables?

1 Answers1