How to group dialogs on two fields in MySQL using GroupBY?

There is a table of dialogs with dialogs in goods of the user table field id, tovar_id, sender_id, recipient_id, message how to display a list of conversations that they are not repeated? Each dialogue contains item ID, the ID of the one who wrote and ID whom he wrote. Accordingly, if there is a correspondence between the user id=1 and id=2, then the table will be the following lines:
- id=1, tovar_id=1, sender_id=1 or recipient_id=2, message='Hello'
- id=2, tovar_id=1, sender_id=2, recipient_id=1, message='hello'
If group by tovar_id, sender_id, recipient_id displays two lines, although this one conversation. By tovar_id=1It may be correspondence with other users
- id=3, tovar_id=1, sender_id=101, recipient_id=1, message='new message'
therefore not suitable just group by tovar_id
June 3rd 19 at 19:31
2 answers
June 3rd 19 at 19:33
Solution
Try this query with a calculated pseudoparalysis dialog_hold.
The point is that this parameter is the same for the correspondence of the user 1 and user 2 in both directions.
You should get the number 1000002 when sender_id=1 or recipient_id=2 and sender_id=2, recipient_id=1.
In this parameter you can sort the conversation so that the conversation of the users was in order.
The value 1000000 should be obviously large than the number of users in your system in any historical period. However, max(user_id) * 1000000 + max(user_id) should never exceed the maximum integer value supported by the DBMS (the upper limit of user id, when this feint ears are able to work).
select a.*
from (select d.id, d.tovar_id, d.sender_id, d.recipient_id, d.message
case
 when d.sender_id < d.recipient_id
 then d.sender_id * 1000000 + d.recipient_id
 else d.recipient_id * 1000000 + d.sender_id
 end dialog_hold
 from dialogs d
 where d.tovar_id = :tovar_id
 and (d.sender_id = :user_id or d.recipient_id = :user_id)
 ) a
order by a.tovar_id, a.dialog_hold, a.id
changed the description - audreanne commented on June 3rd 19 at 19:36
Sketched the approximate solution, try. - jerrold_Hickle commented on June 3rd 19 at 19:39
applied inquiry - displays all dialogs and 1-2 and 2-1 as a separate - audreanne commented on June 3rd 19 at 19:42
the last line:
group by a.tovar_id, a.dialog_hold

works - audreanne commented on June 3rd 19 at 19:45
Query:
INSERT INTO `dialogs`(`tovar_id`, `sender_id`, `recipient_id`, `message`)
VALUES (1, 1, 2, 'Hello, Alice, I'm Bob. As iron?'),
(1, 2, 1, 'hi, Bob, I am Alice. Iron is good!'),
(1, 1, 3, 'hi, Eva, this is Bob. Normal vacuum?'),
(1, 2, 1, 'Bob, I'm Alice. How to turn off the iron?'),
(1, 3, 1, 'Bob, I'm Eva. The vacuum cleaner is not working.')

distribute test sample:
5b51c9b7159fe521503107.png
As you can see, there are pads in the sequence of messages of users 1, 2, and 1, 3.
Next, took the written request, have removed the selection parameters message on a particular product, and those which involve a specific user:
select a.*
from (select d.id, d.tovar_id, d.sender_id, d.recipient_id, d.message
case
 when d.sender_id < d.recipient_id
 then d.sender_id * 1000000 + d.recipient_id
 else d.recipient_id * 1000000 + d.sender_id
 end dialog_hold
 from dialogs d
 /*where d.tovar_id = :tovar_id -- on a particular product
 and (d.sender_id = :user_id or d.recipient_id = :user_id) -- and where the user is user_id
*/
 ) a
order by a.tovar_id, a.dialog_hold, a.id

Got a selection:
5b51ca4f68ffd913370076.png
As you can see, it turned out to group messages by arbors dialog_hold.
If, for example, in PHP to collect two-dimensional array at assots. keys tovar_id and dialog_hold, every element of out (a separate product) to get an array of arbors, each arbor - chain of messages on this product between two users.
$out = [];
foreach($rows as $row) //$rows is a sample of the query result
{
 $out[$row['tovar_id']][$row['dialog_hold']][] = $row; 
}
- jerrold_Hickle commented on June 3rd 19 at 19:48
June 3rd 19 at 19:35
You can try:

select dd.tovar_id, dd.sender_id
 from dialogs dd
 where not exists(select 1
 from dialogs ds
 where ds.tovar_id = dd.tovar_id
 and ds.recipient_id = dd.dd.sender_id
 and ds.id < dd.id)
 group by dd.tovar_id, dd.sender_id


Here are all the dialogue for all items.
If you want to filter by user or product, you can do it by setting the where section

Find more questions by tags MySQL