Doctrine DQL, class table inheritance and access to subclass fields

Question

I have a problem with a DQL query and entity specialization.

I have an Entity called Auction, which is OneToOne relation with Item. Item is a mappedSuperclass for Film and Book. I need a query that could back a search engine, allowing the user to look for auctions with different properties AND selling items with different properties (it is the AND part that makes it challenging).

The problem is that even though Auction has an association pointing to Item as such, I need to have access to Film- and Book-specific fields. The users will specify the Item type they're looking for, but I don't see any way of using this information other than using INSTANCE 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 named bookTypes

which is false for Book, yet true for Item.

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 and FROM 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?

Answer 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.