outer join in a query builder with doctrine

Question

I have an entity named PointsComptage.php and another one named Compteurs.php.

This is the relations between them:

// Compteurs.php 
/**
 * @var \PointsComptage
 *
 * @ORM\ManyToOne(targetEntity="PointsComptage", inversedBy="compteurs")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="pointscomptage_id", referencedColumnName="id")
 * })
 */
private $pointsComptage;

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\OneToMany(targetEntity="ParametresMesure", mappedBy="compteurs")
 */
private $parametresMesure;

/*  ...  */


// PointsComptage.php
/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\OneToMany(targetEntity="Compteurs", mappedBy="pointsComptage")
 */
private $compteurs;

/*  ...  */

This is the query in my repository entity to recover the compteurs with their attributes for one pointComptage:

$queryBuilder = $this->_em->createQueryBuilder();

$queryBuilder
  ->select ('c')
  ->from('MySpaceMyBundle:Compteurs', 'c')
  ->leftJoin('c.pointsComptage', 'pc')
  ->join('c.parametresMesure', 'pm')
  ->join('pm.typesUnite', 'tu')
  ->join('pm.typesParametre', 'tp')
  ->where('c.pointsComptage = pc.id')
  ->andWhere('pm.compteurs = c.id')
  ->andWhere('pm.typesUnite = tu.id')
  ->andWhere('pm.typesParametre = tp.id')
  ->andWhere('c.pointsComptage = :id')
  ->add('orderBy', 'c.miseEnService', 'ASC')
  ->setParameter('id', $id);

  return $queryBuilder->getQuery()
                      ->getResult();

The problem is that I recover well my compteurs for the pointComptage selected, but only the compteurs which have parametresMesure relation.

In my database, it's possible that a compteur have not parametreMesure datas.

How can I recover the compteurs which have not parametresMesure and the compteurs which have parametresMesure attributes (in the same queryBuilder)?

I read on the doctrine documentation that a leftJoin in a queryBuilder works like a outer join.

That I am trying to do, is to recover all the compteurs linked to the pointComptage selected, whether or not the parametresMesure.

Mease

If you search SO for 'doctrine outer join' there are several answers. One of those will almost certainly help you. – Oolite

Just use leftJoin instead of join – Falster

@PiWi Even if I use leftJoin instead join in my queryBuilder, my request returns me the same results, i-e just the compteurs which have parametresMesure, but I have well a compteur with no parametresMesure for the pointComptage selected before. – Mease

Answer 1

I found the solution. The problem was that I added the condition in my Where clause, but I needed to specify them in my leftJoin.

Here my code to understand what I did:

$queryBuilder = $this->_em->createQueryBuilder();

$queryBuilder
    ->select ('c')
    ->from('MySpaceMyBundle:Compteurs', 'c')
    ->leftJoin('c.pointsComptage', 'pc', 'WITH', 'pc.id = c.pointsComptage')
    ->leftJoin('c.parametresMesure', 'pm', 'WITH', 'pm.compteurs = c.id')
    ->leftJoin('pm.typesUnite', 'tu', 'WITH', 'pm.typesUnite = tu.id')
    ->leftJoin('pm.typesParametre', 'tp', 'WITH', 'pm.typesParametre = tp.id')
    ->andWhere('c.pointsComptage = :id')
    ->add('orderBy', 'c.miseEnService', 'ASC')
    ->setParameter('id', $id);

return $queryBuilder->getQuery()
                    ->getResult();

Like this I recover all my compteurs even if they don't have parametresMesure.

Mease

You found it yourself :-) I was just writing that answer myself ;-) If you use left joins this performs even better than filtering in the end. – Falster

Can you explain why we dont use ->Having('pc.id = c.pointsComptage') in place of using WITH ? – Durgy