How to write a DQL select statement to search some, but not all the entities in a single table inheritance table

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.


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) . "')");

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

Tags:

Doctrine Orm