getArrayResult on entity with ManyToOne association

Question

Having the follow basic tables (one-to-many relationship)
Client - Has many users.
Users - Each user belongs to single client.

In a very simple example if I query the user entity (Querybuilder) with getArrayResult() I see the following:

The actual generated SQL contains the foreign key field to be returned (i.e. ClientID)
The actual returned data array does NOT contain the foreign key field.

At this stage I do not need to return foreign data and so do not need to join to the associated table.

So question is...
What or how do I return the foreign key value in my array?

Query is:

   $qb = $this->_em->createQueryBuilder();  
   $qb->select('e');  
   $qb->from('Entity\User', 'e');

SQL is:

SELECT w0_.Id AS Id0, w0_.Name AS Name2, w0_.ClientID AS ClientID7
FROM users w0_

score 21 · Accepted Answer · edited Apr 17 '13 at 16:02

21

Try to set the HINT_INCLUDE_META_COLUMNS query hint on the query (not the builder) before you execute it.

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

edited Apr 17 '13 at 16:02

Manse

37,765
10
83
108

answered Apr 01 '11 at 22:24

romanb

6,391
2
20
19

Will this not work if I try and grab just the foreign key field in the select statement (as opposed to the whole entity)? It doesn't seem to be for me. – Jeremy Hicks Jun 07 '11 at 16:09
Great, this one's a lifesaver! – ZvL Nov 20 '14 at 23:43
1

thank you, now i am a step forward, but i need relations as array not just id... to be able to process it with json with one request and not 10 requests for each id here – Erik Kubica Jan 20 '15 at 16:44
This is interesting, but doesn't work quite as expected. Getting the foreign key seems normal to me, but for some reason the associated column names are returned in snake_case, whereas the other properties are returned in CamelCase. – vctls Dec 13 '16 at 09:37

score 0 · Answer 2 · answered Mar 31 '11 at 17:54

0

As far as I know, you can't do this, because ClientID is not a property of User. User has a property like $client, and that client entity has an $id.

This is confusing you because you're dealing with Entites but you're still thinking in SQL.

The solution, though slightly less efficient, would probably be to join the Client entity into your DQL query, and then get $results[N]['client']['id'] (or similar, I'm not too familiar with getResultArray())

answered Mar 31 '11 at 17:54

timdev

61,857
6
82
92

2

This is the solution that I was using, and appreciate that I should be thinking 'entities and ORM', not SQL... but we still want efficient SQL being generated. We really don't want to join to another table unnecessarily. Very inefficient. Thanks for the comment though... – MarkOfSine Apr 06 '11 at 13:27

getArrayResult on entity with ManyToOne association

2 Answers2

Linked