Как присоединиться к двум таблицам, используя список разделенных запятыми в поле join

У меня есть две таблицы, categories и movies.

В таблице movies у меня есть столбец categories. Этот столбец состоит из категорий, в которые входит фильм. Категории - это идентификаторы, разделенные запятой.

Вот пример:

Table categories {
  -id-       -name-
  1          Action
  2          Comedy
  4          Drama
  5          Dance
}

Table movies {
  -id-       -categories-  (and some more columns ofc)
  1          2,4
  2          1,4
  4          3,5
}

Теперь к актуальному вопросу: возможно ли выполнить запрос, который исключает столбец категорий из таблицы фильмов, и вместо этого выбирает соответствующие категории из таблицы категорий и возвращает их в массиве? Как соединение, но проблема в том, что существует несколько разделяемых запятыми разделов, возможно ли сделать какое-то регулярное выражение?

Ответ 1

Использование разделенных запятыми списков в поле базы данных является анти-шаблоном, и его следует избегать любой ценой. Потому что это PITA, чтобы извлечь эти значения, разделенные запятой, из agian в SQL.

Вместо этого вы должны добавить отдельную таблицу ссылок, чтобы представить взаимосвязь между категориями и фильмами, например:

Table categories
  id integer auto_increment primary key
  name varchar(255)

Table movies
  id integer auto_increment primary key
  name varchar(255)

Table movie_cat
  movie_id integer foreign key references movies.id
  cat_id integer foreign key references categories.id
  primary key (movie_id, cat_id)

Теперь вы можете сделать

SELECT m.name as movie_title, GROUP_CONCAT(c.name) AS categories FROM movies m
INNER JOIN movie_cat mc ON (mc.movie_id = m.id)
INNER JOIN categories c ON (c.id = mc.cat_id)
GROUP BY m.id

Вернуться к вашему вопросу
Альтернативно используя ваши данные, вы можете сделать

SELECT m.name as movie_title
  , CONCAT(c1.name, if(c2.name IS NULL,'',', '), ifnull(c2.name,'')) as categories 
FROM movies m
LEFT JOIN categories c2 ON 
 (replace(substring(substring_index(m.categories, ',', 2),
  length(substring_index(m.categories, ',', 2 - 1)) + 1), ',', '') = c2.id)
INNER JOIN categories c1 ON 
 (replace(substring(substring_index(m.categories, ',', 1), 
  length(substring_index(m.categories, ',', 1 - 1)) + 1), ',', '') = c1.id)

Обратите внимание, что последний запрос работает только в том случае, если в фильме есть 2 или меньше категорий.

Ответ 2

select
    m.id,
    group_concat(c.name)
from
    movies m
    join categories c on find_in_set(c.id, m.categories)
group by
    m.id

Результат должен быть примерно таким:

Table movies {
  -id-       -categories-
  1          Comedy,Drama
  2          Action,Drama
  4          Other,Dance
}

Ответ 3

Брэд прав; нормализация - это решение. Для решения этой проблемы существует нормализация. Это должно быть хорошо описано в вашей книге MySQL, если это стоит соли.

Если вы действительно настаиваете, вы можете подделать прямое соединение путем перекрестного сопоставления с FIND_IN_SET (который удобно ожидает строку с разделителями-запятыми).

Теперь MySQL не может вернуть "массив" — что то, что наборы результатов для — но он может дать вам имена категорий, разделенные, скажем, трубой (|):

SELECT
       `m`.`id`,
       `m`.`name`,
       GROUP_CONCAT(`c`.`name` SEPARATOR "|") AS `cats`
  FROM
       `movies`     AS `m`,
       `categories` AS `c`
 WHERE
       FIND_IN_SET(`c`.`id`, `m`.`categories`) != 0
 GROUP BY
       `m`.`id`;

Результат:

id  "name"     "cats"
---------------------------------------------------
1   "Movie 1"  "Comedy|Drama"
2   "Movie 2"  "Action|Drama"
4   "Movie 4"  "Dance"

Ответ 4

Это напрямую не отвечает на ваш вопрос, но то, что у вас в таблице movies, действительно плохо.

Вместо того, чтобы комбинировать categories с запятой, то, что вы должны делать, состоит в том, чтобы каждая категория была в отдельных строках, например:

Table movies {
  -id-       -categories-
  1          2
  1          4
  2          1
  2          4
  4          3
  4          5
}