symfony2 leftjoin condition on right - losing data

Question

I have 3 entities

. "Ejuridique" has 1 relation "onetomany" with

. "Project" has 1 relation "onetomany" with

. "Photo"

When I do 1 query on "Ejuridique" and cascading left join on "Project" and after on "Photo", adding conditions on projects and photos, I lose all the "Ejuridiques" who do not have projects. But I would like to keep all the "Ejuridique" even if they do not have projects.

I have tried all the solutions given for other questions similar with this one found on forums, but it still does not work.

with querybuilder in my repository

class EjuridiqueRepository extends EntityRepository
{
    public function myFindAll()
        {
            $qb = $this->createQueryBuilder('a')
                ->leftjoin('a.projets', 'pr' )
                ->leftjoin('pr.photos', 'ph' )
                ->where('pr.projetPosition = :position_pr')
                ->ANDwhere('ph.position = :position_ph')
                ->setParameters(array('position_pr'=> "1",'position_ph'=> "1"))
                ->addSelect('pr')
                ->addSelect('ph');

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

OR with DQL

$dql = "SELECT e,pr,ph FROM chlwebsitesBundle:Ejuridique e LEFT JOIN e.projets pr  
LEFT JOIN pr.photos ph WHERE pr.projetPosition=1 AND ph.position=1";

$query = $this->getDoctrine()->getEntityManager()->createQuery($dql);
$ejuridiques = $query->getResult();

I lose data with both the 2 methods.

Thank you in advance for helping me

Answer 1

The problem with your query is that your WHERE clause has conditions for project and photo entities, so any entity where these relations are null will not be returned.

You need to put these conditions in your LEFT JOIN i.e.

$qb = $this->createQueryBuilder('a')
->leftjoin('a.projets', 'pr' \Doctrine\ORM\Query\Expr\Join::WITH, 'pr.projetPosition = :position_pr')
->leftjoin('pr.photos', 'ph', \Doctrine\ORM\Query\Expr\Join::WITH, 'ph.position = :position_ph' )
->setParameters(array('position_pr'=> "1",'position_ph'=> "1"))
->addSelect('pr')
->addSelect('ph');