How to count the total number of records by "pairs"?

Question

PHP

Hello! I have a question. There is a table, and it has fields to_user, from_user.
For example, there are entries, the first one is to_user = 1, from_user=5, and the second entry, where to_user=5 and from_user=1. That is, two people exchanged messages. This is how my users' correspondence is organized. So you need to display the total number of dialogs with users. In the case above, we display the number 1, because we have correspondence with only one person, and there will be many of them .. I can’t make a request .. I do this:

SELECT `m`.* FROM `messages` as m JOIN `users` as u ON (m.`to_user` = '{$user['id']}' AND m.`from_user` = u.`id`) OR (m.`from_user` ='{$user['id']}' AND m.`to_user` = u.`id`) GROUP BY m.id

But that's not it.. it gives 2 records and I don't know how to group..

Answer 1

it seems to me that first you need to do
SELECT minimum(to_user,from_user) a , maximum(to_user,from_user) b FROM messages
and then from this table make a selection of differing rows like this:
SELECT DISTINCT a, b .....

Answer 2

Based on the wording of the question, somehow it turns out very simply. Quoting you:
You want to select messages sent by the user or sent by the user
Then you say that you really want to choose something else.
I understand that you want to get the total number of dialogs, not entries (in the message table)?!
And then I look at your request and see that you are looking for (trying to look for) the number of conversations in which this user participates.

SELECT * FROM `messages` AS z1
WHERE z1.`from`={$user['id']} OR z1.`to`={$user['id']}
AND NOT EXISTS(
  SELECT id FROM `messages` as z2 WHERE z2.`to` = z1.`from` AND z2.`from` = z1.`to`
)
GROUP BY `from`,`to`