1

I'm building a chat app in which two people will comunicate on enquiry, I want to fetch conversation between two users on enquiry, table structure is like below:

Table: enquiries

id, name, product, msg

Table: replies

id, enquiry_id(fk), from_user(fk), to_user(fk), msg

    +----+--------+-----------+---------+------+
    | id | enq_id | from_user | to_user | msg  |
    +----+--------+-----------+---------+------+
    | 1  | 1      | 1         | 2       | msg1 |
    +----+--------+-----------+---------+------+
    | 2  | 1      | 2         | 1       | msg2 |
    +----+--------+-----------+---------+------+
    | 3  | 1      | 1         | 2       | msg3 |
    +----+--------+-----------+---------+------+
    | 4  | 1      | 2         | 1       | msg4 |
    +----+--------+-----------+---------+------+
    | 5  | 1      | 1         | 3       | msg1 |
    +----+--------+-----------+---------+------+
    | 6  | 1      | 3         | 1       | msg2 |
    +----+--------+-----------+---------+------+  

What I'm doing

I'm using using below query to fetch communication between two users on the basis of enquiries but it is giving two seperate records for same users & enquiry.

SELECT 
   r.enquiry_id, 
   r.from_user, 
   r.to_user, 
   count(1) replies_count 
FROM 
   `replies` r 
GROUP BY 
   r.enquiry_id, r.from_user, r.to_user
ORDER BY 
   r.id DESC

This is the result of the above query

    +--------+-----------+---------+-----+
    | enq_id | from_user | to_user | msg |
    +--------+-----------+---------+-----+
    | 1      | 1         | 2       | 2   |
    +--------+-----------+---------+-----+
    | 1      | 2         | 1       | 2   |
    +--------+-----------+---------+-----+
    | 1      | 1         | 3       | 1   |
    +--------+-----------+---------+-----+
    | 1      | 3         | 1       | 1   |
    +--------+-----------+---------+-----+

And my expected result is like

+--------+--------------+--------------+-----+
| enq_id | participant1 | participant2 | msg |
+--------+--------------+--------------+-----+
| 1      | 1            | 2            | 4   |
+--------+--------------+--------------+-----+
| 1      | 1            | 3            | 2   |
+--------+--------------+--------------+-----+
  • 1
    can be more than two distinct users in the conversation? – McNets Nov 29 '16 at 13:50
  • There will be single row for two users communicating on same enquiry, I've modified my question please check – Ramesh Mhetre Nov 30 '16 at 5:01
1

check it here if it is what you're asking for: http://rextester.com/TVOKS20933

select enquiry_id, from_user, to_user, count(1) msg 
from replies
group by enquiry_id, from_user, to_user
order by id;

+----+--------+-----------+---------+------+
| id | enq_id | from_user | to_user | msg  |
+----+--------+-----------+---------+------+
| 1  | 1      | 1         | 2       | msg1 |
+----+--------+-----------+---------+------+
| 2  | 1      | 2         | 1       | msg2 |
+----+--------+-----------+---------+------+
| 3  | 1      | 1         | 2       | msg3 |
+----+--------+-----------+---------+------+
| 4  | 1      | 2         | 1       | msg4 |
+----+--------+-----------+---------+------+
| 5  | 1      | 1         | 3       | msg1 |
+----+--------+-----------+---------+------+
| 6  | 1      | 3         | 1       | msg2 |
+----+--------+-----------+---------+------+    

+--------+-----------+---------+-----+
| enq_id | from_user | to_user | msg |
+--------+-----------+---------+-----+
| 1      | 1         | 2       | 2   |
+--------+-----------+---------+-----+
| 1      | 2         | 1       | 2   |
+--------+-----------+---------+-----+
| 1      | 1         | 3       | 1   |
+--------+-----------+---------+-----+
| 1      | 3         | 1       | 1   |
+--------+-----------+---------+-----+
| improve this answer | |
  • This what is I'm doing right now, I want only one row for communication between two users. I have modified my question, please check – Ramesh Mhetre Nov 30 '16 at 5:00
1

A dialogue between two users can be grouped together as a single row if you ensure that the IDs always go in a specific order, for example, lower ID followed by higher ID. For just two elements, you can easily implement such an order using built-in functions LEAST and GREATEST. For instance, if you have rows:

from_user  to_user
---------  -------
1          2
2          1
1          2

and perform a query like this:

SELECT
  LEAST(from_user, to_user) AS participant1,
  GREATEST(from_user, to_user) AS participant2
FROM
  ...

the result will be:

participant1  participant2
------------  ------------
1             2
1             2
1             2

If you then use the expressions LEAST(from_user, to_user) and GREATEST(from_user, to_user) as grouping terms instead of simply from_user, to_user, your output will return a single row per each pair of users regardless of which one was from_user and which to_user.

Therefore, you can modify your query like this to achieve the desired output:

SELECT 
   r.enquiry_id, 
   LEAST(r.from_user, r.to_user) AS participant1, 
   GREATEST(r.from_user, r.to_user) AS participant2, 
   count(1) replies_count 
FROM 
   `replies` r 
GROUP BY 
   r.enquiry_id,
   LEAST(r.from_user, r.to_user),
   GREATEST(r.from_user, r.to_user)
ORDER BY 
   MAX(r.id) DESC
;

You can see that I also changed the ORDER BY from r.id to MAX(r.id). While it is true that MySQL's non-strict mode permits you to select non-GROUP BY columns without aggregation, in practice this can be very misleading and cause unpredictable/inconsistent results where the selected column can have multiple values per group. I suggest you always wrap a non-GROUP BY column in an aggregate function when referencing it in SELECT, HAVING or ORDER BY.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.