Doctrine2查询生成器左选择加入 [英] Doctrine2 Query Builder Left Join with Select

问题描述

我想使用doctrine2查询生成器实现此SQL：

I want to implement this SQL using doctrine2 query builder:

SELECT c.*, COUNT(s.id) AS studentCount
FROM classes c
LEFT JOIN (
    SELECT * 
    FROM student_classes
    WHERE YEAR =  '2012'
) sc ON c.id = sc.class_id
LEFT JOIN students s ON sc.student_id = s.id
GROUP BY c.id

我尝试过这个，但没有工作

I tried this one but didn't work

$qb = $this->getEntityManager()
    ->getRepository('Classes')
    ->createQueryBuilder('c');
$qb->select('c.id AS id, c.name AS name, COUNT(s) AS studentCount');
$qb->leftJoin(
    $qb->select('sc1')
        ->from('StudentClasses', 'sc1')
        ->where('sc1.year = :year')
        ->setParameter('year', $inputYear), 
    'sc2'
);
$qb->leftJoin('sc2.students', 's');
$qb->groupBy('c.id');
return $qb->getQuery()->getScalarResult();

或者我应该使用nativeSQL吗？

or should I use nativeSQL instead?

任何帮助将不胜感激，
谢谢。

any help would be appreciated, thanks.

推荐答案

你想做什么是非常有趣的，因为JOIN在SELECT中似乎不支持使用DQL或QueryBuilder的Doctrine2。当然，您可以尝试使用本地查询。

What are you trying to do is really interesting, because JOIN on a SELECT seems to not be supported by Doctrine2 with DQL or QueryBuilder. Of course, you can try with a native query.

但是，为了回答您的问题，我相信您不需要在SELECT上进行JOIN。简单地说，在 StudentClasses 上加入JOIN，然后在WHERE中添加一个关于 $ year 的条件！ WHERE子句是为此而设计的。

However, to answer to your question, I believe that you don't need to make a JOIN on a SELECT. Simply, JOIN on StudentClasses and then add a condition in the WHERE about the $year! The WHERE clause is made for that.

这篇关于Doctrine2查询生成器左选择加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！