PHP doctrine 1.2 ORM - polymorphic queries with class table inheritance

Question

I'm experimenting with the Doctrine ORM (v1.2) for PHP. I have defined a class "liquor", with two child classes "gin" and "whiskey". I am using concrete inheritance (class table inheritance in most literature) to map the classes to three seperate database tables.

I am attempting to execute the following:

$liquor_table = Doctrine_Core::getTable('liquor');
$liquors = $liquor_table->findAll();

Initially, I expected $liquors to be a Doctrine_Collection containing all liquors, whether they be whiskey or gin. But when I execute the code, I get a empty collection, despite having several rows in the whiskey and gin database tables. Based on the generated SQL, I understand why: the ORM is querying the "liquor" table, and not the whiskey/gin tables where the actual data is stored.

Note that the code works perfectly when I switch the inheritance type to column aggregation (simple table inheritance).

What's the best way to obtain a Doctrine_Collection containing all liquors?

Update

After some more research, it looks like I'm expecting Doctrine to be performing a SQL UNION operation behind the scenes to combine the result sets from the "whiskey" and "gin" tables.

This is known as a polymorphic query.

According to this ticket, this functionality is not available in Doctrine 1.x. It is destined for the 2.0 release. (also see Doctrine 2.0 docs for CTI).

So in light of this information, what would be the cleanest, most efficient way to work around this deficiency? Switch to single table inheritance? Perform two DQL queries and manually merge the resulting Doctrine_Collections?

Answer 1

I wrote the (not production ready) beginnings of an ORM that would do exactly what you're looking for a while back. Just so that I could have a proof of concept. All my studies did yield that you're in some way mixing code and data (subclass information in the liquor table).

So what you might do is write a method on your liquor class/table class that queries it's own table. The best way to get away with not having to hard-code all the subclasses in your liquor class is to have a column which contains the class name of the subclass in it.

How you spread the details around is entirely up to you. I think the most normalized (and anyone can correct me if I'm wrong here) way to do it is to store all fields that appear in your liquor class in the liquor table. Then, for each subclass, have a table that stores the specific data that pertains to the subclass type. Which is the point at which you are mixing code and data because your code is reading the liquor table to get the name of the subclass to perform a join.

I'll use cars & bikes and some minimal, yet trivial differences between them for my example:

Ride
----
id
name
type

(1, 'Sebring', 'Car')
(2, 'My Bike', 'Bicycle')

Bicycle
-------
id
bike_chain_length

(2, '2 feet')

Car
---
id
engine_size

(1, '6 cylinders')

There's all kinds of variations from here forward like storing all liquor class data in the subclass table and only storing references and subclass names in the liquor table. I like this the least though because if you are aggregating the common data, it saves you from having to query every subclass table for the common fields.

Hope this helps!

Answer 2

Hi,

the only stable and useful inheritence mode of Doctrine for the moment is column_aggregation. I have tried the others in different projects. With column_aggregation you can imitate polymorphic queries. Inheritance in general is something that is a bit buggy in Doctrine (1.x). With 2.x this will change, so we may have better options in the future.