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?

Armada

could you please post the models and relations between them? – Vizier

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.. – Proa

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

Eringo

Answer 2

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

Mccune

Answer 3

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); 

Vizier

I tried using this query, but it gave the same "Error: Invalid PathExpression. Must be a StateFieldPathExpression." – Armada