由MySQL中的发送方接收方或接收方发送方分组

Question

I am trying to make an inbox section of messages. My problem goes like this.

id   userfrom   userto

1                     a                b

2                     b                a
now i have to select only one message of conversation between a and b.I have also tried some mysql queries like this one

$query="select * 
        from messages 
        where userfrom='a' || userto='a' 
        group by userto 
        order by id desc"

but it shows both messages of conversation but i only want to show only one message of the conversation

Answer 1

You can try the following conditional group by query:

select * 
from messeages 
where userfrom='a' OR userto='a' 
group by IF(userfrom > userto, userfrom,userto),
         IF(userfrom > userto, userto,userfrom)
order by id desc

<kbd>WORKING DEMO</kbd>

Note: Actually in the GROUP BY clause the group by is done based on the following logic:

GROUP BY greater value of (userfrom,userto), lower value of(userfrom,userto)

Example:

userfrom ='a' and userto='b' 

userfrom ='b' and userto='a'

In the above two rows greater value is 'b' and lower value is 'a.Thus from both rows GROUP BY will be applied first on b then on a.