getArrayResult or getSql not working properly ? #5872

New Issue

admin · 2026-01-22T15:20:42+01:00

admin commented

2026-01-22 15:20:42 +01:00

Originally created by @poolerMF on GitHub (Feb 3, 2018).

Originally assigned to: @Ocramius on GitHub.

I got entity, that have manyToOne (user)
I made simple QB (select table)

$queryBuilder->getQuery()->getSQL():

SELECT 
  c0_.id AS id_0, 
  c0_.name AS name_1, 
  c0_.user AS user_2 
FROM 
  client c0_ 
ORDER BY 
  c0_.id DESC 
LIMIT 
  20 OFFSET 0

I'm calling $queryBuilder->getQuery()->getArrayResult()

real executed SQL from profiler:

SELECT 
  c0_.id AS id_0, 
  c0_.name AS name_1
FROM 
  client c0_ 
ORDER BY 
  c0_.id DESC 
LIMIT 
  20 OFFSET 0

where is problem ? should getSql select user ? or should getArrayResult return user ?

Originally created by @poolerMF on GitHub (Feb 3, 2018). Originally assigned to: @Ocramius on GitHub. I got entity, that have manyToOne (user) I made simple QB (select table) **$queryBuilder->getQuery()->getSQL():** SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.user AS user_2 FROM client c0_ ORDER BY c0_.id DESC LIMIT 20 OFFSET 0 I'm calling $queryBuilder->getQuery()->getArrayResult() **real executed SQL from profiler:** SELECT c0_.id AS id_0, c0_.name AS name_1 FROM client c0_ ORDER BY c0_.id DESC LIMIT 20 OFFSET 0 where is problem ? should getSql select user ? or should getArrayResult return user ?

admin added the Question label 2026-01-22 15:20:42 +01:00

admin closed this issue

2026-01-22 15:20:42 +01:00

admin commented

2026-01-22 15:20:43 +01:00

@Ocramius commented on GitHub (Feb 3, 2018):

@poolerMF what are the mappings involved in this query? Also, did you make sure there is no caching?

@Ocramius commented on GitHub (Feb 3, 2018): @poolerMF what are the mappings involved in this query? Also, did you make sure there is no caching?

admin commented

2026-01-22 15:20:43 +01:00

@poolerMF commented on GitHub (Feb 3, 2018):

query:

$em->getRepository(Client::class)->createQueryBuilder('t')->getQuery()->getArrayResult();

mapping:

/**
 * @var ...\User
 *
 * @ORM\ManyToOne(targetEntity="...\User")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="user", referencedColumnName="id", nullable=true)
 * })
 */
private $user;

@poolerMF commented on GitHub (Feb 3, 2018): **query:** $em->getRepository(Client::class)->createQueryBuilder('t')->getQuery()->getArrayResult(); **mapping:** /** * @var ...\User * * @ORM\ManyToOne(targetEntity="...\User") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="user", referencedColumnName="id", nullable=true) * }) */ private $user;

admin commented

2026-01-22 15:20:44 +01:00

@Ocramius commented on GitHub (Feb 3, 2018):

Ah, that's normal then, since the association is not part of the entity.

@Ocramius commented on GitHub (Feb 3, 2018): Ah, that's normal then, since the association is not part of the entity.

admin commented

2026-01-22 15:20:44 +01:00

@poolerMF commented on GitHub (Feb 3, 2018):

@Ocramius association is not part of the entity ?
sorry what ? maybe I do not understand

can you send me example, when is it part of the entity ?

and still issue - is getSql returning correct SQL ? becose it is different from real one

@poolerMF commented on GitHub (Feb 3, 2018): @Ocramius association is not part of the entity ? sorry what ? maybe I do not understand can you send me example, when is it part of the entity ? and still issue - is getSql returning correct SQL ? becose it is different from real one

admin commented

2026-01-22 15:20:45 +01:00

@Ocramius commented on GitHub (Feb 3, 2018):

I made a quick test to show how this works:

<?php

declare(strict_types=1);

namespace Doctrine\Tests\ORM\Functional\Ticket;

use Doctrine\ORM\AbstractQuery;
use Doctrine\ORM\Annotation as ORM;
use function sprintf;

class DDC0000Test extends \Doctrine\Tests\OrmFunctionalTestCase
{
    protected function setUp()
    {
        parent::setUp();

        $this->schemaTool->updateSchema([
            $this->em->getClassMetadata(DDC0000Root::class),
            $this->em->getClassMetadata(DDC0000Association::class),
        ]);
    }

    public function testAssociationFieldsAreNotInArrayResult()
    {
        $root        = new DDC0000Root();
        $association = new DDC0000Association();

        $root->association = $association;

        $this->em->persist($root);
        $this->em->persist($association);
        $this->em->flush();

        $result = $this
            ->em
            ->createQuery(sprintf('SELECT r FROM %s r', DDC0000Root::class))
            ->getArrayResult();

        self::assertSame([['id' => DDC0000Root::class]], $result);
    }
}

