How to Do Left Join in Doctrine

How to do left join in Doctrine?

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

Left join ON condition AND other condition syntax in Doctrine

You can try this :

use Doctrine\ORM\Query\Expr;

->leftJoin('a.installations', 'i', Expr\Join::WITH, 'i.page = :page')
->setParameter('page', $page)

See function leftJoin in doc: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods

Left Join in Doctrine and Symfony

Do you miss "WITH" ?

$qm = $this->createQueryBuilder()
->select("alben.name","alben.alben_id") 
->from("alben") 
->leftJoin("video","video", "WITH", "alben.alben_id =video.album");
return $qm->getQuery()->getResult();

You have to tell doctrine there is repository :

in yaml : repositoryClass: AppBundle\Entity\VideoRepo

in annotation :

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="AppBundle\Entity\VideoRepository")
 */
class Video
{
    //...
}

Make shure you define VideoLeftJoin method as public in this VideoRepository class.

AND in LEFT JOIN with Doctrine

Try with :

return $this->createQueryBuilder('photo')
        ->leftJoin("photo.likedPhotos", "lp", Join::WITH, "photo.id = lp.Photo AND lp.user_id = 6")
        ->addSelect('lp')
        ->where('photo.is_public = 1')
        ->orderBy('photo.uploaded_at', 'DESC')
        ->getQuery();

See doctrine doc : https://www.doctrine-project.org/projects/doctrine-orm/en/2.9/reference/query-builder.html#line-number-5775ab09aefa638b14c776a733b4b61d2e324b74-43

HOW MAKE A update WITH left join DOCTRINE

similar question look at here, i see that join is not supported in update or delete queries.

---

Related Topics