文章/答案/技术大牛

发布

社区首页 >问答首页 >ManyToMany (Symfony 6)查询问题

问ManyToMany (Symfony 6)查询问题
EN

Stack Overflow用户

提问于 2022-03-03 09:02:26

回答 1查看 388关注 0票数 0

我有以下的情况，两个实体，“演员”和“电影”是在一个.*-1.**(多对多)关系。我试图实现的基本目标是根据电影id检索电影的所有演员名称，并将这些数据序列化为JSON (在目标URL‘base/ names’中隐式显示JSON数组)。

在普通SQL中，这是“从演员中选择a.name --一个内部连接movie_actor，a.id = ma.actor_id内连接电影m ON ma.movie_id = m.id，其中movie_id = 7;”
但是，在转换到DQL或使用本机查询时，我遇到了一个问题(请注意，我对Symfony :)的经验有限。
另外，我也很好奇，如何反过来工作，根据演员身份检索给定演员的所有电影标题。
数据库结构：在这里输入图像描述
“行为者”实体：

<?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,
        ];
    }
}

“电影”实体：

<?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,
        ];
    }
}

"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);
    }
}

事先谢谢你。

json

symfony

doctrine-orm

php

回答 1

Stack Overflow用户

发布于 2022-03-03 22:03:44

有一种简单的方法可以用createQueryBuilder()实现这一点。

索引函数如下：

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


    }
}

添加->join('a.movies', 'm')时，Doctrine会自动识别Actor和Movie之间的关系，并知道存在一个movie_actor表。证据是当您进入symfony分析器时，您将看到与您想要的完全相同的查询。

来自symfony分析器的查询：

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 = ?

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/71334500

复制

相似问题

问ManyToMany (Symfony 6)查询问题
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ManyToMany (Symfony 6)查询问题EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ManyToMany (Symfony 6)查询问题
EN