How to Perform Left Joins in Doctrine

When working with complex data models, it becomes necessary to retrieve data from multiple tables by establishing relationships between them. Left joins allow you to fetch all rows from one table and only the matching rows from the other table.

Issue Encountered

A common error that may arise when attempting a left join in Doctrine is receiving the following syntax error:

[Syntax Error] line 0, col 98: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'

This error occurs when "ON" is used in the join clause instead of "WITH." To resolve this, replace "ON" with "WITH" as shown below:

$qb->leftJoin('User\Entity\User', 'u', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.user = u.id')

Implementing Left Joins

To perform a left join in Doctrine, there are two approaches:

With an Association:

If your entity has an association with the table you want to join, you can use the following syntax:

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

In this case, "Credit\Entity\UserCreditHistory#user" represents the association between the two entities.

Without an Association:

If no association exists, you can use the following syntax:

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

This query retrieves records from both tables and returns a result set containing arrays of the following format:

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