Doctrine 2 query builder and index by

Question

Is it possible to use index by with the doctrine 2 query builder, and if so what is the proper syntax?

score 44 · Answer 1 · answered Feb 27 '13 at 19:35

44

Beginning with 2.2, you can now include INDEX BY in your from statement. If you are adding a from clause,

$qb->from($class, $alias, $indexBy);

If you already have a from clause that you want to replace, then you can replace it with:

$qb->add('from', new Expr\From($class, $alias, $indexBy), false);

There's an open pull request to add it to the Repository's createQueryBuilder function as well, so hopefully this will get added soon.

answered Feb 27 '13 at 19:35

cmenning

630
5
10

Hey, I'm writing from 2015, and it's not there yet :-( – zerkms Apr 01 '15 at 00:53

score 7 · Answer 2 · answered Dec 15 '12 at 16:33

For an update. You can do something like this.

$qb = $entityManager->createQueryBuilder();
$qb->from($repository->getClassName(), 'a', 'a.id');
$qb->select(a);

$result = new ArrayCollection($qb->getQuery()->getResult());

As a result, array collection will contain properly indexed elements.

score 4 · Accepted Answer · answered Jul 06 '12 at 08:56

4

Here's one solution when using querybuilder, using manual injection of the index by clause -

(do you querybuilder statement)
$q  = $q->getQuery()->setDQL(str_replace('WHERE', 'INDEX BY yourIndexValue WHERE', $q->getDQL()));

This seems to work for me...

answered Jul 06 '12 at 08:56

Simon Kerr

566
1
4
3

I realise this is a very old answer, but I would just like to point out that this won't work for queries that don't have any WHERE clause. – rpkamp Feb 03 '16 at 11:30

score 3 · Answer 4 · answered Oct 28 '16 at 08:37

3

The proper syntax and the simplest would be:

$enityManager->getRepository('AppBundle:Entity')->createQueryBuilder('entity', 'entity.name')->getQuery()->getResult();

This would index the result by Entity name field.

answered Oct 28 '16 at 08:37

BrokenPixel

73
1
8

score 0 · Answer 5 · answered Sep 08 '16 at 08:29

You can also use a default INDEX BY of a foreign key e.g. "yourIndexValue_id" directly in your mapping:

/**
 * @ORM\OneToMany(targetEntity="Bar", mappedBy="foo", indexBy="bar_id")
 */
protected $bars;

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 -1 · Answer 6 · answered Feb 09 '12 at 10:44

Yes that's possible, using the query builder. A small example, let's assume we want to index by t.someField. Watch the third argument in from method.

<?php
$query = $em->createQueryBuilder()
    ->select('t.somefield', 't.someOtherField')
    ->setFrom('Entity\Table', 't', 't.someField')
    ->getQuery()
$results = $query->getArrayResult();

//your result will look something like:
$results['somefieldvalue'] = array(array('somefield' => 'value', 'someOtherField' => 'test'));
?>

This should work in Doctrine 2.1 at least. In Doctrine 2.0 it's not supported yet. In Doctrine 2.0 it's only supported when you specify it with DQL.

With Doctrine 2.1.0-DEV, I use `$qb->add('from', 'Entity\Table t INDEX BY t.id');` — Maxence, Feb 18 '12 at 13:30

Doctrine 2 query builder and index by

6 Answers6

Linked