dongzan2740
2016-02-24 11:33
浏览 38
已采纳

如何使用Doctrine在Symfony2中实现子查询?

I need to count the number of items returned in the subquery. If I write the subquery how DQL - all good, but if I try to build a query via QueryBuilder - I get an error.

Subquery DQL:

$qb3 = $this->createQueryBuilder('c')
            ->select('COUNT(c.id)')
            ->where('c.id IN (SELECT cl.id FROM Acme\AppBundle\Entity\ClassC cl INNER JOIN Acme\AppBundle\Entity\ClassP p WHERE p.var1 = :var1 AND p.var2 = cl.id GROUP BY cl.id)')
            ->setParameter('var1', $var);

Subquery via QueryBuilder:

$qb = $this->createQueryBuilder('c');
$qb->select('COUNT(c.id)')
   ->where(
       $qb->expr()->in(
           'c.id',
           $this->createQueryBuilder('cl')
                ->select('cl.id')
                ->innerJoin('Acme\AppBundle\Entity\ClassP', 'p')
                ->where('p.var1 = :var1')
                ->setParameter('var1', $var)
                ->andWhere('p.var2 = cl.id')
                ->groupBy('cl.id')
                ->getDQL()
     )
 );

Both versions return the same DQL.

Error: screen

图片转代码服务由CSDN问答提供 功能建议

我需要计算子查询中返回的项目数。 如果我写子查询如何DQL - 一切都很好,但如果我尝试通过QueryBuilder构建一个查询 - 我得到一个错误。

子查询DQL: < pre> $ qb3 = $ this-&gt; createQueryBuilder('c') - &gt; select('COUNT(c.id)') - &gt; where('c.id IN(SELECT cl) .id FROM Acme \ AppBundle \ Entity \ ClassC cl INNER JOIN Acme \ AppBundle \ Entity \ ClassP p WHERE p.var1 =:var1 AND p.var2 = cl.id GROUP BY cl.id)') - &gt; setParameter ('var1',$ var);

通过QueryBuilder的子查询:

  $ qb = $ this-  &gt; createQueryBuilder('c'); 
 $ qb-&gt; select('COUNT(c.id)')
  - &gt;其中(
 $ qb-&gt; expr() - &gt; in(\  n'c.id',
 $ this-&gt; createQueryBuilder('cl')
  - &gt; select('cl.id')
  - &gt; innerJoin('Acme \ AppBundle \ Entity \ ClassP',  'p')
  - &gt; where('p.var1 =:var1')
  - &gt; setParameter('var1',$ var)
  - &gt; andWhere('p.var2 = cl.id'  )
  - &gt; groupBy('cl.i  d')
  - &gt; getDQL()
)
); 
   
 
 

两个版本都返回相同的DQL。

错误: 屏幕

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • duanmeng1950 2016-02-24 11:39
    最佳回答

    Try to move setParameter() to main level of query.

    $qb = $this->createQueryBuilder('c');
    $qb->select('COUNT(c.id)')
    ->where(
       $qb->expr()->in(
           'c.id',
           $this->createQueryBuilder('cl')
                ->select('cl.id')
                ->innerJoin('Acme\AppBundle\Entity\ClassP', 'p')
                ->where('p.var1 = :var1')
                ->andWhere('p.var2 = cl.id')
                ->groupBy('cl.id')
                ->getDQL()
     )
    )
    ->setParameter('var1', $var);
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题