Doctrine query with nullable/optional join

Question

I got a method on my repository class to get me Article with their join (category, image ...) and not every Article have categories or image, what don't give me back all expected result, cause my current repository function return only Article who have Categories and Image not null and just ignore what having null value.

My Article entity have the following relationship.

 /**
 * @ORM\ManyToMany(targetEntity="App\ArticleBundle\Entity\Category", cascade={"persist"})
 */
private $categories;

/**
 * @ORM\ManyToOne(targetEntity="App\ArticleBundle\Entity\Image", cascade={"persist"})
 * 
 */
private $image;

And this is my repository function

public function getArticle($id)
{
    $qb = $this->createQueryBuilder('a')
    ->where('a.id = :theId')
        ->setParameter('theId', $id)
    ->join('a.author', 'auth')
        ->addSelect('auth')
    ->join('a.categories', 'cat')
        ->addSelect('cat')
    ->join('a.image', 'img')
        ->addSelect('img');

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

Now I want to know if I can get Article which have categories, image or not in one query with the join. I want to say when using the Doctrine lazy loading (by avoid the join in the query) I get the expected result.

Answer 1

Use ->leftJoin() to get Article which has categories, image or not in one query:

public function getArticle($id)
{
    $qb = $this
        ->createQueryBuilder('a')
        ->addSelect('auth', 'cat', 'img')
        ->join('a.author', 'auth')
        ->leftJoin('a.categories', 'cat')
        ->leftJoin('a.image', 'img')
        ->where('a.id = :theId')
        ->setParameter('theId', $id)
    ;

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

Thus, this avoids extra queries when Doctrine attempts to load the related properties in a lazy manner.

Explanation:

Using ->join() or ->innerJoin():

This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B).

Using ->leftJoin():

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table.

Source: Visual-Representation-of-SQL-Joins explained in detail by C.L. Moffatt