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.

If you search SO for 'doctrine outer join' there are several answers. One of those will almost certainly help you. — redbirdo, Jun 24 '15 at 08:45
@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. — french_dev, Jun 24 '15 at 09:07

score 12 · Accepted Answer · edited Jun 24 '15 at 11:03

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.

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. — Pi Wi, Jun 24 '15 at 09:26
Can you explain why we dont use ->Having('pc.id = c.pointsComptage') in place of using WITH ? — famas23, Nov 19 '17 at 16:44

outer join in a query builder with doctrine

1 Answers1