/** @ORM\Entity() */
class DDC0000Root
{
    /** @ORM\Id @ORM\GeneratedValue(strategy="NONE") @ORM\Column(type="string") */
    public $id = self::class;

    /** @ORM\ManyToOne(targetEntity=DDC0000Association::class) */
    public $association;
}

/** @ORM\Entity() */
class DDC0000Association
{
    /** @ORM\Id @ORM\GeneratedValue(strategy="NONE") @ORM\Column(type="string") */
    public $id = self::class;
}

Also, yes, fields that do not need to be fetched are also removed from the SELECT clause.

If you still want the ID of the association, you should add it to your SELECT with the IDENTITY() function.

@Ocramius commented on GitHub (Feb 3, 2018): I made a quick test to show how this works: ```php <?php declare(strict_types=1); namespace Doctrine\Tests\ORM\Functional\Ticket; use Doctrine\ORM\AbstractQuery; use Doctrine\ORM\Annotation as ORM; use function sprintf; class DDC0000Test extends \Doctrine\Tests\OrmFunctionalTestCase { protected function setUp() { parent::setUp(); $this->schemaTool->updateSchema([ $this->em->getClassMetadata(DDC0000Root::class), $this->em->getClassMetadata(DDC0000Association::class), ]); } public function testAssociationFieldsAreNotInArrayResult() { $root = new DDC0000Root(); $association = new DDC0000Association(); $root->association = $association; $this->em->persist($root); $this->em->persist($association); $this->em->flush(); $result = $this ->em ->createQuery(sprintf('SELECT r FROM %s r', DDC0000Root::class)) ->getArrayResult(); self::assertSame([['id' => DDC0000Root::class]], $result); } } /** @ORM\Entity() */ class DDC0000Root { /** @ORM\Id @ORM\GeneratedValue(strategy="NONE") @ORM\Column(type="string") */ public $id = self::class; /** @ORM\ManyToOne(targetEntity=DDC0000Association::class) */ public $association; } /** @ORM\Entity() */ class DDC0000Association { /** @ORM\Id @ORM\GeneratedValue(strategy="NONE") @ORM\Column(type="string") */ public $id = self::class; } ``` Also, yes, fields that do not need to be fetched are also removed from the `SELECT` clause. If you still want the ID of the association, you should add it to your `SELECT` with the `IDENTITY()` function.

admin commented

2026-01-22 15:20:45 +01:00

@poolerMF commented on GitHub (Feb 3, 2018):

when I use

$qb->setHint(Query::HINT_INCLUDE_META_COLUMNS, true);

then I will get manyToOne columns

but getSql() SHOULD NOT RETURN COLUMNS THAT WILL BE NOT FETCHED
I still think, getSql is not working correctly

@poolerMF commented on GitHub (Feb 3, 2018): when I use $qb->setHint(Query::HINT_INCLUDE_META_COLUMNS, true); then I will get manyToOne columns but getSql() **SHOULD NOT RETURN COLUMNS THAT WILL BE NOT FETCHED** I still think, getSql is not working correctly

admin commented

2026-01-22 15:20:45 +01:00

@Ocramius commented on GitHub (Feb 3, 2018):

In the test above, try these two:

            $this
                ->em
                ->createQuery(sprintf('SELECT r FROM %s r', DDC0000Root::class))
                ->setHydrationMode(AbstractQuery::HYDRATE_ARRAY)
                ->getSQL()

This one is the default:

            $this
                ->em
                ->createQuery(sprintf('SELECT r FROM %s r', DDC0000Root::class))
                ->setHydrationMode(AbstractQuery::HYDRATE_OBJECT)
                ->getSQL()

@Ocramius commented on GitHub (Feb 3, 2018): In the test above, try these two: ```php $this ->em ->createQuery(sprintf('SELECT r FROM %s r', DDC0000Root::class)) ->setHydrationMode(AbstractQuery::HYDRATE_ARRAY) ->getSQL() ``` This one is the default: ```php $this ->em ->createQuery(sprintf('SELECT r FROM %s r', DDC0000Root::class)) ->setHydrationMode(AbstractQuery::HYDRATE_OBJECT) ->getSQL() ```

admin commented

2026-01-22 15:20:45 +01:00

@poolerMF commented on GitHub (Feb 3, 2018):

I don't want to be rude, but you are not answering to my question/point
getSql is not returning real sql that will be executed

@poolerMF commented on GitHub (Feb 3, 2018): I don't want to be rude, but you are not answering to my question/point getSql is not returning real sql that will be executed

admin commented

2026-01-22 15:20:47 +01:00

@Ocramius commented on GitHub (Feb 3, 2018):

@poolerMF getSql returns the SQL for the matching hydration mode (https://github.com/doctrine/doctrine2/issues/7036#issuecomment-362796456)

@Ocramius commented on GitHub (Feb 3, 2018): @poolerMF `getSql` returns the SQL for the matching hydration mode (https://github.com/doctrine/doctrine2/issues/7036#issuecomment-362796456)

admin referenced this issue

2026-01-22 16:05:20 +01:00

[PR #5872] Improved Output in Validation with no Entities #9764

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5872