0

I'm trying to grab some conversation records in my database however only show the latest result. I've tried the below however it groups by the first record found is there a way I can make it group by the last record on the dateline colum 

  SELECT DISTINCT*
  FROM table_messages 
  WHERE fromid=4 OR toid=4
  GROUP BY convoid
  ORDER BY dateline desc

My table is like the below

pmid - fromid - toid - convoid - dateline
 1       4        15      3      1461079193
 2       4        15      3      1461079200
 3       15        4      3      1461079220
 4       15        4      3      1461079230
 5       4        15      3      1461079270
Paradigm
  • 371
  • 2
  • 12
  • You don't need the GROUP BY. ORDER then LIMIT: http://stackoverflow.com/q/4708708/4665459 – Mark Leiber Apr 19 '16 at 15:18
  • What is convoid? Assuming pmid is the "personal message ID", why don't you group by pmid and add `LIMIT 1` at the end? – Reeno Apr 19 '16 at 15:18
  • pmid is a primary key that auto increments so doesn't help letting me know who the conversation is between. The reason I cant use limit is because I'm running a foreach as the user may have conversations with multiple people. – Paradigm Apr 19 '16 at 15:23

1 Answers1

0

To get a list of all the conversations for a specific user, and the last message sent (and it's details) for each conversation, in a single query:

SELECT
    convo.convoid,
    message.pmid,
    message.dateline,
    CASE WHEN fromid = 4 then "SENT" ELSE "RECEIVED" as direction
FROM
    (SELECT convoid, max(dateline) as maxdateline FROM table_messages GROUP BY convoid) convo
    INNER JOIN table_messages message
        ON convo.convoid = message.convoid AND
            convo.maxdateline = message.dateline
WHERE 
    fromid = 4 or toid = 4

Here we are working with two sets again. The first (from the FROM) is a list of all convoid's and their max dateline. Then we INNER JOIN those with the same table on the relationship of convoid and dateline. This will give us a record set of every conversation and it's most recent message.

Then we restrict in the WHERE clause to get just the conversations where either the fromid or the toid is the user you are interested in.

For the fun, I added the CASE statement, just to bring in more information about that last message sent (whether it was sent by the user or received by the user).

It MIGHT be quicker to do:

SELECT
    convo.convoid,
    message.pmid,
    message.dateline,
    CASE WHEN fromid = 4 then "SENT" ELSE "RECEIVED" as direction
FROM
    (SELECT convoid, max(dateline) as maxdateline FROM table_messages GROUP BY convoid WHERE fromid = 4 or toid = 4) convo
    INNER JOIN table_messages message
        ON convo.convoid = message.convoid AND
            convo.maxdateline = message.dateline

Depending on how MySQL optimizes the first query. If it tries to get every conversation and then the last message, then restricts it for the user then you can force the optimizer to instead, with this version, only get conversations for the user, then get the latest message for those conversations. My bet though is that it's a wash and that MySQL will properly optimize the first query to do what is explicitly stated in the second. But... I figured just in case, I would put it here.

For readability sake, I like the first query better since one can very quickly see the conditions in the main query's WHERE clause. Otherwise you have to hunt in the subquery.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • The issue I'm having using limit is I'm doing a while in PHP so it only returns one result rather than all the results from the convoid. – Paradigm Apr 19 '16 at 15:35
  • So is it that you want all the records for the ConvoID that has the greatest dateline? – JNevill Apr 19 '16 at 16:07
  • Yes I want to group by ConvoID that has the greatest dateline. At the moment it's grouping the first record it finds into the convoid whereas I need it to group the highest dateline record of the convoid – Paradigm Apr 19 '16 at 16:19
  • Cool. That makes sense. I've updated the answer with a query that will do that. – JNevill Apr 19 '16 at 16:22
  • I've run the below however it displays the same convoid more than once https://u.pomf.is/yjiubb.png SELECT * FROM `table_privatemessages` WHERE fromid =4 OR toid =4 AND convoid = ( SELECT DISTINCT convoid FROM table_privatemessages ORDER BY dateline DESC LIMIT 1 ) LIMIT 0 , 30 – Paradigm Apr 19 '16 at 16:28
  • This doesn't really work as it displays only one convoid whereas the user may be having multiple conversations with others. I can happily export the table if you want to try? – Paradigm Apr 19 '16 at 16:37
  • I think the problem here is that I don't understand your requirements. You have a user that is having conversations which have a dateline and to/from userids. You are wanting to find the most recent messages across multiple conversations for a specific user and limit the results to the top 30 messages? – JNevill Apr 19 '16 at 16:40
  • Ok so what I'm building is a page where the user can see there most recent conversations, the issue is if i do a while->fetch_array($query) it will display every conversation line so I need a way of showing just the latest conversation details per conversation ID. So for example i want to show the latest message and the time the last message was sent – Paradigm Apr 19 '16 at 16:43
  • That totally makes sense. "The latest message and it's time for each conversation for a specific user". – JNevill Apr 19 '16 at 16:45