dongpaozhi5734
2015-06-22 15:53
浏览 67
已采纳

使用实体之间的OneToMany关系,如何使用Doctrine创建正确的queryBuilder(在反向边实体上)

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>
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • donpb2823 2015-06-22 16:11
    最佳回答

    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.

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题