With a OneToMany relation between entities, how create the right queryBuilder with Doctrine (on the Inversed Side entity)

Question

I have these 3 entities in my symfony2.6 project:

Compteurs.php

class Compteurs
{
    /**
     * @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;

ParametresMesure.php:

class ParametresMesure
{
    /**
     * @var Compteurs
     *
     * @ORM\ManyToOne(targetEntity="Compteurs", inversedBy="parametresMesure")
     * @ORM\JoinColumn(name="compteurs_id", referencedColumnName="id")
     */
    private $compteurs;

PointsComptage.php

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

As you can see, the these entites are linked each other.

I created a table for display all PointsComptage and datas I need into the table. This the code for the table in my twig view:

  <tbody>
    {% for currentPC in pointsComptage %}
      <tr>
        <td>{{ currentPC.ensembles}}</td>
        <td>{{ currentPC.nomPointComptage}}</td>
        <td>{{ currentPC.invariantPointComptage}}</td>
        <td>{{ currentPC.typesEnergie}}</td>
        <td>{{ currentPC.typesComptage}}</td>
        <td>{{ currentPC.consoProduction}}</td>
        <td>
          <a href="{{ path('detailsPointsComptage', {'id': currentPC.id }) }}"><button class="btn btn-info btn-xs">Détail</button></a>
          <a href="{{ path('modifierPointsComptage', {'id': currentPC.id }) }}"><button class="btn btn-warning btn-xs">Modifier</button></a>
        </td>
      </tr>
    {% endfor %}
  </tbody>

Of course, for now, all works well. When I click on the details button, it redirects me on a new page, where there is another table who displaying all the datas I need for the current Point Comptage I choose before (currentPC.id).

So this is my controller method for displaying the detail page:

public function detailsPointsComptageAction($id)
    {
      $em=$this->getDoctrine()->getManager();

      $detailPC = $this->getDoctrine()
                     ->getRepository('MySpaceMyBundle:PointsComptage')
                     ->findOneById($id);

      $compteur = $this->getDoctrine()
                     ->getRepository('MySpaceMyBundle:PointsComptage:Compteurs')
                     ->getCompteursAttributesByPC($id);


        return $this->render('MySpaceMyBundle:MyFolder:detailsPC.html.twig', array( 'detailsPC' => $detailPC, 'compteurs' => $compteur));
    }

To explain briefly: I recover the id of my currentPC I cliked on for displaying his detail page, and on his detail page I need to display all compteurs associated to the currentPC and all the other attributes associated to these compteurs (ParametreMesure.php).

So I created a queryBuilder for Compteurs, in CompteursRepository.php, with my getCompteursAttributesByPC($id) method ($id matches with the $id for PointsComptage.php).

This is the code for: CompteursRepository.php

public function getCompteursAttributesByPC($id)
  {
    $queryBuilder = $this->_em->createQueryBuilder();

    $queryBuilder
      ->select('pm', 'c')
      ->from('MySpaceMyBundle:PointsComptage', 'pc')
      ->from('MySpaceMyBundle:ParametresMesure', 'pm')
      ->leftJoin('MySpaceMyBundle:Compteurs', 'c', 'WITH', 'pm.compteurs = c.id')
      ->where('pc.id = c.pointsComptage ')
      ->andWhere('pc.id = :id')
      ->setParameter('id', $id);

      return $queryBuilder->getQuery()
                          ->getArrayResult();
  }

But I have this error with my repository method:

An exception occurred while executing 'SELECT c0_.id AS id_0, c0_.matricule_compteur AS matricule_compteur_1, c0_.mise_en_service_compteur AS mise_en_service_compteur_2, c0_.mise_hors_service_compteur AS mise_hors_service_compteur_3, p1_.id AS id_4, p1_.code_parametre AS code_parametre_5, p1_.nom_parametre AS nom_parametre_6 FROM points_comptage p2_ LEFT JOIN compteurs c0_ ON (p1_.compteurs_id = c0_.id), parametres_mesure p1_ WHERE p2_.id = c0_.pointscomptage_id AND p2_.id = ?' with params ["1"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p1_.compteurs_id' in 'on clause'

Someone could explain me how can I recover all the datas I need for the detail page and display them in my detail page?

I hope you understand well what I am trying to do here: one PointComptage -> all of his Compteurs -> all of the ParametresMesure of the Compteurs linked to the PointComptage I choose first.

---

UPDATE -EDIT

I try with this query following @Jovan Perovic suggestion:

public function getCompteursAttributesByPC($id)
{
  $queryBuilder = $this->_em->createQueryBuilder();

  $queryBuilder
    ->select('pc')
    ->addSelect('pm')
    ->addSelect('c')
    ->from('MySpaceMyBundle:Compteurs', 'c')
    ->leftJoin('c.pointsComptage', 'pc')
    ->join('c.parametresMesure', 'pm')
    ->where('c.pointsComptage = :id')
    ->andWhere('c.id = pm.compteurs')
    ->setParameter('id', $id);

    return $queryBuilder->getQuery()
                        ->getScalarResult();
}

Note that in my queryBuilder I change getArrayResult() by getScalarresult(), because with a var dump with getArrayResult I have this:

array (size=2)
  0 => 
    array (size=6)
      'id' => int 5
      'matriculeCompteur' => string 'egfizegilf88' (length=12)
      'miseEnService' => 
        object(DateTime)[638]
          public 'date' => string '2012-05-15 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'miseHorsService' => 
        object(DateTime)[684]
          public 'date' => string '2015-06-19 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'pointsComptage' => 
        array (size=5)
          'id' => int 1
          'invariantPointComptage' => string 'invariant 1' (length=11)
          'nomPointComptage' => string 'test 1' (length=6)
          'codeDistribution' => string 'code test 1' (length=11)
          'localisationPointComptage' => string 'local test 1' (length=12)
      'parametresMesure' => 
        array (size=2)
          0 => 
            array (size=3)
              ...
          1 => 
            array (size=3)
              ...
  1 => 
    array (size=6)
      'id' => int 10
      'matriculeCompteur' => string 'ghhh666' (length=7)
      'miseEnService' => 
        object(DateTime)[642]
          public 'date' => string '2015-06-01 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'miseHorsService' => 
        object(DateTime)[688]
          public 'date' => string '2015-06-19 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'pointsComptage' => 
        array (size=5)
          'id' => int 1
          'invariantPointComptage' => string 'invariant 1' (length=11)
          'nomPointComptage' => string 'test 1' (length=6)
          'codeDistribution' => string 'code test 1' (length=11)
          'localisationPointComptage' => string 'local test 1' (length=12)
      'parametresMesure' => 
        array (size=1)
          0 => 
            array (size=3)
              ...

As you can see, there are no results for ParametresMesure.

And with getScalarResult() I have this:

array (size=3)
  0 => 
    array (size=12)
      'c_id' => int 5
      'c_matriculeCompteur' => string 'egfizegilf88' (length=12)
      'c_miseEnService' => 
        object(DateTime)[638]
          public 'date' => string '2012-05-15 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'c_miseHorsService' => 
        object(DateTime)[692]
          public 'date' => string '2015-06-19 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'pc_id' => int 1
      'pc_invariantPointComptage' => string 'invariant 1' (length=11)
      'pc_nomPointComptage' => string 'test 1' (length=6)
      'pc_codeDistribution' => string 'code test 1' (length=11)
      'pc_localisationPointComptage' => string 'local test 1' (length=12)
      'pm_id' => int 1
      'pm_codeParametre' => string '658' (length=3)
      'pm_nomParametre' => string 'test 658' (length=8)
  1 => 
    array (size=12)
      'c_id' => int 5
      'c_matriculeCompteur' => string 'egfizegilf88' (length=12)
      'c_miseEnService' => 
        object(DateTime)[690]
          public 'date' => string '2012-05-15 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'c_miseHorsService' => 
        object(DateTime)[684]
          public 'date' => string '2015-06-19 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'pc_id' => int 1
      'pc_invariantPointComptage' => string 'invariant 1' (length=11)
      'pc_nomPointComptage' => string 'test 1' (length=6)
      'pc_codeDistribution' => string 'code test 1' (length=11)
      'pc_localisationPointComptage' => string 'local test 1' (length=12)
      'pm_id' => int 3
      'pm_codeParametre' => string 'gjgfjgfj489489' (length=14)
      'pm_nomParametre' => string 'hyhfhfhfhf' (length=10)
  2 => 
    array (size=12)
      'c_id' => int 10
      'c_matriculeCompteur' => string 'ghhh666' (length=7)
      'c_miseEnService' => 
        object(DateTime)[695]
          public 'date' => string '2015-06-01 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'c_miseHorsService' => 
        object(DateTime)[642]
          public 'date' => string '2015-06-19 00:00:00' (length=19)
          public 'timezone_type' => int 3
          public 'timezone' => string 'Europe/Paris' (length=12)
      'pc_id' => int 1
      'pc_invariantPointComptage' => string 'invariant 1' (length=11)
      'pc_nomPointComptage' => string 'test 1' (length=6)
      'pc_codeDistribution' => string 'code test 1' (length=11)
      'pc_localisationPointComptage' => string 'local test 1' (length=12)
      'pm_id' => int 7
      'pm_codeParametre' => string 'ygyugyg' (length=7)
      'pm_nomParametre' => string 'bhkighfsighf' (length=12)

The difference is that with getScalarresult(), my query recover the datas of ParametresMesure.php linked to the Compteurs.php whereas with getArrayResult() not.

My view code and my controller code are still the same, but I have now this error:

Key "matriculeCompteur" for array with keys "c_id, c_matriculeCompteur, c_miseEnService, c_miseHorsService, pc_id, pc_invariantPointComptage, pc_nomPointComptage, pc_codeDistribution, pc_localisationPointComptage, pm_id, pm_codeParametre, pm_nomParametre" does not exist in MySpaceMyBundle:MyFolder:detailsPC.html.twig at line 41

The line 41 matches with this code: <td>{{ currentCompteur.matriculeCompteur}}</td>

As you can understand, matriculeCompteur corresponds to $matriculeCompteur in my Compteurs.php entity.

This is my code for recover all datas linked to the compteurs in relation with the id of PointComptage selected before, then all ParametresMesure linked to these Compteurs:

<tbody>
  {% for currentCompteur in compteurs %}
    <tr>
      <td>{{ currentCompteur.matriculeCompteur}}</td>
      <td>{{ currentCompteur.miseEnService|date("Y-m-d", "Europe/Paris")}}</td>
      <td>{{ currentCompteur.miseHorsService|date("Y-m-d", "Europe/Paris")}}</td>
      <td class="no-cell-padding">
        <table class="inner-table table stripe row-border order-column display table-bordered table-hover compact" cellspacing="0" width="100%">
            <tr>
              <td>code for parametresMesure</td>
              <td>code for parametresMesure</td>
              <td>code for parametresMesure</td>
              <td>code for parametresMesure</td>
            </tr>
        </table>
      </td>
    </tr>
  {% endfor %}
</tbody>

Answer 1

When you specify multiple FROMs, later one will overwrite the previous ones.

So, instead of writing:

$queryBuilder
      ->select('pm', 'c')
      ->from('MySpaceMyBundle:PointsComptage', 'pc')
      ->from('MySpaceMyBundle:ParametresMesure', 'pm')
      ->leftJoin('MySpaceMyBundle:Compteurs', 'c', 'WITH', 'pm.compteurs = c.id')
      ->where('pc.id = c.pointsComptage ')
      ->andWhere('pc.id = :id')
      ->setParameter('id', $id);

I believe you need something like this:

$queryBuilder
      ->select('pc', 'c', 'pm')
      ->from('MySpaceMyBundle:PointsComptage', 'pc')
      ->join('pc.compteurs', 'c')
      ->leftJoin('c.parametresMesure', 'pm')
      ->where('pc.id = :id')
      ->setParameter('id', $id);

Since you do not have a direct link from PointsComptage to ParametresMesure, you first need to join PointsComptage with Compteurs and then Compteurs with ParametresMesure.

Just note, I wrote ->join('pc.compteurs', 'c') but based on your logic you might want to use leftJoin instead.

Is this what you're trying to achieve?

Hope this helps.

Answer 2

I choose @Jovan Perovic because it allowed me to find the good answer. This is my code for the queryBuilder I made for find my results:

public function getCompteursAttributesByPC($id)
{
  $queryBuilder = $this->_em->createQueryBuilder();

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

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

and in my twig view:

<tbody>
  {% for compteur in arrayCompteur %}
    <tr>
      <td>{{ compteur.matriculeCompteur}}</td>
      <td>{{ compteur.miseEnService|date("Y-m-d", "Europe/Paris")}}</td>
      <td>{{ compteur.miseHorsService|date("Y-m-d", "Europe/Paris")}}</td>
      <td>
        <table >
          <tr>
            <th>Code</th>
            <th>Nom</th>
            <th>Type</th>
            <th>Unité</th>
          </tr>
          {% for parametre in compteur.parametresMesure %}
            <tr>
              <td>
                {{ parametre.codeParametre}}
                {% if parametre.codeParametre is empty %}
                  <em>aucune informations</em>
                {% endif %}
              </td>
              <td>
                {{ parametre.nomParametre}}
                {% if parametre.nomParametre is empty %}
                  <em>aucune informations</em>
                {% endif %}
              </td>
              <td>
                {{ parametre.typesUnite}}
                {% if parametre.typesUnite is empty %}
                  <em>aucune informations</em>
                {% endif %}
              </td>
              <td>
                {{ parametre.typesParametre}}
                {% if parametre.typesParametre is empty %}
                  <em>aucune informations</em>
                {% endif %}
              </td>
            </tr>
          {% endfor %}
        </table>
      </td>
    </tr>
  {% endfor %}
</tbody>