Discriminator not used in SQL for one-to-many mapping in ColdFusion ORM

Question

Let's say I have the following entities:

Store:

component
{
    property name="Id" fieldtype="id" generator="native";
    property name="Name";
    property name="Fruits" fieldtype="one-to-many" cfc="Fruit" inverse="true" cascade="all-delete-orphan";
    property name="Vegetables" fieldtype="one-to-many" cfc="Vegetable" inverse="true" cascade="all-delete-orphan";
}

Product.cfc:

component table="Product" discriminatorcolumn="ProductType"
{
    property name="Id" fieldtype="id" generator="native";
    property name="Name";
    property name="Price";
    property name="Store" fieldtype="many-to-one" cfc="Store";
}

Fruit.cfc:

component table="Product" extends="Product" discriminatorvalue="Fruit"
{
    property name="HasVitaminC" type="boolean";
}

Vegetable.cfc:

component table="Product" extends="Product" discriminatorvalue="Vegetable"
{
    property name="IsGreen" type="boolean";
}

In other words, a Store contains a collection of Fruits and Vegetables, and Fruits and Vegetables extend the base component Product. I'm using table-per-hierarchy mapping with a discriminator column for the Products.

The issue I'm running into now is when I populate a store and try to get the Fruits or Vegetables collection:

var store = EntityLoadByPK("Store", 13);
var fruits = store.getFruits();

The SQL that it runs behind the scenes to grab fruits is:

select * from Product where store_id = 13;

As you can see, the discriminator is not used, and I get both fruits and vegetables. When I try to iterate through this collection, I will receive an error because a Vegetable is a type mismatch with Fruit.

However, if I try the inverse way where I start with Fruit and filter by a Store:

var store = EntityLoadByPK("Store", 13);
var fruits = EntityLoad("Fruit", {store=store});

It will run the correct SQL:

select * from Product where ProductType = 'Fruit' and store_id = 13;

My question is, what am I doing wrong here that allows the ORM to work correctly when I filter the fruits by store, but not correctly when I try to get the fruits collection for a store? Or have I stumbled across a bug?

Edit: Here is the CFM file I'm executing to reproduce this behavior:

<cfscript>
    ormReload();

    // create a new store
    store = EntityNew("Store");
    store.setName("Grocery Store");

    // create a new fruit and add it to the store
    fruit = EntityNew("Fruit");
    fruit.setName("Banana");
    fruit.setStore(store);
    store.addFruits(fruit);

    // create a new vegetable and add it to the store
    vegetable = EntityNew("Vegetable");
    vegetable.setName("Asparagus");
    vegetable.setStore(store);
    store.addVegetables(vegetable);

    // save the store
    EntitySave(store);

    // flush and clear out the session
    ormFlush();
    ormClearSession();

    // load the store and get its fruits collection, this will execute the
    // wrong SQL and return TWO items
    store = EntityLoad("Store")[1];
    fruits = store.getFruits();
    writedump(fruits);

    ormClearSession();

    // load the store and filter fruits by it, this will execute the
    // correct SQL and return only one item
    store = EntityLoad("Store")[1];
    fruits = EntityLoad("Fruit", {store=store});
    writedump(fruits);
</cfscript>

Answer 1

The problem is that you have a single mapped association : the many-to-one association between a product and a store. The two one-to-many associations (store-vegetables and store-fruits) are both the inverse association of the single product-store association, which is not valid.

I'm not sure of the best solution, though. Moreover, I know what I can do in Java, but not in coldfusion. Look at adding a where clause or a filter to your collection mapping (see http://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/annotations/Where.html and http://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/annotations/Filter.html).

Or simply have a single store-products one to many association, and add two custom methods filtering the collection of products.