On an example: There is a table "Films" and "Genres", connected by many-to-many. It is necessary to deduce all the Genres of the Film if at least one Genre coincides with the condition. That is, films are selected by Genre, but for each film it is necessary to show not only the Genre that coincided with the condition, but everything related to it.

All this in Doctrine2 Query Builder.

Upd. Request at the moment in the form of DQL:

SELECT Movies, Movies, MovieGenres FROM Entities\Movies Movies LEFT JOIN Movies.MovieGenres MovieGenres WITH MovieGenres.id IN (:genres) WHERE Movies.id IN (SELECT MovieGenres2.id FROM Entities\MovieGenres MoviesGenres2) GROUP BY Movies, MovieGenres

In this form, the film with the genres "Western", "Drama" when searching for the word "Western" only the genre "Western", and "Drama" shows if it is also looking for it.

  • There is a table "Films" and "Genres", connected by many-to-many. Structures in the DDL - in the question text. - Akina

1 answer 1

Something like

 SELECT f.name, GROUP_CONCAT(g.name) FROM films f, genres g, films_genres fg WHERE f.id = fg.f_id AND g.id = fg.g_id AND f.id IN (SELECT DISTINCT fg1.f_id FROM films_genres fg1, genres g1 WHERE g1.id = fg1.g_id AND g1.name IN (@genres_list)) sq GROUP BY f.name
  • one
    Here, too, filtering by genres is obtained, but I need to bring out all the genres of the film, if one of them is on the genres_list list ... - Anastasia Sitnina
  • Have you tried? Or just say so? - Akina
  • I tried to translate into Doctrine, but apparently crooked. Now I drove through MySql PHPMyAdmin returns correctly. Tell me, please, how to translate this into Doctrine - it turns out that you need to request the films_genres table, but it is generated automatically and is not Entity - Anastasia Sitnina
  • Or it is impossible through QueryBuilder, it is necessary to insert through DQL? - Anastasia Sitnina pm