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?

could you please post the models and relations between them? — manix, Sep 06 '12 at 04:36
I encounter the same kind of problem, except i got a `[Semantical Error] line 0, col 121 near 'alert WHERE clientAlert.client': Error: 'alert' is already defined.`. — Florian Klein, Feb 25 '14 at 09:01

score 2 · Answer 1 · answered Apr 23 '14 at 12:09

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

score 2 · Answer 2 · answered Sep 08 '16 at 08:27

If you need to INDEX BY a foreign key e.g. "region_id" it is possible in your mapping:

/**
 * @ORM\OneToMany(targetEntity="Region", mappedBy="user", indexBy="region_id")
 */
protected $regions;

The feature was added here.

Unfortunately it does not seem to be documented that you have to use the name of the column of the foreign key itself.

Working with Indexed Associations

score -2 · Answer 3 · answered Sep 06 '12 at 04:49

-2

Import the Query class (optional):

use \Doctrine\ORM\Query;

Create the query:

$query = $this->data->em->createQuery('
    SELECT a 
    FROM Assignment a 
    INDEX BY a.reg //to set array custom key
    WHERE a.user = :user');
$query->setParameter('user', 3); //user with id 3

//set the hidration mode in order to work with read-only arrays
$assignments = $query->getResult(Query::HYDRATE_ARRAY);

answered Sep 06 '12 at 04:49

manix

14,537
11
70
107

I tried using this query, but it gave the same "Error: Invalid PathExpression. Must be a StateFieldPathExpression." – LeafStorm Sep 06 '12 at 19:58

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

3 Answers3