How to do left join in Doctrine

Question

This is my function where I m trying to show the User history  For this I need to display the user s current credits along with his credit history   This is what I am trying to do    public function getHistory  users         qb    this- gt entityManager- gt createQueryBuilder         qb- gt select array  a   u                - gt from  Credit Entity UserCreditHistory    a               - gt leftJoin  User Entity User    u    Doctrine ORM Query Expr Join  WITH   a user   u id               - gt where  a user    users                - gt orderBy  a created at    DESC          query    qb- gt getQuery         results    query- gt getResult         return  results      However  I get this error        Syntax Error  line 0  col 98  Error  Expected Doctrine ORM Query Lexer  T WITH  got  ON    Edit  I replaced  ON  with  WITH  in the join clause and now what I see is only 1 value from the joined column

User · Accepted Answer

If you have an association on a property pointing to the user (let's say Credit\Entity\UserCreditHistory#user, picked from your example), then the syntax is quite simple:

public function getHistory($users) {
    $qb = $this->entityManager->createQueryBuilder();
    $qb
        ->select('a', 'u')
        ->from('Credit\Entity\UserCreditHistory', 'a')
        ->leftJoin('a.user', 'u')
        ->where('u = :user')
        ->setParameter('user', $users)
        ->orderBy('a.created_at', 'DESC');

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

Since you are applying a condition on the joined result here, using a LEFT JOIN or simply JOIN is the same.

If no association is available, then the query looks like following

public function getHistory($users) {
    $qb = $this->entityManager->createQueryBuilder();
    $qb
        ->select('a', 'u')
        ->from('Credit\Entity\UserCreditHistory', 'a')
        ->leftJoin(
            'User\Entity\User',
            'u',
            \Doctrine\ORM\Query\Expr\Join::WITH,
            'a.user = u.id'
        )
        ->where('u = :user')
        ->setParameter('user', $users)
        ->orderBy('a.created_at', 'DESC');

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

This will produce a resultset that looks like following:

array(
    array(
        0 => UserCreditHistory instance,
        1 => Userinstance,
    ),
    array(
        0 => UserCreditHistory instance,
        1 => Userinstance,
    ),
    // ...
)

[php] How to do left join in Doctrine?

Examples related to php

Examples related to doctrine-orm

Examples related to zend-framework2