[Quick 4.2.4] Can Discriminated Entities Originate from Same Table?

Communities Quick ORM
1

I am experimenting with discriminated entities to create a Media entity and MediaImage entity.

All media is stored in a single table, media, regardless of whether it is a MediaImage or not. Therefore, I don’t need to join based on a foreign key. I was hoping to be able to just use the discriminatorValue="image" meta attribute to define the parent/child relationship. Quick requires the joinColumn attribute so I have left it blank since no join is needed.

The SQL that gets generated throws a database error because it can’t complete the join statement. Here’s what the query looks like:

SELECT 
  TOP (1) [media].[altText], 
  [media].[id], 
  [media].[type], 
  ... // rest of columns
  [media].[type]
FROM 
  [media] 
  LEFT OUTER JOIN [media] ON [media].[id] = 
WHERE 
  ([media].[id] = ?)

Here is the code for the entities:

// Media.cfc
component
    extends="quick.models.BaseEntity"
    accessors="true"
    table="media"
    discriminatorColumn="type"
{

    property name="id";
    property name="type";
    
    variables._discriminators = [
        "ImageMediaQ@cms"
    ];

}

// ImageMedia.cfc
component
    extends="MediaQ"
    accessors="true"
    table="media"
    discriminatorValue="image"
    joinColumn=""
{

    property name="altText" default="";

}

How can I set up these discriminated entities in Quick so they originate from the same database table?

2

Update: I believe I was lacking the correct terminology in my original post. I’ll try to add some clarification here for anyone interested.

Quick’s discriminated entities feature follows the multiple table inheritance (MTI) pattern, which lets subclasses share common data from a parent table/class but distinct data for the subclass comes from a separate table defined by the value in a column from the primary table called the “discriminator column”. For example, a Media base class might have MediaBook and MediaAlbum subclasses distinguished based on the discriminator column, type. In MTI, the database schema for this example might look like this:
image

The MediaBook subclass entity would have all of the properties from the media_book table as well as the media table.

In my original example, I was looking to use a different pattern called single table inheritance (STI). STI is similar to MTI, except instead of distributing subclass data across multiple tables, all of the subclass data comes from a single table like this:
image

Both MTI and STI are popular recognized design patterns and each method has its pros and cons. Here’s a blog post that describes the differences between each in more detail.

Sometimes we don’t have control over the database schema we are working with so Quick should be flexible enough to support both MTI and STI. I created a pull request to add this capability to the library. Hopefully, it will be introduced into the main branch soon.