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
.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With