Как указать условие в Join для group_concat в SQL запросе?

Question

У меня есть таблицы:

create table movies (
  id int unsigned auto_increment,
  name varchar(255),
  primary key(id)
);

insert into movies values (1, 'name1'), (2, 'name2'), (3, 'name3'), (4, 'name4'), (5, 'name5');

create table movies_genres (
    genre_id int,
    movie_id int
);

insert into movies_genres values (7,1), (1,1), (10,1), (6,2), (1,2), (3,2), (5,3), (5,4), (2,5), (1,5);

Я хочу получить список ID с условием по жанру.

Например, список ID фильмов с жанром 1

id, genres
1   7,1,10
2   6,1,3
5   1

Обратите внимание, в поле genres я хочу получить и id жанра, который ищу и другие жанры, которые относятся к фильму.

Что я делаю

select m.id, group_concat(mg.genre_id) as genres from movies m 
left join movies_genres mg on mg.movie_id = m.id 
and mg.genre_id = 1
group by m.id 
order by m.id desc 
limit 15

Что получаю

id  genres
5   1
4   (null)
3   (null)
2   1
1   1

Вот здесь можно посмотреть: http://sqlfiddle.com/#!9/15aa04/4

Подскажите, что я делаю не так? Мне видится, что должен быть стандартный запрос для моей задачи? Хотелось бы не только решение, но и комментарий? Есть ли легкое решение или здесь видится архитектурная проблема?

Answer 1

Запрос может быть следующим:

select 
    m.id, group_concat(mg.genre_id) as genres 
from movies m 
left join movies_genres mg on mg.movie_id = m.id
where m.id in (
    select movie_id from movies_genres mg where mg.genre_id = 1
)
group by m.id 
order by m.id desc 
limit 15;

DB Fiddle here

Answer 2

select m.id, group_concat(mg.genre_id ORDER BY mg.genre_id) as genres 
from movies m 
join movies_genres mg on mg.movie_id = m.id 
group by m.id 
HAVING SUM(mg.genre_id = 1)
order by m.id desc 
limit 15

Что изменено в "авторском" запросе:

Заменён LEFT JOIN на INNER JOIN.

Тут даже сказать нечего. Кроме настоятельной рекомендации внимательно изучить типы связывания - использование LEFT в запросе свидетельствует об отсутствии понимания и написании именно так "по привычке".

Добавлена сортировка в GROUP_CONCAT.

В отсутствии сортировки результат запроса становится недетерминированным (т.е. на одних и тех же данных два выполнения могут вернуть разные данные, потому что сервер без указания порядка имеет право выполнять конкатенацию в произвольном порядке, что в общем нехорошо).

Убрано условие and mg.genre_id = 1 из выражения соединения таблиц.

Нам в итоге требуется получить все жанры, а не только тот, по которому выполняется отбор.

Добавлено условие пост-отбора HAVING SUM(mg.genre_id = 1).

Данное условие проверяет все жанры фильма на равенство заданному значению 1. Для тех фильмов, у которых в списке этот жанр есть, мы получаем сумму 1 (или больше, если таблица связывания допускает дубликаты - а в показанной версии она допускает), а для тех, у кого этого жанра нет, соответственно получаем сумму 0. В итоге в выходной набор попадут только те записи, у которых в списке заданный жанр присутствует.

БЛОГ НА HUSL

Как указать условие в Join для group_concat в SQL запросе?

Ответы (2 шт):