Jira issue originally created by user romanb:
We need entity type expressions in DQL that allow to discriminate types in the query. In SQL this would mostly just map down to a discriminator column/value comparison. Entity type expressions are covered in section 126.96.36.199 of the spec. The proposed syntax there sounds good to me, unless someone has a better idea.
1) SELECT e FROM Employee e WHERE TYPE(e) IN (Exempt, Contractor)
2) SELECT e FROM Employee e WHERE TYPE(e) IN (:empType1, :empType2)
3) SELECT e FROM Employee e WHERE TYPE(e) IN :empTypes
4) SELECT TYPE(e) FROM Employee e WHERE TYPE(e) <> Exempt
Some implementation ideas: In the case of "binding" the types (Nr. 2/3) this is non-trivial since we can not just bind class names as strings since we need to convert the class names to their discriminator type. Instead, we could have special handling for parameters of type "ClassMetadata" or "ReflectionClass". That means in case Nr. 2, :empType1 and :empType2 would be bound as such instances, not as strings. That way we can clearly distinguish them and see that this means a "type" and not just a normal "string" (since PHP does not have any type literals, i.e. Foo.class, unfortunately).
Example Nr. 3 is not really necessary as a start and I think depends on another ticket that is about allowing binding arrays/collections to single placeholders in the query, so this is partly out of scope of this ticket alone.
Class names themselves in a DQL query could be converted to their discriminator values in conditional expressions (Nr. 1 and 4, anywhere else needed?). TYPE() itself does probably not make sense everywhere also. Example Nr. 4 mentions TYPE() in the SELECT part, but this has to be seen in the context that this would return Class<?> instances in Java. The equivalent would be ReflectionClass or ClassMetadata instances. This might be non-trivial to do since it might affect hydration. In the conditional part (WHERE), TYPE() probably converts simply to the name of the discriminator column. If we do the same in the SELECT part it would be easier but this would then return "discrimnator values", and not some sort of class instances, which may not be of that much use.
First step for implementation: Suggestion for updated EBNF.
Second step for implementation: Adjusting the Parser to recognize the new language construct (probably at least 1 new AST node needed).
Third step for implementation: Adjusting the standard SQLWalker to produce the appropriate SQL for the new language construct.
Comment created by @beberlei:
Comment created by romanb:
Hm, no, I dont like that. instanceof normally returns a boolean, not a type.
Comment created by mjh_ca:
Just for completeness - I think Roman is referring to example 3 depending on ticket DDC-170
Comment created by dennis.verspuij:
It would also be nice if
SELECT e FROM Employee e ORDER BY TYPE(e)
would order the results by discriminator value.
Guilherme is working on a first implementation. If he manages to come up with something useful in time we will include this in the 2.0 release.
Comment created by @guilhermeblanco:
I am thinking to change the original idea from:
WHERE TYPE(e) = 'User'
WHERE TYPE(e) IN (?1, ?2)
to something like:
WHERE e INSTANCE OF 'User'
WHERE e INSTANCE OF (?1, ?2)
There're a couple of reasons for that. The main one if the simplification of ComparisonExpression.
Also, it doesn't break the current semantical logic, since there's no real change on left/right expressions.
Finally, it doesn't sound too much logical to me ORDER BY TYPE, although I see the point of the user request.
So, what do you think?
this would only make sense in WHERE, ORDER BY or SELECT would read:
SELECT e INSTANCE OF, e FROM Foo e ORDER BY e INSTANCE OF
Since you only want to order by type and not specify one, i think we really need a function that returns this value.
We agreed on using the INSTANCE OF operator and limiting it to the WHERE clause for now as the other use-cases seemed too exotic. The implementation is underway by Guilherme and is very likely to be included in the 2.0 release.
Support to INSTANCE OF is now supported in DQL.
Issue was closed with resolution "Fixed"