Как выбрать только тех пользователей с кем есть сообщения?

Question

[nonvon]

Ещё раз прошу помощи! Внедряю систему сообщений на сайт и всё бы хорошо - но почему-то разработчик решил показывать в списке диалогов сразу всех пользователей - даже тех которым ни мы не писали ни они нам не писали. Очень хотелось бы это исправить (там issue создавалось давно - но автор пропал( ).

таблица сообщений - интересует собственно только два поля - от кого и кому

`messages` (
  `id` int(11) NOT NULL,
  `from_id` int(11) DEFAULT NULL,
  `whom_id` int(11) NOT NULL,
  `message` varchar(750) NOT NULL,
  `status` int(11) DEFAULT '0',
  `is_delete_from` int(11) DEFAULT '0',
  `is_delete_whom` int(11) DEFAULT '0',
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL
)

а теперь сам запрос - довольно большой

SELECT
   `usr`.`id`,
   `usr`.`username` AS `username`,
   `msg`.`cnt` AS `cnt_mess`,
   `msg_`.`cnt_` AS `cnt_mess_all` 
FROM
   `user` `usr` 
   LEFT JOIN
      (
         SELECT
            `from_id`,
            count(id) AS `cnt` 
         FROM
            `messages` 
         WHERE
            (
               `status` = 1
            )
            AND 
            (
               `whom_id` = 9
            )
         GROUP BY
            `from_id`
      )
      `msg` 
      ON usr.id = msg.from_id 
   LEFT JOIN
      (
         SELECT
            `from_id`,
            `whom_id`,
            count(id) AS `cnt_` 
         FROM
            `messages` 
         WHERE
            (
               `from_id` = 9
            )
            OR 
            (
               `whom_id` = 9
            )
         GROUP BY
            `from_id`
      )
      `msg_` 
      ON usr.id = msg_.from_id 
      or usr.id = msg_.whom_id 
WHERE
   `usr`.`id` != 9 
ORDER BY
   `msg`.`cnt` DESC,
   `usr`.`username` DESC

первый join берёт все непрочитанные сообщения `usr`.`id` = 9 и работает верно, а вот второй это уже я пробовал

и результат такой не понятно как получилось 3 в поле cnt_mess_all ведь согласно всех строк из таблицы

там должно быть 1

в идеале в cnt_mess_all должно быть общее кол-во строк переписки между пользователями

тоесть
user9 - user3 = 2
user9 - user6 = 2
user9 - user7 = 1

Заранее благодарю за помощь. Обгуглился уже честное слово ((

Comments

[ThunderCat]

не понятно как получилось 3

Чего не понятно то,
выбираем все ячейки
где фром_ид = 9 ...
считаем всего
по этой группе
там их три.

Answer 1

[Aricus]

GROUP BY `from_id`

Вы берёте по двум колонкам, а группируете только по одной из них. Отсюда и получается такой результат.
GROUP BY `from_id`, `whom_id`
Не уверен, но, вроде, должно помочь.

Comments

[nonvon]

к сожалению нет. тут вообще не верный запрос - второй join не работает

SELECT  DISTINCT 
        CASE WHEN from_id = 9 
            THEN whom_id 
            ELSE from_id 
        END userID
FROM    messages
WHERE  9 IN (from_id, whom_id)

вот так вроде правильно - но осталось теперь скрестить с самим запросом

[Aricus]

В принципе, тоже понятно, почему join не работает.

ON usr.id = msg_.from_id
or usr.id = msg_.whom_id

Один из них - 9, а другой, например - 7. И не понятно, с каким из них скрещивать. Возможно, так:

ON (usr.id = msg_.from_id AND msg_.from_id != 9)
      OR (usr.id = msg_.whom_id AND msg_.whom_id != 9)

Запрос получается слишком перегруженный. Возможно, быстрее (да и проще) будет выполнить два отдельных запроса, с from_id = 9 и с whom_id = 9, и получить нужные данные уже в скрипте.

[nonvon]

согласен. не хотелось какакодить