mysql - LEFT 在学说查询生成器中加入两个表

Question

我有一个名为 tracking 的实体.它涉及一个User和一个 Course .我还有一个名为 credential 的实体正式链接 User和 Course

User <- Tracking -> Course || User <- Credential -> Course

跟踪是某种连接实体，但它不是两者之间的主要连接。我有一个查询，其中我已经加入了用户和类(class)，如果存在，我想左加入跟踪。我已尝试简化我的示例。

    $q->select(
        'user.id',
        'user.firstname',
        'user.lastname',
        'count(t) as courses',
        'count(t.completed) as completed'
    );
    $q->from(Credential::class, 'c');
    $q->from(Course::class, 'course');
    $q->from(User::class, 'user');
    $q->leftJoin(Tracking::class, 't', 'WITH', 't.user = user and t.course = course');
    $q->where('c.user = user and c.object = course');
    $q->groupBy('user');

我在这里想要实现的是注册类(class)的用户列表、类(class)数量以及可能的已完成类(class)数量。

不幸的是，doctrine 似乎只能连接到用户表或类(class)表，而不能同时连接到两者。这甚至可能是 mysql 的限制。我已经一遍又一遍地调试这个问题 - 我已经用不同的例子多次遇到这个问题 - 而且我似乎无法找到除了使用之外的解决方案 ->from(Tracking)这将消除尚未开始任何类(class)的学生，以及他们尚未开始的类(class)的统计数据。我在谷歌上搜索了一遍又一遍，但很难搜索到这个问题并且找不到“如何使用 Doctrine 连接两个表”。

我收到错误 Column not found: 1054 Unknown column 'c1_.id' in 'on clause'我认为这意味着它可以加入 t.user = user但不是 t.course = course

这是实际的代码和错误

    $q = $this->em->createQueryBuilder();
    $q->select(
        'user.id',
        'user.firstname',
        'user.lastname',
        'count(sc.id) as courses',
        'count(ct.commenced) as commenced',
        'count(ct.completed) as completed',
        'avg(ct.scorePercent) as avgscore',
        'avg(ct.totalTime) as avgtime'
    );
    $q->from(Security\Credential::class, 'c');
    $q->from(Security\SecurableCourse::class, 'sc');
    $q->from(Security\AccreditableInheritance::class, 'ai');
    $q->from(Security\AccreditableUser::class, 'au');
    $q->from(User::class, 'user');
    $q->join(Tracking\CourseTracking::class, 'ct', 'WITH', 'ct.objectIdentity = sc and ct.user = user');
    $q->where('sc = c.securable and ai.parent = c.accreditable and au = ai.child and user = au.user');
    $q->andWhere('c.action = :action and sc.course in (:courses)');
    $q->setParameter('action', 'study')->setParameter('courses', $courses);
    $q->groupBy('user.id');
    $users = $q->getQuery()->getScalarResult();

Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0): 执行时发生异常 'SELECT u0_.id AS id_0, u0_.firstname AS firstname_1, u0_.lastname AS lastname_2, count(s1_.id) AS sclr_3, count(t2_.commenced) AS sclr_4, count(t2_.completed) AS sclr_5, avg(t2_.scorePercent) AS sclr_6, avg(t2_.totalTime) AS sclr_7 FROM Credential c3_ INNER JOIN Tracking t2_ ON (t2_.objectIdentity_id = s1_. id AND t2_.user_id = u0_.id) AND t2_.dtype IN ('coursetracking') AND ((t2_.deleted IS NULL OR t2_.deleted > '2016-04-26 08:33:31')), SecurableIdentity s1_ , AccreditableInheritance a4_, AccreditableIdentity a5_, User u0_ WHERE (((s1_.id = c3_.securable_id AND a4_.parent_id = c3_.accreditable_id AND a5_.id = a4_.child_id AND u0_.id = a5_.user_id) AND (c3_.action = ? AND s1_.course_id IN (?, ?, ?))) AND ((u0_.deleted IS NULL OR u0_.deleted > '2016-04-26 08:33:31'))) AND (s1_.dtype IN ('securablecourse') AND a5_.dtype IN ('accreditableuser')) GROUP BY u0_.id' with params [\"study\",\"46\",\"45\",\"160\"]:\n\nSQLSTATE[42S22]: 找不到列: 1054 'on 子句' 中的未知列 's1_.id'

Answer 1

这只是一个如何实现它的提示。我不能给你正确的答案，因为你没有提供足够的细节。但这将帮助您实现所需。

$q->select(u, t, co, ce);
$q->from('User', 'u');
$q->leftJoin('u.tracking', 't');
$q->leftJoin('t.course', 'co');
$q->leftJoin('u.credential', 'ce');