1

So here is my stripped table from the messages module of an app I'm writing.

when I run the following command it goes as expected

SELECT * FROM messages WHERE to_user = 1 OR from_user = 1 GROUP BY from_user

returns

The drama is that I want to have just one instance of 2 | 1 or 1 | 2 , since in my app I'm trying to group the messages based on the username that has sent the message. I tried without the OR clause, but then when 1 send a message to 2 the message does not appear until he gets a reply from 2. 1 and 2 are dynamic from php variables

quietmint
  • 13,885
  • 6
  • 48
  • 73
Ando
  • 1,802
  • 4
  • 25
  • 47

4 Answers4

3

Since you're grouping by username, I would select a single valid list of user ID:

SELECT DISTINCT from_user FROM messages WHERE to_user = 1 
UNION
SELECT DISTINCT to_user FROM messages WHERE from_user = 1 

If you don't mind duplicates, UNION ALL and removing DISTINCT will improve performance.

Matt S
  • 14,976
  • 6
  • 57
  • 76
  • UNION will make the list distinct. UNION ALL will be faster but return duplicates (which may be OK, I don't know). – Matt S Sep 14 '12 at 21:14
2
  1. You cannot have aggregate and non-aggregate fields in your query result.
  2. You can use a function to form the tuple (to_user, from_user):

    SELECT DISTINCT IF(from_user < to_user, from_user, to_user) AS first,
                    IF(from_user < to_user, to_user, from_user) AS second
    FROM   messages
    

(not tested, of course).

quietmint
  • 13,885
  • 6
  • 48
  • 73
Alex Monthy
  • 1,827
  • 1
  • 14
  • 24
  • Technically [you _can_ `SELECT` nonaggregated columns with `GROUP BY` in MySQL](http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html), [but you shouldn't](http://stackoverflow.com/questions/3168042/can-i-use-non-aggregate-columns-with-group-by) because its undefined which value will be chosen for the nonaggregated columns. – quietmint Sep 15 '12 at 17:53
  • That's the point. In practice, MySQL returns the first value from the grouped column, and does not give any hint that this might be semantically wrong. – Alex Monthy Sep 15 '12 at 19:22
1

This is a little tricky but it works for me.

SELECT n.f, n.t
FROM
  (SELECT DISTINCT m.from_user AS f, m.to_user AS t
   FROM messages AS m
   WHERE (m.from_user = 1
          OR m.to_user =1)) AS n,
  (SELECT DISTINCT m.from_user AS f, m.to_user AS t
   FROM messages AS m
   WHERE (m.from_user = 1
          OR m.to_user =1)) AS m
WHERE n.f = m.t
  AND m.f = n.t
  AND n.f < m.f;
quietmint
  • 13,885
  • 6
  • 48
  • 73
aaroncio
  • 11
  • 1
-1

you can append

HAVING to_user < from_user 
ertrzyiks
  • 386
  • 1
  • 6
  • In a message system you cannot guarantee that for every tupal (from,to) a tupel (to,from) exists. Therefore your solution might leave some rows out. – Alex Monthy Sep 14 '12 at 21:14
  • from pair {n,m} {m,n} you will keep only sorted one; if you can send message to yourself it should be <= operator – ertrzyiks Sep 14 '12 at 21:14
  • @ertrzyiks: this is incorrect method. what happens in your query if, let say, table has record like to_user = 3, from_user = 1 (3 < 1) == it will fail... – Glavić Sep 14 '12 at 21:49
  • that is what happened down the rows :( – Ando Sep 14 '12 at 22:07
  • im sorry, you are right; Alex Monthy's solution seems handle such situations – ertrzyiks Sep 15 '12 at 15:18