coldfusion ORM: many-to-many conditional property

Question

I am trying to add a where clause to a many-to-many property I have defined in one of my objects. I must be doing something wrong though because I keep getting hibernate errors saying that the column doesn't exist.

in the template cfc:

<cfproperty
    name="Settings"
    fieldtype="many-to-many"
    cfc="Setting"
    linktable="settings_templates"
    fkcolumn="templateID"
    inversejoincolumn="settingsId"
    where="deleted='false'"
>

In the settings cfc:

<cfproperty
    name="templates"
    fieldtype="many-to-many"
    cfc="Template"
    linktable="settings_templates"
    fkcolumn="settingsId"
    inversejoincolumn="templateID"
    where="deleted='false'"
>

The error I am getting is:

08/02 16:06:27 [jrpp-170] HIBERNATE ERROR - [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'deleted'.

Can anyone see what I am doing wrong? there is a deleted column in both tables, but not in the link table.

Answer 1

The where property behavior for many-to-many is very strange...

In order to debug this, activate the Hibernate logging is primordial. Refer you to this post: http://www.rupeshk.org/blog/index.php/2009/07/coldfusion-orm-how-to-log-sql/

Take this example:

Article.cfc

/**
 * @output false
 * @persistent true
 * @table article
 */
component {

    property name="id" fieldtype="id";
    property name="title";

    property 
        name="tags" singularname="tag" 
        fieldtype="many-to-many" cfc="Tag" linktable="link_article_tag" fkcolumn="articleId" 
        inversejoincolumn="tagId" where=" deleted = 0 "
    ;

}

Tag.cfc

/**
 * @output false
 * @persistent true
 * @table tag
 */
component {

    property name="id" fieldtype="id";
    property name="name";
    property name="deleted" dbdefault="0";

    property 
        name="articles" singularname="article" 
        fieldtype="many-to-many" cfc="Article" linktable="link_article_tag" fkcolumn="tagId" 
        inversejoincolumn="articleId"
    ;

}

When I try to list all articles like this ormExecuteQuery('from Article'), see what is appened in the hibernate logs:

select
    tags0_.articleId as articleId6_1_,
    tags0_.tagId as tagId1_,
    tag1_.id as id0_0_,
    tag1_.name as name0_0_,
    tag1_.deleted as deleted0_0_ 
from
    link_article_tag tags0_ 
inner join
    tag tag1_ 
        on tags0_.tagId=tag1_.id 
where
    tags0_.deleted = 0 

Damned! WTF :| tags0_ == join table ... You see what's wrong?

In order to understand this behavior, I'm going to activate HBMXML generation in Application.cfc with this.ormSettings.saveMapping = true Here is the files:

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class entity-name="Tag" lazy="true"
        name="cfc:www.app.models.test.Tag" table="tag">
        <id name="id" type="string">
            <column length="255" name="id"/>
        </id>
        <property name="name" type="string">
            <column name="name"/>
        </property>
        <property name="deleted" type="string">
            <column default="0" name="deleted"/>
        </property>
        <bag name="articles" table="link_article_tag">
            <key column="tagId"/>
            <many-to-many class="cfc:www.app.models.test.Article" column="articleId"/>
        </bag>
    </class>
</hibernate-mapping>

What we can see: The where attribute is set at the bag level, not at the to many-to-many. If I edit like this:

    <bag name="tags" table="link_article_tag">
        <key column="articleId"/>
        <many-to-many class="cfc:www.app.models.test.Tag" column="tagId" where=" deleted = 0 "/>
    </bag>

The generated SQL become:

select
    tags0_.articleId as articleId62_1_,
    tags0_.tagId as tagId1_,
    tag1_.id as id59_0_,
    tag1_.name as name59_0_,
    tag1_.deleted as deleted59_0_ 
from
    link_article_tag tags0_ 
inner join
    tag tag1_ 
        on tags0_.tagId=tag1_.id 
where
    tag1_.deleted = 0 

This method works but is not good for code maintenance and readability. If anyone has a better solution, I'm interested.

Answer 2

So far, the only way around this that I have found is to override the getter using ORMExecuteQuery(). There I can query the objects directly and look for things such as this. Personally, I prefer to work in cfscript, so my code would look something like this:

public array function getSettings() {
    return ORMExecuteQuery("SELECT s FROM template t JOIN t.settings s WHERE t.id=:id AND s.deleted=:deleted", {
        deleted=false, id=this.getId()
    });
}

N.B. – EXAMPLE NOT TESTED

Of course, you don't have to use the parameters as I have, you could just use deleted=false, but that's up to you (I like using parameters, personally).

Also, don't forget the singularName attribute on many-to-many properties.

On a related note, I've had issues using properties on both objects. You really only need to use them on the template object, in this case. Think about it this way: A template needs to know its settings, but the settings don't need to know about the template they belong to – using properties on both objects in this way can lead to errors, in some cases.