DQL using INSTANCE OF with parameters return no results

[courtney-miles]

If I defined the following DQL and it returns the expected results.

$dql = <<<DQL
SELECT o FROM \MyProj\AbstractMyObject AS o 
WHERE o INSTANCE OF \MyProj\AlphaObject
DQL;
$q = $this->getEntityManager()->createQuery($dql);
$q->getResult(); // 1 row returns

However, when I supply the instance name as a parameter I get no results.

$dql = <<<DQL
SELECT o FROM \MyProj\AbstractMyObject AS o 
WHERE o INSTANCE OF :type
DQL;
$q = $this->getEntityManager()->createQuery($dql);
$q->setParameter(
    'type',
    '\MyProj\AlphaObject'
);
$q->getResult(); // 0 row returns

What could I be doing wrong? Or how can I can I debug this? I know to get the query with $q->getSQL(), but I can't see how to confirm exactly what value it resolves for use with the parameterised query.

I have also tried using \MyProj\AlphaObject::class and combinations beginning without the slash. Ultimately I'm looking to supply an array types, which also does not work, so I'm focusing on getting one parameter to work first.

These are the versions of the various doctrine packages I am using.

doctrine/annotations               v1.2.7  Docblock Annotations Parser
doctrine/cache                     v1.6.0  Caching library offering an object-oriented API for many cache ba...
doctrine/collections               v1.3.0  Collections Abstraction library
doctrine/common                    v2.6.1  Common Library for Doctrine projects
doctrine/dbal                      v2.5.5  Database Abstraction Layer
doctrine/inflector                 v1.1.0  Common String Manipulations with regard to casing and singular/pl...
doctrine/instantiator              1.0.5   A small, lightweight utility to instantiate objects in PHP withou...
doctrine/lexer                     v1.0.1  Base library for a lexer that can be used in Top-Down, Recursive ...
doctrine/orm                       v2.5.6  Object-Relational-Mapper for PHP```

[Ocramius]

Try binding $em->getClassMetadata(AlphaObject::class);

On 2 Jun 2017 05:28, "courtney-miles" notifications@github.com wrote:

If I defined the following DQL, it returns the expected results.

$dql = <<<DQLSELECT o FROM \MyProj\AbstractMyObject AS o WHERE o INSTANCE OF \MyProj\AlphaObjectDQL;$q = $this->getEntityManager()->createQuery($dql);$q->getResult(); // 1 row returns

However, when I supply the instance name as a parameter I get no results.

$dql = <<<DQLSELECT o FROM \MyProj\AbstractMyObject AS o WHERE o INSTANCE OF :typeDQL;$q = $this->getEntityManager()->createQuery($dql);$q->setParameter( 'type', '\MyProj\AlphaObject');$q->getResult(); // 0 row returns

What could I be doing wrong? Or how can I can I debug this? I know to get the query with $q->getSQL(), but I can't see how to confirm exactly what value it resolves for use with the parameterised query.

I have also tried using \MyProj\AlphaObject::class and combinations beginning without the slash. Ultimately I'm looking to supply an array types, which also does not work, so I'm focusing on getting one parameter to work first.

This are the versions of the various doctrine packages.

doctrine/annotations v1.2.7 Docblock Annotations Parser doctrine/cache v1.6.0 Caching library offering an object-oriented API for many cache ba... doctrine/collections v1.3.0 Collections Abstraction library doctrine/common v2.6.1 Common Library for Doctrine projects doctrine/dbal v2.5.5 Database Abstraction Layer doctrine/inflector v1.1.0 Common String Manipulations with regard to casing and singular/pl... doctrine/instantiator 1.0.5 A small, lightweight utility to instantiate objects in PHP withou... doctrine/lexer v1.0.1 Base library for a lexer that can be used in Top-Down, Recursive ... doctrine/orm v2.5.5 Object-Relational-Mapper for PHP

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/doctrine/doctrine2/issues/6483, or mute the thread https://github.com/notifications/unsubscribe-auth/AAJakA5nQpaWPX3E5V99uA2Dp_JMkosPks5r_4FVgaJpZM4NtyfU .

[courtney-miles]

Try binding $em->getClassMetadata(AlphaObject::class);

I have given that a go, but it does not appear to have helped.

This is my resulting code.

$dql = <<<DQL
SELECT o FROM \MyProj\AbstractMyObject AS o 
WHERE o INSTANCE OF :type
DQL;
$this->getEntityManager()->getClassMetadata(AlphaObject::class);
$q = $this->getEntityManager()->createQuery($dql);
$q->setParameter(
    'type',
    '\MyProj\AlphaObject'
);
$q->getResult(); // 0 row returns

In case it matters, this is triggered from within an Entity Repository method.

[Ocramius]

$dql = <<<DQL
SELECT o FROM \MyProj\AbstractMyObject AS o 
WHERE o INSTANCE OF :type
DQL;
$q = $this->getEntityManager()->createQuery($dql);
$q->setParameter(
    'type',
    $this->getEntityManager()->getClassMetadata(AlphaObject::class);
);
$q->getResult();

[courtney-miles]

Thank you @Ocramius. This works. Apologies for my lack of understanding on how getClassMetadata() is used.

So the last thing I can't figure out it how to get this to work with 2 or more instances.

So this is what I'm trying.

$dql = <<<DQL
SELECT o FROM \MyProj\AbstractMyObject AS o 
WHERE o INSTANCE OF (:types)
DQL;
$q = $this->getEntityManager()->createQuery($dql);
$q->setParameter(
    'types',
    [
        $this->getEntityManager()->getClassMetadata(AlphaObject::class),
        $this->getEntityManager()->getClassMetadata(BetaObject::class)
    ]
);
$q->getResult();

I was given the impression somewhere supplying an array would work, but can't find where now.

Thanks again for your help so far.

[trickeyone]

I've found that trying to use INSTANCE OF with a parameter doesn't work. The parameter is bound with the values given (i.e. the actual class name) instead of the discriminator type value.

So, this:

$dql = <<<DQL
SELECT o FROM \MyProj\AbstractMyObject AS o 
WHERE o INSTANCE OF (:types)
DQL;
$q = $this->getEntityManager()->createQuery($dql);
$q->setParameter(
    'types',
    [
        $this->getEntityManager()->getClassMetadata(AlphaObject::class),
        $this->getEntityManager()->getClassMetadata(BetaObject::class)
    ]
);

Will result in this kind of SQL query:

SELECT <fields> FROM my_object o WHERE o.<discriminator_column> IN (:types)

And the types parameter will be the below instead of the discriminator type values:

[
    'MyProj\AlphaObject',
    'MyProj\BetaObject',
]

I usually have to do the following to get it to work:

$dql = "SELECT o FROM \MyProj\AbstractMyObject AS o " .
    "WHERE o INSTANCE OF " . AlphaObject::class . " OR o INSTANCE OF " . BetaObject::class;

Doing this, the discriminator types are properly determined.

[Ocramius]

@courtney-miles INSTANCE OF accepts a single value: you tried to use it like an IN() expression

[Ocramius]

Closing here, as all questions were fully answered. If you think that the documentation could be improved, please send a patch there.