Много JOIN и пресловутое GROUP BY перед ORDER BY

[Jake Badland]

Доброго времени суток)
Задача в том, чтобы для одного оператора по привязанным профилям вытащить последние сообщения и к ним притянуть информацию о пользователе с которыми общался профиль..
Проблема в том, что пользователь может общаться одновременно с несколькими профилями. Вот по сути мне не удаётся сгруппировать таким образом, чтобы получить не только каждое сообщение от юзера, но и к каждому профилю. Интересуют только исходящие сообщения (messages.direction = 'out')
условно operator_id = 1
Есть таблица по привязке операторов к профилям:
operators_to_profiles
id, operator_id, profile_id
Таблица с сообщениями:
id, operator_id, profile_id, user_id, text, direction, created_at

Запрос на данный момент:

SELECT *
FROM operators_to_profiles AS otp
INNER JOIN messages ON messages.profile_id = otp.profile_id
INNER JOIN users ON messages.user_id = users.id
INNER JOIN (
    SELECT MAX(created_at) AS MaxMessDate, messages.user_id
    FROM operators_to_profiles
    INNER JOIN messages ON messages.profile_id = operators_to_profiles.profile_id
    WHERE
    messages.direction = 'out'
    AND operators_to_profiles.operator_id = 1
    GROUP BY messages.user_id #вот как тут сгруппировать, чтобы получить не только уникальных пользователей? т.е. уникальные urer_id <->profile_id
) AS temp
ON (temp.user_id = messages.user_id
AND messages.created_at = temp.MaxMessDate)
WHERE otp.operator_id = 1
AND messages.direction = 'out'

Возможно есть другой вариант решения.
Буду весьма благодарен за помощь)


ЗЫ. тут есть какой - то мануал по подсветке синтаксиса?) И хоть немного форматирования?)

 

[AnrDaemon]

Первая таблица в запросе неверная. А форматирование можно найти, если глаза разуть.

 

[Jake Badland]

Первая таблица в запросе неверная. А форматирование можно найти, если глаза разуть.

Насчёт форматирования php кода

echo "Я таки нашел)";

а для скуля не.

И можно немного пояснить, что значит "неверная"? Имя не так указал, сама идея использовать в таком виде, религия не та?

 

[Jake Badland]

Первая таблица в запросе неверная. А форматирование можно найти, если глаза разуть.

Посмотрел вообще как ты людям отвечаешь, всегда конкретно даешь ответ) Который заставляет задуматься)
Блин, ну я и так думал и так.. ну не могу я допереть в чем прикол, что я получаю не то, что хочу.

 

[ksnk]

Разрабатывай запрос последовательно.
Например, все данные, по идее, есть в таблице messages, то есть чтото вроде

select * from messages m
inner join
(
  select max(created_at) as date,profile_id
  from messages
  where user_id=1 and direction='out'
  group by profile_id
) x on m.created_at=x.date and m.profile_id=x.profile_id
  and m.user_id=1 and m.direction='out'

уже может нам дать почти все, что нам нужно. При условии, конечно, что один и тот же пользователь не может писать пачку сообщений в одну и ту же секунду. К ней нужно прикрутить join с расшифровкой пользователя-получателя сообщения.
Если нужно вывести весь комплект профилей, основной таблицей придется делать выборку из operators_to_profiles.

Для того, чтобы на вопрос с простыней sql все таки получить более менее содержательный ответ, можно пользоваться sqlfiddle. В нем есть удобная фенечка - TextDDL, которая позволяет быстро импортирорвать срез данных из базы. Просто копировать кусочки таблиц в Excel, там редактировать и оттуда в форму создания таблицы.

Ну и форматирование sql на форуме таки есть. Причем примерно там же, где и форматирование php

 

[Jake Badland]

Разрабатывай запрос последовательно.
Например, все данные, по идее, есть в таблице messages, то есть чтото вроде

select * from messages m
inner join
(
  select max(created_at) as date,profile_id
  from messages
  where user_id=1 and direction='out'
  group by profile_id
) x on m.created_at=x.date and m.profile_id=x.profile_id
  and m.user_id=1 and m.direction='out'

уже может нам дать почти все, что нам нужно. При условии, конечно, что один и тот же пользователь не может писать пачку сообщений в одну и ту же секунду. К ней нужно прикрутить join с расшифровкой пользователя-получателя сообщения.
Если нужно вывести весь комплект профилей, основной таблицей придется делать выборку из operators_to_profiles.

Для того, чтобы на вопрос с простыней sql все таки получить более менее содержательный ответ, можно пользоваться sqlfiddle. В нем есть удобная фенечка - TextDDL, которая позволяет быстро импортирорвать срез данных из базы. Просто копировать кусочки таблиц в Excel, там редактировать и оттуда в форму создания таблицы.

Ну и форматирование sql на форуме таки есть. Причем примерно там же, где и форматирование php

and m.user_id=1

Тут нужна привязка.
У оператора много профилей. Профили оператора можно однозначно вытащить через operators_to_profiles.
У профилей есть многие ко многим через мессаги к юзерам.
Вот тут и проблема.
А к результату уже инфу по юзерам подвязать.
По факту мне надо получить список сообщений которые были отправлены от профилей привязаных к конкретному оператору всем доступным юзерам. И только 1 последнее сообщение. В смысле, чтобы оператор видел список контактов с кем, через какой профиль, дату и текст.
Я же в начале писал, что именно в во вложенности и/или джойнах путаюсь или даже не знаю уже в чём.

Второй день уже глаз дергается...

И спасибо за подсказку по форматированию)

 

[Jake Badland]

Уже мной была поднята схожая тема почти 3 года назад

Выборка последих сообщений

да можно и без юнионов нахачить :), типа select max(case read when 0 then SOME_DATE_FAR_IN_THE_FUTURE else date end) правда чтобы это эффективно работало, придется сделать persistent virtual column такой и индекс туда (мы ж про mysql тут?)

phpclub.ru

но тут именно из - за таких джойнов сингулярная запутанность)

 

[Jake Badland]

SELECT *
FROM operators_to_profiles AS otp
INNER JOIN messages ON messages.profile_id = otp.profile_id
INNER JOIN users ON messages.user_id = users.id
INNER JOIN (
    SELECT MAX(created_at) AS MaxMessDate, messages.user_id
    FROM operators_to_profiles
    INNER JOIN messages ON messages.profile_id = operators_to_profiles.profile_id
    WHERE
    messages.direction = 'out'
    AND operators_to_profiles.operator_id = 1
    GROUP BY messages.user_id, messages.profile_id
) AS temp
ON (temp.user_id = messages.user_id
AND messages.created_at = temp.MaxMessDate)
WHERE otp.operator_id = 1
AND messages.direction = 'out'

Что - то я затупил))) Всем спасибо))