8

Lets say this is the database structure:

enter image description here

SELECT * FROM `pms` where id_to = 1 or id_from = 1

This would return all the messages that he has recived or sent,

So how can I retrieve the last message from each conversation that the user 1 may have?

PD: I call it conversation when there is one or more messages between two users

-edit-

So given this database content:

enter image description here

We want to get id 4 and 6

Toni Michel Caubet
  • 19,333
  • 56
  • 202
  • 378

3 Answers3

6

This assumes id is an auto-increment column:

SELECT MAX(id) AS id
FROM pms
WHERE id_to = 1 OR id_from = 1
GROUP BY (IF(id_to = 1, id_from, id_to))

Assuming you have id_from and id_to indexed, this variation will most likely perform better because MySQL doesn't know what to do with an OR:

SELECT MAX(id) AS id FROM
(SELECT id, id_from AS id_with
FROM pms
WHERE id_to = 1
UNION ALL
SELECT id, id_to AS id_with
FROM pms
WHERE id_from = 1) t
GROUP BY id_with

Here's how to get the messages for those ids:

SELECT * FROM pms WHERE id IN
    (SELECT MAX(id) AS id FROM
    (SELECT id, id_from AS id_with
    FROM pms
    WHERE id_to = 1
    UNION ALL
    SELECT id, id_to AS id_with
    FROM pms
    WHERE id_from = 1) t
    GROUP BY id_with)
Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
3
select pms.* from pms 
inner join 
    (select max(fecha) as max_fecha,
           if(id_to<id_from,id_to,id_from) min_id, 
           if(id_to<id_from,id_from,id_to) max_id
      from pms where id_to = 1 or id_from = 1 
         group by if(id_to<id_from,id_to,id_from),if(id_to<id_from,id_from,id_to)) t 
     on (if(pms.id_to<pms.id_from,pms.id_to,pms.id_from)=t.min_id) 
        and (if(pms.id_to<pms.id_from,pms.id_from,pms.id_to)=t.max_id) 
        and (pms.fecha=t.max_fecha)

Also if id_to and id_from in your table are small enough to prevent overflow in statement (id_to+id_from) here is the simple query:

select pms.* from pms 
inner join 
    (select max(fecha) as max_fecha, id_to+id_from as sum_id
      from pms where id_to = 1 or id_from = 1 
         group by id_to+id_from) t 
     on ((pms.id_to+pms.id_from)=t.sum_id) 
        and (pms.fecha=t.max_fecha)
 where pms.id_to = 1 or pms.id_from = 1
valex
  • 23,966
  • 7
  • 43
  • 60
  • This is showing the last message of each user of each conversation. Its close, but we only want the last message of each conversation – Toni Michel Caubet Aug 28 '12 at 13:35
  • @ToniMichelCaubet, is this really the one you're going to use? – Marcus Adams Aug 28 '12 at 14:08
  • yours performe better? the only reason is cause i have to add all the other rows to your query and rename the max(id) as id... @MarcusAdams – Toni Michel Caubet Aug 28 '12 at 14:12
  • Works OK! But replace "GROUP BY id_to+id_from" with: "GROUP BY sum_id". And this is redundant: "((pms.id_to+pms.id_from)=t.sum_id)", also "WHERE pms.id_to = 1 or pms.id_from = 1" is a redundancy; remove that as well and the code will be much shorter / efficient. – andreszs Jul 25 '14 at 21:51
1

This query should work:

SELECT a.*
FROM pms a
     INNER JOIN (
                 SELECT id_to, id_from, MAX(fecha) AS fecha
                 FROM pms
                 WHERE (id_to = 1 OR id_from = 1)
                 GROUP BY LEAST(id_to, id_from)
                ) b
                ON a.fecha = b.fecha AND
                   (a.id_to = b.id_to OR
                   a.id_from = b.id_from);

See example @ sqlfiddle here

If you have id as PRIMARY KEY and you are logging messages in a chronological order, then it can be further optimized and simplified as:

SELECT a.*
FROM pms a
     INNER JOIN (
                 SELECT MAX(id) AS id
                 FROM pms
                 WHERE (id_to = 1 OR id_from = 1)
                 GROUP BY LEAST(id_to, id_from)
                ) b
                ON a.id = b.id;
Omesh
  • 27,801
  • 6
  • 42
  • 51