CODEWITHSUNDEEP
symfonydoctrine-ormdoctrine-query
Thomas Fournet
Thomas Fournet

Reputation: 700

DQL access id from object property with left join

I realized this sql which works without problems

SELECT meeting.name, meeting.date, community.name, participation.isPresent, participation.user_id
    FROM meeting
    INNER JOIN community
    ON meeting.community_id = community.id
    AND community.is_active = 1

LEFT join participation
    ON meeting.id = participation.meeting_id
    AND participation.user_id = 1078

WHERE meeting.date >= CURRENT_DATE()
ORDER BY meeting.date DESC

I'm trying to reproduce it with the doctrine query builder but I never got the right result. The user id part doesn't seem to be part of the leftJoin function but is applied to the request globally, which is not what I want.

public function getNextMeetings()
{
    $qb = $this->createQueryBuilder('m')
        ->select('m.name AS meeting, m.date, c.name AS community, p.isPresent', 'IDENTITY(p.user) AS user')
        ->innerJoin('m.community', 'c')
        ->where('c.isActive = true')
        ->leftJoin('m.participations', 'p')

        //->leftJoin('p.user', 'u')
        //->where('u.id = 1078 OR u.id IS NULL')

        //->where('IDENTITY(p.user) = 1078')

        ->andWhere('m.date >= CURRENT_DATE()')
        ->orderBy('m.date', 'DESC');

    return $qb->getQuery()->execute();
}

My comments are what I tried to fix this issue.

Upvotes: 0

Views: 1068

Answers (1)

Jannes Botis
Jannes Botis

Reputation: 11242

Check Working with QueryBuilder: High level API methods

More precisely, the definition od leftJoin() function:

public function leftJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null);

You can place a condition on the joined Entity by:

use Doctrine\ORM\Query\Expr;

->leftJoin('m.participations', 'p', Expr\Join::WITH, 'p.user = :userId')
->setParameter('userId', 1078)

Note you do not need a condition for "meeting.id = participation.meeting_id", as this is autoapplied by the relation m.participations to the join constructed.

Upvotes: 1

Related Questions