Doctrine 2 - How to use discriminator column in where clause

Question

I was used discriminator column in where clause like this:

//f = root entity
$qb = $this->createQueryBuilder('f');
$qb->add('where', 'f.format = \'image\' OR f.format = \'text\'');

I've got an error: "Message: [Semantical Error] line 0, col 73 near 'format = 'image'': Error: Class Entities\File\AbstractFile has no field or association named format"

How can i use discriminator column in where clause?

Thanks.

Eventual

You should be able to use case when and INSTANCE OF - see my answer here: https://mcmap.net/q/373271/-how-to-select-discriminator-column-in-doctrine-2 – Vanesavanessa

Answer 1

I think that you should use INSTANCE OF

Orna

More specifically $db->andWhere('f INSTANCE OF Entity\File\Image') – Jodee

You can also use the keys of the discriminator map instead of the entity class name. – Luffa

If you use a place holder (e.g., 'f INSTANCE OF :type') then you need to set the parameter as follows: $query->setParameter('type', $em->getClassMetadata('Entity\File\Image')) – Boulevard

$db->andWhere($db->expr()->isInstanceOf('f', Entity\File\Image::class)); – Sherysherye

Answer 2

It would look in query builder like this:

$class = 'Entity\File\Image';

$qb = $this->createQueryBuilder('f');
$qb->where($qb->expr()->isInstanceOf('f', $class));

Note: that you will not be able to set the class as a parameter because it will be escaped.

Cato

Answer 3

for PHP 5.50 and above:

$this->createQueryBuilder('f')
        ->andWhere('f INSTANCE OF '.Image::class)

Perreault

Answer 4

As this latest doctrine version it is supported to query directly the discriminator value.

public function findOfType($discr)
    {
        $qb = $this->createQueryBuilder('e');
        $qb->where('e INSTANCE OF :discr');
        $qb->setParameter('discr', $discr);
        return $qb->getQuery()->getResult();
    }

will have a result query with this clause:

WHERE e0_.discr IN ('discriminator_passed_to_function')

Developer

Answer 5

This doctrine extension was very useful for me because I needed to access the parent class and INSTANCE OF doesn't works in that case.

https://gist.github.com/jasonhofer/8420677

For example: I have the following class structure:

BaseClass

Class1 inherits from BaseClass (discriminator = c1)

Class2 inherits from Class1 (discriminator = c2)

Class3 inherits from Class1 (discriminator = c3)

I want to select all entities from Class1 but not from Class2 or Class3

SELECT c FROM \Class1 c WHERE TYPE(c) = 'c1';

Determinant

Answer 6

All you need:

return $this->createQueryBuilder('entity')
            ->andWhere('entity.field = :field')
            ->leftJoin('entity.entity2', 'entity2')
            ->andWhere('entity2 INSTANCE OF :type')
            ->setParameter('field', 'value')
            ->setParameter('type', $this->getEntityManager()->getClassMetadata(Entity2::class))
            ->getQuery()
            ->getResult()
            ;

Mclin

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Estragon