How to retrieve only entity id using Symfony QueryBuilder?

Question

I'm trying to personalize a symfony 2.4 repository query to retrieve only some fields. Everything is ok with flat fields but when retrieving Entity fields, I only get the id (by default) but not the whole entity data. My query:

    $select = $this->createQueryBuilder('ca')
    ->select('ca.id, ca.name')
    ->leftJoin('ca.users', 'user')
    ->addSelect('(user) as users'); 

    $select->setMaxResults($count);

    return $select->getQuery()->getResult();

The result is: [{id: 1, name: "Some name", users: 1}, ...]

How can I change this query for users to contain the whole user data, like id, name, address, etc.?

@revengeance a many-to-many user relation, if you mean that. — Vandervals, May 31 '18 at 10:47
No, i thought fields. Id, name and what else ? Maybe add code with your both entity classes, so we can check whats wrong. Currently looking at your code, everything, except ->addSelect seems okay. Also are you sure, that you need ->leftJoin in your example :) ? With many to many usually its inner join to get needed elements for some id — revengeance, May 31 '18 at 10:51
No, it needs to be a leftJoin for sure, as innerJoin deletes some entries without user. The entities doesn't have anything special: a simple class with no extends and simple private fields like id, name, status... and public methods. Also, other fields joining entities have the same problem. — Vandervals, May 31 '18 at 10:58
For me in 2.4 you dont have to specify select to get the whole user data just add your join, but i dont know how work getArrayResult(). — Charlie Lucas, Jun 05 '18 at 08:24
getArrayResult() will not return back related entities resulting in a flat structure. Changing it to getResult() will allow you to return and inspect the entire entity and its linked data. Unless thats what you want? A cartesian product of multiple joins? — Ne Ma, Jun 07 '18 at 12:37
@NeilMasters I'm sorry to tell you that using getResult I'm getting the same flat result. I'm getting only id instead of all user data. — Vandervals, Jun 08 '18 at 08:45

Djengobarm · Accepted Answer · 2018-06-07T08:19:53.900

4

This works for me:

$select = $this->createQueryBuilder('ca')
    ->select('partial ca.{id, name}, users')
    ->leftJoin('ca.users', 'users');

$select->setMaxResults($count);

return $select->getQuery()->getArrayResult();

edited Jun 07 '18 at 08:19

answered Jun 05 '18 at 13:39

Djengobarm

398
3
7

Cesur APAYDIN · Answer 2 · 2018-06-08T09:07:07.457

2

You should try this way. You can use Partial.

$select = $this->createQueryBuilder('ca')
->select('partial ca.{id, name}')
->leftJoin('ca.users', 'users')
->addSelect('users');

A detailed description of this issue is available here. Doctrine2: Cannot select entity through identification variables without choosing at least one root entity alias

edited Jun 08 '18 at 09:07

answered Jun 05 '18 at 16:24

Cesur APAYDIN

806
3
11
24

This result is the same as doing only `$select = $this->createQueryBuilder('ca')` It is returning the whole instance with every join, which I'm trying to avoid – Vandervals Jun 08 '18 at 08:48

score 1 · Answer 3 · answered May 31 '18 at 10:06

1

change this:

->select('ca.id, ca.name')

to this:

->select('ca')

answered May 31 '18 at 10:06

Alessandro Minoccheri

35,521
22
122
171

Done, but it doesn't solve the problem. The result is `[ { 0: { id:1, name:"Some name" }, 'users': 1}, { }...]` – Vandervals May 31 '18 at 10:10
Have you tried to delete cache? And inside the entity there is some kind of exclusion policy? @Vandervals – Alessandro Minoccheri May 31 '18 at 10:16
I've cleared cache and also checked the dump() result instead of the serialized value. The problem is already there. – Vandervals May 31 '18 at 10:18
Did you try addSelect('user') or ->leftJoin('ca.users', 'users') and addSelect('users') – Cesur APAYDIN May 31 '18 at 10:38
@RamazanApaydın yes and this is the error displayed: `Cannot select entity through identification variables without choosing at least one root entity alias ` – Vandervals May 31 '18 at 10:44

score 1 · Answer 4 · answered Jun 08 '18 at 09:56

i hope this will useful for you.

$qb = $this->em->createQueryBuilder();
        $qb->select('o.id as order_id,o.createdBy as created_by,o.user as user_id');
        // for getting selective fields
        //$qb->select('o');        
        //for getting all fields in certain table
        $qb->from('Entity\Orders', 'o');
        $qb->setMaxResults(1); 
        // for getting single record only.
        return $qb->getQuery()->getOneOrNullResult(); 
        // for getting single record or null record.
        //return $qb->getQuery()->getResult(); 
        // for getting multiple records.

feel free to ask any query regarding it.

How to retrieve only entity id using Symfony QueryBuilder?

4 Answers4