13

So I have 3 entities within one table. I need to be able to search 2 out of the 3 entities in one select statement, but I'm not sure how to do this.

Veve
  • 6,643
  • 5
  • 39
  • 58
blacktie24
  • 4,985
  • 6
  • 41
  • 52

3 Answers3

19

Use the INSTANCE OF operator in your dql query like this (where User is your base class):

$em->createQuery('
    SELECT u 
    FROM Entity\User u 
    WHERE (u INSTANCE OF Entity\Manager OR u INSTANCE OF Entity\Customer)
');

Doctrine translates this in the sql query in a WHERE user.type = '...' condition.

See here for more details on the dql query syntax.

John Kary
  • 6,703
  • 1
  • 24
  • 24
Max
  • 15,693
  • 14
  • 81
  • 131
  • Note that when working with a discriminator map you might need to use the discriminator value, you can get this in your repository like this: `$this->getClassMetadata()->discriminatorValue` – robbe clerckx Mar 14 '17 at 09:32
5

The answer for multiple instances actually doesn't work. You would have to do something like this to check for multiple instances.

$classes = ['Entity\Manager', 'Entity\Customer'];
$qb = $this->createQueryBuilder('u');
->where('u.id > 10') //an arbitrary condition, to show it can be combined with multiple instances tests
->andWhere("u INSTANCE OF ('" . implode("','", $classes) . "')");
Tom Lei
  • 199
  • 1
  • 3
  • `$qb->expr()->isInstanceOf('u', '('.implode(',', $classes_names).')')` See more: https://github.com/doctrine/orm/issues/4462 – Maxim Mandrik Jan 11 '19 at 20:53
2

As commented by flu, if you want to retrieve some entities from different instances with a QueryBuilder instead of a DQL query, you can use an array as parameter:

$qb = $this->createQueryBuilder('u');
    ->where('u.id > 10') //an arbitrary condition, to show it can be combined with multiple instances tests
    ->andWhere('u INSTANCE OF :classes')
    ->setParameter('classes', ['Entity\Manager', 'Entity\Customer'])
;
Community
  • 1
  • 1
Veve
  • 6,643
  • 5
  • 39
  • 58