46

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.

Can Aydoğan
  • 1,040
  • 2
  • 10
  • 23
  • You should be able to use `case when` and `INSTANCE OF` - see my answer here: https://stackoverflow.com/a/59690590/645042 – Sam Critchley Jan 11 '20 at 00:37

6 Answers6

75

I think that you should use INSTANCE OF

MatTheCat
  • 18,071
  • 6
  • 54
  • 69
Koc
  • 2,375
  • 2
  • 22
  • 26
  • 14
    More specifically $db->andWhere('f INSTANCE OF Entity\File\Image') – gawpertron Nov 29 '11 at 15:56
  • 2
    You can also use the keys of the discriminator map instead of the entity class name. – Pavel Petrov Mar 26 '14 at 08:29
  • 6
    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'))` – Leukipp Jun 08 '15 at 00:11
  • 5
    `$db->andWhere($db->expr()->isInstanceOf('f', Entity\File\Image::class));` – Dejv Jan 14 '16 at 11:53
14

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.

Aistis
  • 3,695
  • 2
  • 34
  • 34
4

for PHP 5.50 and above:

$this->createQueryBuilder('f')
        ->andWhere('f INSTANCE OF '.Image::class)
Andrew Atkinson
  • 4,103
  • 5
  • 44
  • 48
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')
Leggy7
  • 483
  • 9
  • 23
1

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';
Ragnar
  • 4,393
  • 1
  • 27
  • 40
0

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()
            ;
Jonathan Deleray
  • 1
  • 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](/help/how-to-answer). – Community Feb 23 '22 at 17:36