Как выполнить LEFT JOIN с условием?

Question

[Алексей Коновалов]

Всем привет. У меня есть две таблицы:
messages - хранит сообщения.
messages_read - хранит данные о том прочитано ли сообщение пользователем.
Когда я получаю список сообщений из БД и хочу определить было ли прочитано сообщение текущим пользователем, мне приходится выполнять LEFT JOIN и запрос получается таким.

SELECT m.`id`, m.`message`, mr.`readed` 
FROM `messages` m
LEFT JOIN `messages_read` mr ON mr.`message_id` = m.`id` AND mr.`user_id` = 111
WHERE m.`chat_id` = 12

Обратите пожалуйста внимание на AND mr.`user_id` = 111. Мне это условие просто необходимо, потому что нужно знать прочитал ли именно этот пользователь это сообщение.
Но в документации MySql сказано, что нужно избегать условий в конструкции JOIN.

Подскажите, как лучше поступить в этой ситуации?

Comments

[Stalker_RED]

в документации MySql сказано, что нужно избегать условий в конструкции JOIN.

Можете показать где?

[Алексей Коновалов]

Stalker_RED, www.mysql.ru/docs/man/JOIN.html

Никогда не следует указывать в части ON какие бы то ни было условия, накладывающие ограничения на строки в наборе результатов. Если необходимо указать, какие строки должны присутствовать в результате, следует сделать это в выражении WHERE.

[ThunderCat]

Алексей Коновалов,

в документации MySql сказано, что нужно избегать условий в конструкции JOIN.

Никогда не следует указывать в части ON какие бы то ни было условия,

Во первых там написано не в "джоин" а в "он", и верно написано, по тому что

SELECT m.`id`, m.`message` 
FROM `messages` m
LEFT JOIN `messages_read` mr 
ON mr.`message_id` = m.`id`
WHERE m.`chat_id` = 12
AND mr.`user_id` = 111

[McMike]

ThunderCat, здесь наверное ничего криминального не вылезет, но вообще результат разный может быть, в зависимости от сложности запроса.

[Алексей Коновалов]

ThunderCat, Так же как и в ответе внизу: "Ваше условие эквивалентно условию INNER JOIN mr.`user_id` = 111
А значит если записи о прочтении не существует, то и само сообщение мы не получим."

Во первых там написано не в "джоин" а в "он",

Ну и ON это все то, что идет после ON, включая ON .... AND ....

[Алексей Коновалов]

У меня и вовсе был вариант получать данные отдельным запросом и совмещать уже на стороне php с сообщениями, которые получили ранее

[ThunderCat]

Алексей Коновалов,

Ну и ON это все то, что идет после ON, включая ON .... AND ....

Угу, и order by, limit - все это on... Нет, он это он, вэа это отдельный оператор.

А значит если записи о прочтении не существует, то и само сообщение мы не получим.

Ну а зачем тогда вообще его джойнить если в возвращаемые поля у вас все равно ничего из этой таблицы не попадает?

[Алексей Коновалов]

ThunderCat,

Ну а зачем тогда вообще его джойнить если в возвращаемые поля у вас все равно ничего из этой таблицы не попадает?

Ну так мне нужно показать сообщения. А если нет данных прочитано оно или нет, то это же не значит что и само сообщение не нужно показывать

[ThunderCat]

Алексей Коновалов,

Ну так мне нужно показать сообщения. А если нет данных прочитано оно или нет, то это же не значит что и само сообщение не нужно показывать

Надеюсь вы понимаете что в таком случае это условие там вообще ниочем? То есть что с ним, что без него - результат будет один и тот же.

[Алексей Коновалов]

ThunderCat, если его не добавить, то туда будет вынесены данные о любом пользователе, который не прочитал это сообщение

[ThunderCat]

Алексей Коновалов, либо я туплю, либо вы... вы выбираете чат по ид. Все сообщения этого чата полюбому будут для этого пользователя, не важно прочел он его или нет, так? Если бы нужно было определить прочел ли пользователь сообщение, был бы смысл делать выборку именно как у вас, но указав в полях возврата mr.`user_id`, который был бы либо айди, либо нулл. Если же у вас МНОГО людей в чате - тогда правильнее будет сделать так:

