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->entityManager->createQueryBuilder();
    $qb->select(array('a','u'))
            ->from('Credit\Entity\UserCreditHistory', 'a')
            ->leftJoin('User\Entity\User', 'u', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.user = u.id')
            ->where("a.user = $users ")
            ->orderBy('a.created_at', 'DESC');

    $query = $qb->getQuery();
    $results = $query->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.

Ocramius · Accepted Answer · 2013-02-26T11:49:43.520

141

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,
    ),
    // ...
)

edited Feb 26 '13 at 11:49

answered Feb 26 '13 at 11:42

Ocramius

25,171
7
103
107

i just tried both approaches with/without relation. , 1st for with relation as u suggest i followed your code i am getting the result set. But i cannot see the Joined result set. for 2nd approach i want to say that i am getting data but it wasnt what i was exactly looking for – noobie-php Feb 26 '13 at 12:14
4

The first query gives you the `UserCreditHistory` object with the `User` object hydrated into it, since it is a fetch-join ( http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#joins ) – Ocramius Feb 26 '13 at 12:19
Yup very true, it is hydrated so i need to loop through to get the desired value – noobie-php Feb 26 '13 at 12:25
and you can also do this with the `QueryBuilder` like this: http://stackoverflow.com/a/19189240/667773 – Denes Papp Sep 18 '14 at 14:03
10

I am trying to do same (join without association) but I get just one dimensional array as a result. Like [UserCrediHistory,UserInstance,UserCreditHistory,UserInstance....]. Am I missing something? – kormik Mar 19 '15 at 00:26
@kormik: Use ``getScalarResult()`` instead of ``getResult()`` on your query and you'll be fine. See https://stackoverflow.com/a/46172218/430742 – Jpsy Sep 12 '17 at 09:07
1

method (ii) not working. Shwing error "Expected Doctrine\ORM\Query\Lexer::T_DOT, got 'u'"" – Davinder Kumar Oct 10 '17 at 12:33
I agree its showing the error above that Davinder Kumar sees. – Joseph Astrahan Oct 29 '17 at 20:47

How to do left join in Doctrine?

1 Answers1

Linked

Related