CODEWITHSUNDEEP
mysqlsymfonyjoindoctrine-orm
darkbluesun
darkbluesun

Reputation: 827

LEFT Join onto two tables in doctrine2 query builder

I have an entity called tracking. It relates to a User and a Course. I also have an entity called credential which officially links User and Course

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

Tracking is a joining entity of sorts, but it's not the primary join between the two. I have a query where I already have the user and the course joined, and I want to left-join the tracking if it exists. I've tried to simplify my example.

    $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');

What I'm trying to achieve here, is a list of users who are enrolled in courses, the number of courses, and where possible the number of completed courses.

Unfortunately, doctrine can only seem to join to either the user table or the course table, but not to both. This might even be a mysql limitation. I've debugged this over and over - and I've run into the problem several times with different examples - and I just can't seem to find a solution other than using ->from(Tracking) which would eliminate students who haven't started any courses, and stats from courses they haven't started. I've googled over and over again, but It's so hard to search for this problem and not get 'How to join two tables with Doctrine'.

I get the error Column not found: 1054 Unknown column 'c1_.id' in 'on clause' which I assume means it can join on t.user = user but not t.course = course

Here is the actual code and error

    $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): An exception occurred while executing '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]: Column not found: 1054 Unknown column 's1_.id' in 'on clause'

Upvotes: 2

Views: 3701

Answers (1)

LBA
LBA

Reputation: 4109

This is just a hint how to achieve it. I cannot give you the correct answer as you don't give enough details. But this will help you to achieve what you need.

$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');

Upvotes: 1

Related Questions