如何从消息表中查找最后一条消息并查找用户

Question

This is my User table This is my Message table

And tHis is my Listing Table

Now I get successfully get the last message with this query

enter code here
$coreQueryUser = $this->Message->query('
            select m.*
            from
                messages m
                inner join (
                select max(id) as maxid
                from messages
                where messages.list_id = 3 
                group By (if(sender_id > reciever_id,  sender_id, reciever_id)), 
                (if(sender_id > reciever_id,  reciever_id, sender_id))
               ) t1 on m.id=t1.maxid 
        ');

when i run this query it gives me following output:-

enter code here
<pre>Array
(
[0] => Array
    (
        [m] => Array
            (
                [id] => 2
                [sender_id] => 10
                [reciever_id] => 21
                [list_id] => 3
                [message] => hello sir
                [add_date] => 2016-09-25 08:24:38
                [is_check] => 0
            )

    )

[1] => Array
    (
        [m] => Array
            (
                [id] => 7
                [sender_id] => 10
                [reciever_id] => 22
                [list_id] => 3
                [message] => hmm
                [add_date] => 2016-09-27 00:00:00
                [is_check] => 0
            )

    )
)

Now you see in the output in first array sender_id =10 and reciver_id = 21 and list_id = 3 List is created by user whose id is 10. in the output i want to fetch the User data whose user id 21. Remember that when reciever send the message to sender the ids are interchanging. in that case i also want the data of whose user id is 21

Please help me:(

Answer 1

Finallly i find the solution of my own

enter code here
$coreQueryUser = $this->Message->query('
            select m.* ,u.*
            from
                messages m
                inner join (
                select max(id) as maxid
                from messages
                where messages.list_id = " '. $data['list_id'] .'" 
                group By (if(sender_id > reciever_id,  sender_id, reciever_id)), 
                (if(sender_id > reciever_id,  reciever_id, sender_id))
               ) t1 on m.id=t1.maxid 
                join 
                users u  ON u.id = (CASE WHEN m.sender_id = 10
                                   THEN m.reciever_id
                                   ELSE m.sender_id        
                               END)
        ');