Doctrine DQL, class table inheritance and access to subclass fields
Solution 1
As Matt stated, this is an old issue that Doctrine Project won't fix (DDC-16).
The problem is that doctrine's DQL is a statically typed language that comes with a certain amount of complexity in its internals.
We thought about allowing upcasting a couple of times, but the effort to get that working is simply not worth it, and people would simply abuse the syntax doing very dangerous things.
As stated on DDC-16, it is also indeed not possible to understand which class the property belongs to without incurring in nasty problems such as multiple subclasses defining same properties with different column names.
If you want to filter data in subclasses in a CTI or JTI, you may use the technique that I've described at https://stackoverflow.com/a/14854067/347063 . That couples your DQL with all involved subclasses.
The DQL you would need in your case is most probably (assuming that Entities\Book
is a subclass of Entities\Item
):
SELECT
a
FROM
Entities\Auction a
INNER JOIN
a.item i
INNER JOIN
i.bookTypes b
WHERE
i.id IN (
SELECT
b.id
FROM
Entities\Book b
WHERE
b.type = 'Fantasy'
)
That is the pseudo-code for your problem. It is not nice, but keep in mind that SQL and DQL are very different and follow different rules.
Solution 2
Updated:
I've discovered a solution for this. See my answer for this related question:
Doctrine2: Polymorphic Queries: Searching on properties of subclasses
Solution 3
You can easily solve this by left-joining your base entity with your inheritance class using the id:
SELECT a FROM Entities\Auction a
INNER JOIN a.item i
INNER JOIN Entities\Book b WITH b.id = i.id
INNER JOIN b.bookTypes bt
WHERE bt.type = 'Fantasy'
AND...
or with a queryBuilder:
$queryBuilderb->select('a')
->from('Entities\Auction', 'a')
->innerJoin('a.item', 'i')
->innerJoin('Entities\Book', 'b', 'WITH', 'b.id = i.id')
->innerJoin('b.bookTypes', 'bt')
->where('bt.type = :type')
->andWhere(...
->setParameter('type', 'Fantasy');
This is based on the answer given by Ian Philips in the question here
Solution 4
The Doctrine team has stated that they're not going to add support for this:
https://github.com/doctrine/orm/issues/2237
Pertinent comments from that page:
Thats indeed tricky. That syntax alone can, however, never work, because there might be several subclasses that have a field named "d", so Doctrine would not know which field you mean.
I am closing this one.
The requirement of this issue is basically violating OO principles.
If you really need to filter across multiple child-entities in your inheritance, then try something as following instead:
SELECT r FROM Root r WHERE r.id IN ( SELECT c.id FROM Child c WHERE c.field = :value )
crizzis
Updated on July 08, 2022Comments
-
crizzis almost 2 years
I have a problem with a DQL query and entity specialization.
I have an Entity called
Auction
, which isOneToOne
relation withItem
.Item
is amappedSuperclass
forFilm
andBook
. I need a query that could back a search engine, allowing the user to look for auctions with different propertiesAND
selling items with different properties (it is theAND
part that makes it challenging).The problem is that even though
Auction
has an association pointing toItem
as such, I need to have access toFilm
- andBook
-specific fields. The users will specify theItem
type they're looking for, but I don't see any way of using this information other than usingINSTANCE OF
in my DQL query.So far, I have tried using a query like:
SELECT a FROM Entities\Auction a INNER JOIN a.item i INNER JOIN i.bookTypes b WHERE i INSTANCE OF Entities\Book AND b.type = 'Fantasy' AND ...".
Such a query results in an error saying that:
Class
Entities\Item
has no field or association namedbookTypes
which is false for
Book
, yet true forItem
.I have also tried
SELECT a FROM Entities\Book i INNER JOIN i.auction a ...
but I reckon Doctrine requires that I refer to the same Entity in
SELECT
andFROM
statements.If that's of importance, I am using class table inheritance. Still, I don't think switching to single table inheritance would do the trick.
Any ideas?
-
crizzis over 12 yearsMerging results is not exactly the case here, since I never need Films and Books to appear in the results of my query at the same time. The workaround I've finally come up with was to add the inverse side of the Auction-Item relationship and query for Items rather than Auctions ('SELECT i FROM Entities\Book INNER JOIN i.auction a WHERE (...)'). Since I know which subclass I'm looking for, I can build my query accordingly. Thanks for the reply, though, your idea could be a good way to go, as it seems there's little point in using inheritance with Doctrine in cases like this one.
-
Ocramius over 10 yearsThat is a quite bad idea and it's more of a hack - you don't need to break your models to fix the DQL. As a last resort, you can always use native SQL at the price of maintaining some queries more.