通过与条令DQL连接关系来选择对象 - select objects by joining relationships with doctrine DQL

select objects by joining relationships with doctrine DQL

本文关键字：关系选择对象连接 DQL | 更新日期: 2023-09-27

我有三个实体：Project、RequestMatcher和Response。以下是它们之间的关系：

$project->getRequestMatchers(); // Project has many RequestMatchers
$requestMatcher->getActiveResponse(); // RequestMatcher has one active Response

如何编写DQL以获取Project的所有活动Response？

    $query = $this->getEntityManager()->createQuery(
        "SELECT resp FROM $responseClass resp
               JOIN $requestMatcherClass req WITH req.project = :project
               WHERE resp.id IN(req.active_response_id)"
    );
    $query->setParameter('project', $project);

不过，我得到了以下例外：

[Doctrine'ORM'Query'QueryException]                                 
  [Syntax Error] line 0, col 230: Error: Expected Literal, got 'req'

我也尝试过，结果相同：

    $query = $this->getEntityManager()->createQuery(
        "SELECT resp FROM $responseClass resp
               LEFT JOIN resp.requestMatcher req WITH req.project = :project
               WHERE resp IN(req.activeResponse)"
    );

这是将给我id的的sql

select active_response_id from (
  select active_response_id from request_matchers where project_id = 1
) t

我不相信这是最好的方法，但我用以下方法解决了这个问题：

    $query = "SELECT resp FROM $responseClass resp
              WHERE resp.id IN (
                  SELECT IDENTITY(req.activeResponse) FROM $requestMatcherClass req
                  WHERE req.project = :project
              )";

您也可以尝试使用QueryBuilder对象，比如：

$this->getDoctrine()->getRepository('YourBundle:Project')->createQueryBuilder('p')
    ->join('p.requestMatchers', 'rm', 'Doctrine'ORM'Query'Expr'Join::WITH, 'rm.project = :project')
    ->join('rm.activeReponse', 'ar')
    ->addSelect('rm.*')
    ->addSelect('ar.*')
    ->setParameter('project', $project)
    ->getQuery()
    ->execute();