Doctrine: Is it possible to INDEX BY a related field?

Question

I have a Doctrine model (Assignment), which has a many-to-one relationship with another model (Region). Assignments are owned by users (with each user having only one assignment per region at a time), and I am trying to use indexBy to have the user's array of assignments be keyed by the ID of the assignment's region. However, I only get standard 0..n numeric keys.

When I try to run a DQL query like SELECT am, reg, user FROM Assignment am INDEX BY [...] JOIN am.region reg JOIN am.user user WHERE user.id = ?1, none of these values for INDEX BY work:

• region (Error: Invalid PathExpression. Must be a StateFieldPathExpression.)
• region_id (Error: Class ...\Assignment has no field or association named region_id)
• region.id (Error: Expected end of string, got '.')

Is this possible? If not, then what would be a convenient way to access a User's assignment on a region without indexBy?

Answer 1

I was dealing with the same problem today. Fortunately I've found the solution : )

First of all, you have to declare additional column in ORM mapping:

Abdulklarapl\My\EntityA:
type: entity
table: entityA
manyToOne:
    entityB:
        targetEntity: EntityB
        joinColumn:
            name: label_id
            referencedColumnName: id
id:
    id:
        type: integer
        id: true
        generator:
            strategy: AUTO
fields:
    value:
        type: text
    entityB_id:
        type: integer
lifecycleCallbacks: {  }

notice that I've declared entityB_id as a field + I've configured manyToOne relation by adding a joinColumn clause

so now you can use entityB_id as scalar value

$doctrine->getEntityManager()->createQueryBuilder()
        ->select('c')
        ->from('AbdulklaraplMyBundle:EntityA', 'c', 'c.entityB_id')
        ->getQuery()->getResult();

it will return assoc array

[
    c.entityB_id: {
        id: "",
        value: ""
        entityB_id: ""
    }
]

you can also use AbstractQuery::HYDRATE_ARRAY as a argument for getResult() - it will return assoc array with array instead the objects