LEFT JOIN и WHERE для связующей таблицы?

Question

[Давид]

Есть запрос:

SELECT 
 t.*,
 COUNT(o.id) as count_o
FROM tasks as t
LEFT JOIN offers as o ON o.task_id=t.id
WHERE (t.city_id=1 AND t.status=1)
AND (o.user_id <> 2 AND o.status NOT IN(1,2))
GROUP BY t.id

Все очень просто две таблицы (задачи tasks и предложения на исполнение offers) и вроде как не "маленький", но не могу разобраться. Когда у меня ничего не находит в таблице offers, тогда count_o должно быть равно 0, но БД возвращает пустой результат. Когда убираю AND (o.user_id <> 2 AND o.status NOT IN(1,2,4)) - есть нули

Answer 1

[Котик Антон]

Чтобы понять, почему так происходит, сравните результаты вот этих запросов:

SELECT t.*, o.id
FROM tasks AS t
LEFT JOIN offers AS o ON o.task_id = t.id
WHERE (t.city_id = 1 AND t.status = 1) 
AND (o.user_id <> 2 AND o.status NOT IN(1,2));

SELECT t.*, o.id
FROM tasks AS t
LEFT JOIN offers AS o ON o.task_id = t.id
WHERE (t.city_id = 1 AND t.status = 1);

Чтобы это исправить, перенесите все условия для offers из WHERE в LEFT JOIN (пример на SqlFiddle):

SELECT t.*, COUNT(o.id) AS count_o
FROM tasks AS t
LEFT JOIN offers o ON o.task_id=t.id AND o.user_id <> 2 AND o.status NOT IN(1,2)
WHERE (t.city_id = 1 AND t.status = 1)
GROUP BY t.id;

Подробнее: dev.mysql.com/doc/refman/5.7/en/left-join-optimiza...

Comments

[Давид]

Немного позже тоже об этом подумал. Спасибо!

Answer 2

[Rsa97]

Возможно эффективнее будет JOIN'ить с уже готовой таблицей

SELECT `t`.*, IFNULL(`o`.`count`, 0) AS count_o
  FROM `tasks` AS `t`
  LEFT JOIN (
    SELECT `task_id`, COUNT(*) AS `count`
      FROM `offers`
      WHERE `user_id` <> 2 AND `status` NOT IN (1,2)
      GROUP BY `task_id`
  ) AS `o` ON `o`.`task_id` = `t`.`id`
  WHERE `t`.`city_id` = 1 AND `t`.`status` = 1

Comments

[Давид]

Спасибо! Отличное решение. Правда я использую ORM поэтому не знаю как там интерпретировать такой запрос