Symfony 5 and Doctrine, can’t find a way to fetch results with 3 related entities

I have multiple websites running on the same Symfony 5 installation.
This websites show videos based on video categories, but they share the same videos database. For example if I have 4 videos and their categories.

  • Video 1 = "FPS", "Counter Strike"
  • Video 2 = "FPS", "Call of Duty"
  • Video 3 = "MOBA", "League of Legend"
  • Video 4 = "League of Legends",
    "Professional"

I have 2 Websites, and. I want to show a "Categories" page on this websites. Each website has one or multiple categories to filter the videos.

  • Website 1 = "FPS"
  • Website 2 = "League of Legends", "Professional"

I’m trying to get a list of categories based on the videos for each website.

So the expected result would be :

  • Website 1 Categories List = "FPS", "Counter Strike", "Call of Duty"
  • Website 2 Categories List = "Professional", "League of Legends" (but not "MOBA", because "Professional" is required too)

I have an Entity called "Video", "Website" and one last called "Category" :

Entity/Video.php

/**
 * @ORMManyToMany(targetEntity=Category::class, inversedBy="videos")
 * @ORMOrderBy({"priority" = "DESC"})
*/
private $category;

Entity/Category.php

/**
 * @ORMManyToMany(targetEntity=Video::class, mappedBy="category")
*/
private $videos;

Entity/Website.php

/**
 * @ORMManyToMany(targetEntity=Category::class, inversedBy="websites")
*/
private $categories;

I have hard difficulties to find the right SQL query, and I have tried many things with Doctrine. I’m a bit exhausted about Doctrine, but I know that can be done with Doctrine, and I want to do it the "right" way.

My current SQL query looks like this, and I think this is a bad query, I don’t like "SELECT"
from "SELECT IN" into "SELECT IN". (The "*" in "SELECT" is only here to show a more readable query, and I’m working with MySQL)

SELECT * 
FROM   category 
WHERE  category.id IN (
SELECT video_category.category_id FROM video_category WHERE  video_category.video_id IN (
SELECT video_category.video_id FROM video_category WHERE video_category.category_id IN ( 408, 504 )) 
GROUP  BY video_category.category_id) 

I’m trying to continue with Doctrine, but being honest, it’s hard. (408 and 504 are from my Website datas)

Any clue (I don’t ask for the full code, but more a way) on how to process on that with Doctrine ?

Best Thank you 🙂

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *