How to write ORM statements to return filtered data from data objects?

well. I've just started using orm in the ColdFusion application It has been going well until now I encountered this obstacle I have two tables:

This is the code I use to load data into the page The second part of if is the default load, and the first part is the part used to filter the list to specific categories

<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>

Cfelse query returns, which is exactly what I need:

The cfif query returns this problem because there are two child nodes in each parent array

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

Solution

The HQL you are running is selecting product_ spec_ Cats and products_ spec_ cat_ prod_ cat_ Link entity because you have not defined what 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#

This query is basically the same as select * from... In ordinary SQL queries What you want to do is:

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 an inner join. You can write a query like this:

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

Finally, by the way, I recommend that you use query parameters, especially when you paste the contents of the form range into the 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 });