StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You are probably getting two answers per chat because you would have each person in both positions</p> <pre><code>ChatID Sender Receiver 1 1 2 2 2 1 3 1 2 4 2 1 So, you'll have a record grouped by Sender / Receiver 1 2 and 2 1 </code></pre> <p>I think what you are looking for is distinct people in the conversation where the above should be considered "ONE CHAT" regardless of the position they are in. To correct this, I would do something like...</p> <pre><code>select PreQuery.*, m2.Message, m2.SenderID, m2.ReceiverID from ( SELECT if( m.senderID < m.receiverID, m.senderID, m.receiverID ) as Person1, if( m.senderID < m.receiverID, m.receiverID, m.senderID ) as Person2, max( m.ID ) as LastMessageIDPerChat, max( m.AddedDate ) as LastMessageDate FROM messages m WHERE m.Source = "1" AND "1" IN ( SenderID, ReceiverID ) GROUP BY Person1, Person2 ORDER BY m.AddedDate DESC LIMIT 10 ) PreQuery JOIN Messages m2 on PreQuery.LastMessageIDPerChat = m2.ID </code></pre> <p>This will ensure that whichever ID is lower will always be in the first position, and whichever person's ID is higher is always in second position to prevent false duplicates as described above.</p> <p>Also note... GROUP BY is typically expecting all non-group by fields to be associated with some aggregation, otherwise, it will just grab the first instance of a record found with a qualifying condition. So, if you want to include same people in conversations on different days, you'll want to add "AddedDate" to the group by so it could have... </p> <pre><code>Person1 Person2 on 4/20 Person1 Person3 on 4/20 Person3 Person4 on 4/20 Person1 Person2 on 4/18 Person1 Person5 on 4/17 Person3 Person4 on 4/15 </code></pre> <p>To get the status of who sent last, I had to wrap the query of paired people per conversation and get the last ID of that conversation. THEN, take that and re-join back to messages on that ID (if ID is actually the primary key ID of the messages table, adjust as needed). From the join, I can get the last message for the conversation AND who the sender ID and Receiver ID were for that transaction (and any other data you want from the now alias "m2" reference).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload