How do I write an ORM statement to return filtered data from a data object?

Question

Okay. I am just starting to use ORM in my Coldfusion applications. It has been going really well until now. I hit this snag. I have these two tables:

Here is the code that I am using to load data into the page. The second part of the if is the default load, and the first one is the one that is used to filter down the listings to a specific category.

<cfif form.filtercat neq ''>
    <cfset load = ormexecuteQuery('from product_spec_cats as cats inner join cats.product_spec_cat_prod_cat_lnk as link WHERE link.spl_prod_cat_id = #form.filtercat#',{},false)>
<cfelse>
    <cfset load = entityload('product_spec_cats')>
</cfif>

The cfelse query returns this which is exactly what I need:

The cfif query returns this which is a problem as there are two children in each parent array.

So, my question is, how do I write the HQL to return the data in the same structure as the default query and still be able to filter the data?

Answer 1

The HQL you are running is selecting both the product_spec_cats and product_spec_cat_prod_cat_link entities because you do not define what you want to select:

from product_spec_cats as cats 
inner join cats.product_spec_cat_prod_cat_lnk as link 
WHERE link.spl_prod_cat_id = #form.filtercat#

That query is essentially the same as a select * from ... in a normal SQL query. What you want to do is this:

select cats
from product_spec_cats as cats 
inner join cats.product_spec_cat_prod_cat_lnk as link 
where link.spl_prod_cat_id = #form.filtercat#

Depending on how your relationship is set up, you may not even need the inner join and you can write your query like this:

from product_spec_cats as cats    
where cats.product_spec_cat_prod_cat_lnk.spl_prod_cat_id = #form.filtercat#`

Finally, as an aside, I would recommend you use query parameters, especially when you are sticking something from the form scope into a query:

ormExecuteQuery("
    select cats
    from product_spec_cats as cats
    inner join cats.product_spec_cat_prod_cat_lnk as link
    where link.spl_prod_cat_id = :catID
", { catID = form.filtercat });