SELECT m.`id`, m.`message`, mr.`message_id` status
FROM `messages` m
LEFT JOIN (
      select `message_id` 
      from `messages_read`
      where `user_id` = 111
) mr 
ON mr.`message_id` = m.`id` 
WHERE m.`chat_id` = 12

[Алексей Коновалов]

ThunderCat, Вот, теперь вы точно правильно меня поняли.
Но не будет в таком случае правильней сделать с подзапросом без LEFT JOIN?
т.е. вот так:

ELECT m.`id`, m.`message`, (
      select mr.`status` 
      from `messages_read` mr
      where mr.`user_id` = 111 AND mr.`message_id` = m.`id`
) readed
FROM `messages` m
WHERE m.`chat_id` = 12

[ThunderCat]

Алексей Коновалов, не уверен что это не алиас к джоину, проще глянуть эксплэйн запросов и посмотреть как интерпретирует его внутренний построитель.

[Алексей Коновалов]

ThunderCat, т.е. само по себе решиение и с вложенным запросом в JOIN и с обычным вложенным запросом в SELECT имеют право существовать? Мне нужно только определенить какое более быстрое?

[ThunderCat]

Алексей Коновалов, да, это вполне нормальное решение, причем гораздо более верное чем собачить сравнение в ON (емнип в стрикт моде оно вообще пораждает варнинг или еррор). Хотя возможно в итоге выливается в тот же субзапрос в новых версиях мускуля.

[ThunderCat]

Алексей Коновалов,

Но не будет в таком случае правильней сделать с подзапросом без LEFT JOIN?

Навскидку по логике - для каждой строки в таком случае будет выполняться запрос, в случае же джоин - будет одна выборка по произведению таблиц или 2 запроса с сопоставлением результатов(что очевидно в разы быстрее). ИРЛ скорее всего оптимизатор просто заменит первый вариант вторым.

[Алексей Коновалов]

ThunderCat, Спасибо. Я воспользуюсь вашим вариантом, напишите его в ответах, я помечу его как решение

Answer 1

[ThunderCat]

Если бы нужно было определить прочел ли пользователь сообщение, был бы смысл делать выборку именно как у вас, но указав в полях возврата mr.`user_id`, который был бы либо айди, либо нулл. Если же у вас МНОГО людей в чате - тогда правильнее будет сделать так:

SELECT m.`id`, m.`message`, mr.`message_id` status
FROM `messages` m
LEFT JOIN (
      select `message_id` 
      from `messages_read`
      where `user_id` = 111
) mr 
ON mr.`message_id` = m.`id` 
WHERE m.`chat_id` = 12

Answer 2

[egor_nullptr]

Если запрос отрабатывает за приемлемое для вас время, то ничего не меняйте.

Answer 3

[Валерий]

SELECT m.`id`, m.`message`, case when mr.`message_id` is not null then 'Прочитано' else 'Не прочитано' end as iRead
FROM `messages` m
LEFT JOIN `messages_read` mr ON mr.`message_id` = m.`id`
WHERE m.`chat_id` = 12 and AND mr.`user_id` = 111

Comments

[Алексей Коновалов]

Ваше условие эквивалентно условию INNER JOIN mr.`user_id` = 111
А значит если записи о прочтении не существует, то и само сообщение мы не получим.

[kova1ev]

Алексей Коновалов, если записи о прочтении не существует, то в сджойненной таблице message_id будет null, и соответственно будет заменен на 'Не прочитано'

Просто не очень понятно, что вы хотите видеть в результирующей таблице. Запрос, написанный в этом ответе выведет таблицу всех прочитанных юзером 111 сообщений с полем "Прочитано"

[Алексей Коновалов]

kova1ev, хм... Я попробую, но мне кажется в результате будет не null, а будет отсутствовать и сообщение

Answer 4

[d-stream]

Можно условие вынести в where - современные оптимизаторы в обоих случаях построят одинаково оптимальный план