ManyToMany (Symfony 6) querying issue-CodePudding

I have the following situation, two entities, "Actor" and "Movies" which are in a 1..* - 1..* (many to many) relationship. What I am trying to achieve is basically retrieving all the actor names for a movie based on the movie id and serializing this data as JSON (implicitly showing a JSON array in the target URL 'base/movies'.

In plain SQL, this works "SELECT a.name FROM actor a INNER JOIN movie_actor ma ON a.id = ma.actor_id INNER JOIN movie m on ma.movie_id = m.id WHERE movie_id = 7;"
However, I have an issue when it comes to conversion to DQL or using a native query (please note that I have limited experience with Symfony :) ).
Also, I am curious how would the other way around work, retrieving all movie title for a given actor based on the actor id.
Database structure: enter image description here
The "Actor" entity:

<?php

namespace App\Entity;

use App\Repository\ActorRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ActorRepository::class)]
class Actor implements \JsonSerializable
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private $id;

    #[ORM\Column(type: 'string', length: 255)]
    private $name;

    #[ORM\ManyToMany(targetEntity: Movie::class, mappedBy: 'actors')]
    private $movies;

    public function __construct()
    {
        $this->movies = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }

    /**
     * @return Collection<int, Movie>
     */
    public function getMovies(): Collection
    {
        return $this->movies;
    }

    public function addMovie(Movie $movie): self
    {
        if (!$this->movies->contains($movie)) {
            $this->movies[] = $movie;
            $movie->addActor($this);
        }

        return $this;
    }

    public function removeMovie(Movie $movie): self
    {
        if ($this->movies->removeElement($movie)) {
            $movie->removeActor($this);
        }

        return $this;
    }


    public function jsonSerialize(): array {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'movies' => $this->movies,
        ];
    }
}

The "Movie" entity:

<?php

namespace App\Entity;

use App\Repository\MovieRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: MovieRepository::class)]
class Movie implements \JsonSerializable
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string', length: 255)]
    private string $title;

    #[ORM\Column(type: 'integer')]
    private int $releaseYear;

    #[ORM\Column(type: 'string', length: 255, nullable: true)]
    private string $description;

    #[ORM\Column(type: 'string', length: 255)]
    private string $imagePath;

    #[ORM\ManyToMany(targetEntity: Actor::class, inversedBy: 'movies')]
    private $actors;

    public function __construct()
    {
        $this->actors = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getTitle(): ?string
    {
        return $this->title;
    }

    public function setTitle(string $title): self
    {
        $this->title = $title;

        return $this;
    }

    public function getReleaseYear(): ?int
    {
        return $this->releaseYear;
    }

    public function setReleaseYear(int $releaseYear): self
    {
        $this->releaseYear = $releaseYear;

        return $this;
    }

    public function getDescription(): ?string
    {
        return $this->description;
    }

    public function setDescription(?string $description): self
    {
        $this->description = $description;

        return $this;
    }

    public function getImagePath(): ?string
    {
        return $this->imagePath;
    }

    public function setImagePath(string $imagePath): self
    {
        $this->imagePath = $imagePath;

        return $this;
    }

    /**
     * @return Collection<int, Actor>
     */
    public function getActors(): Collection
    {
        return $this->actors;
    }

    public function addActor(Actor $actor): self
    {
        if (!$this->actors->contains($actor)) {
            $this->actors[] = $actor;
        }

        return $this;
    }

    public function removeActor(Actor $actor): self
    {
        $this->actors->removeElement($actor);

        return $this;
    }

    public function jsonSerialize()
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'description' => $this->description,
            'image_path' => $this->imagePath,
            'actors' => $this->actors,
        ];
    }
}

The "MoviesController":

<?php

namespace App\Controller;

# Below, marked by the "use" keyword the class imports are defined.
use App\Entity\Actor;
use App\Entity\Movie;
use App\Repository\MovieRepository;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\Serializer\Normalizer\JsonSerializableNormalizer;
use Symfony\Component\Serializer\Normalizer\NormalizerInterface;
use Symfony\Component\Serializer\Serializer;
use Symfony\Component\Serializer\SerializerInterface;

class MoviesController extends AbstractController
{
    #[Route('/movies', name: 'movies')]
    public function index(EntityManagerInterface $entityManager, SerializerInterface $serializer): Response
    {
//        $repository = $entityManager->getRepository(Movie::class);
//        $movies = $repository->findAll();
//        return new JsonResponse($movies, Response::HTTP_OK);

        $sql = 'SELECT a.name FROM Actor a INNER JOIN  movie_actor ma ON a.id = ma.actor_id INNER JOIN movie m on ma.movie_id = m.id WHERE movie_id = 7';

        $rsm = new ResultSetMappingBuilder($entityManager);
        $rsm->addRootEntityFromClassMetadata('App\Entity\Movie', 'm');
        $rsm->addJoinedEntityFromClassMetadata('App\Entity\Actor', 'a', 'm', 'actors', array('id' => 'actor_id'));
        return new JsonResponse($rsm, Response::HTTP_OK);
    }
}

Thank you beforehand.

CodePudding user response：

To begin, switch on Symfony5.4 LTS because Symfony6 lack of documentation

Then, look at this site, it explained really well doctrine query builders.

CodePudding user response：

There is simple way to achieve this with createQueryBuilder()

The index function will be this :

class MoviesController extends AbstractController
{
    #[Route('/movies', name: 'movies')]
    public function index(EntityManagerInterface $entityManager, SerializerInterface $serializer): Response
    {

    $qb = $entity_manager->createQueryBuilder();
    $qb ->select('a')
        ->from(Actor::class, 'a' )
        ->join('a.movies', 'm')
        ->where('m.id = :identifier')
        ->setParameter('identifier', 7)
    ;


    $actors = $qb->getQuery()->getResult();
    
    // Remaining logic here


    }
}

When you add the ->join('a.movies', 'm'), Doctrine identify automatically the relationship between Actor and Movie entities and knows that there is a movie_actor table. The proof is when you go to the symfony profiler and you will see the exact same query that you want.

Query from the symfony profiler :

SELECT
  a0_.name AS name_1,
FROM
  actor a0_
  INNER JOIN movie_actor m2_ ON a0_.id = m2_.actor_id
  INNER JOIN movie m1_ ON m1_.id = m2_.movie_id
WHERE
  m1_.id = ?