0

My problem

I am performing a join using doctrine2 query builder and symfony3.

My controller contains following code:

$rp_konta = $this->getDoctrine()->getRepository('AppBundle:konto');
$query = $rp_konta->createQueryBuilder('p')
            ->select('p')
            ->addSelect('SUM(g.kwota)')
            ->leftJoin('p.ksiegowaniaWinien', 'g')
            ->getQuery();

which results in the SQL query:

SELECT k0_.id AS id_0, k0_.kod_konta AS kod_konta_1, k0_.nazwa_konta AS nazwa_konta_2, k0_.typ_konta AS typ_konta_3, k0_.aktywne AS aktywne_4, SUM(d1_.kwota) AS sclr_5 FROM konto k0_ LEFT JOIN dziennik d1_ ON k0_.id = d1_.konto_winien_id AND (d1_.usuniety IS NULL) WHERE k0_.id IN ('1', '2', '3');

Please note that each column name is followed by a suffix id_0 , kod_konta_1 and so on.

As my twig template looks like the following:

{% for konto in konta_pagination %}
<tr>
    <td>{{ konto.id }}</td>
{% endfor %}

and the id column returned as a result of SQL query is marked by doctrine as id_0 and not as id I get the following error:

Key "id" for array with keys "0, 1" does not exist in AppBundle::/raport/saldaKont.html.twig at line 18

How should I modify the query so that it returns the array with keys consistent without any suffix?

Update 1: Entities code

Based on @Tiriel request please find below also my entities code.

Dziennik entity:

class dziennik
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
/.../
 /**
     * @ORM\ManyToOne(targetEntity="konto", inversedBy="ksiegowaniaWinien")
      * @Gedmo\Versioned
     */
     protected $kontoWinien;

      /**
     * @ORM\ManyToOne(targetEntity="konto", inversedBy="ksiegowaniaMa")
      * @Gedmo\Versioned
     */
     protected $kontoMa;
}

Konto entity:

class konto
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

/.../

    /**
     * @ORM\OneToMany(targetEntity="dziennik", mappedBy="kontoWinien")
     */
     protected $ksiegowaniaWinien;

     /**
     * @ORM\OneToMany(targetEntity="dziennik", mappedBy="kontoMa")
     */
     protected $ksiegowaniaMa;

}
Antonio Baena Guerrero
  • 147
  • 15
Abdel5
  • 1,112
  • 3
  • 16
  • 39

1 Answers1

1

Try putting your addSelect('SUM(g.kwota)') AFTER your leftJoin('p.ksiegowaniaWinien', 'g') (at least that's the way I learned, mybe it'll fix your problem).

Otherwise, we need to see your entities. To me, you must have a column name id in each table, and so Doctrine has to suffix each one in it's query. It has never made any problem in my code, but maybe here...

Tiriel
  • 43
  • 1
  • 10
  • This does not seem to help. Normally using mySQL I would add in the select "as" e.g. "c.id as id" but doctrine seems not do allow this. In addition please note that I am not selecting two ids but only one and no other column name is doubled. From simple mySQL user perspective I should be able to call columns however I like. The question is how to do this in doctrine. I will update the code with entities. – Abdel5 Feb 28 '16 at 09:06
  • The problem is, in each entity you have an id property, thus even if you don't want to select both, Doctrine does it and has to rename them. That's why you have the suffixes in you query. Which method do you use to get the results? FetchColumn? – Tiriel Feb 28 '16 at 09:57
  • I was trying to use Knp Paginator and getResult() as well but both fails. Is there then any easier way to achieve clear formatting of such query in twig then? I have tried also that using INDEX BY may be of help but in my case it did not helped. – Abdel5 Feb 28 '16 at 10:56
  • And what about Paginator, the built-in Symfony component? It returns an array of paginated entities, iirc. – Tiriel Feb 28 '16 at 15:10