Запрос MySQL из двух таблиц

Question

Есть такая схема -

Код MySQL -

  CREATE TABLE IF NOT EXISTS `film`.`genres` (
      `id_movie` INT(11) NOT NULL,
      `genre` VARCHAR(45),
  CONSTRAINT `genres_ibfk_1` UNIQUE (`id_movie`, `genre`),
        FOREIGN KEY (`id_movie`)
        REFERENCES `film`.`movie` (`id_movie`)
        ON DELETE CASCADE)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;
    
    CREATE TABLE IF NOT EXISTS `film`.`movie` (
      `id_movie` INT(11) NOT NULL AUTO_INCREMENT,
      `name_link` VARCHAR(100) NOT NULL,
      `name` VARCHAR(80) NULL DEFAULT NULL,
      `img` VARCHAR(25) NULL DEFAULT NULL,
      `year` YEAR NULL DEFAULT NULL,
      `time` VARCHAR(15) NULL DEFAULT NULL,
      `description` TEXT NULL DEFAULT NULL,
      `movie_link` VARCHAR(155) NOT NULL,
      PRIMARY KEY (`id_movie`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;

У каждого фильма может быть несколько жанров. Каждый новый жанр создается с ссылкой на айди фильма.(Айди фильма, название жанра) Таблица жанров -

Таблица фильмов -

Как создать запрос с получением всех фильмов с определенным жанром ? Допустим я хочу получить все которые имеют жанр "DRAMA".

Как выглядит добавление жанров в Entity фильма -

@Enumerated(EnumType.STRING)
    @CollectionTable(name = "genres", joinColumns = @JoinColumn(name = "id_movie"))
    @Column(name = "genre")
    @ElementCollection(fetch = FetchType.EAGER)
    private Set<Genre> genres;

Пример запроса -

public List<Movie> getAllMovieWithGenre(Genre genre) {
    TypedQuery query = getEntityManager()
            .createQuery("SELECT Movie FROM Movie m where m.genres.genre = :genre", type);
    query.setParameter("genre", genre);
    return query.getResultList();
}

Answer 1

Я смог решить подобную проблему при помощи такого запроса (учитывайте, что это синтаксис PostgreSQL, но он очень похож на MySQL):

SELECT f
FROM films f
WHERE f.id IN (SELECT film_id FROM film_genres fg WHERE fg.genre=:genre)

Этот запрос должен вывести для вас список фильмов, у которых вышло совпадение по жанру.

То есть тут вся суть в ключевом слове IN. Оно как бы проверяет, чтобы id фильма был в таблице, которую мы получаем в скобках. А в скобках мы получаем таблицу, состоящую из айдишников (film_id) которые находятся в строках, в которых есть требуемый нами жанр. Учитывайте, что оператор IN работает только с таблицами, которые состоят из одной колонки.

Ещё раз, по порядку. Вот этот запрос: SELECT film_id FROM film_genres fg WHERE fg.genre=:genre возвращает нам список film_id из записей, в которых genre=:genre? выглядеть эта таблица будет примерно так:

film_id
-------
1000002
1000026
1000032
1000048
...

А вот эта часть запроса:

SELECT f
FROM films f
WHERE f.id IN (...)

Находит все фильмы, у которых id содержится в таблице, которую мы получили в скобках.

БЛОГ НА HUSL

Запрос MySQL из двух таблиц

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