Criteria Builder - Filtering on entity relationships

I currently have a many-to-many relationship of Businesses and their associated Applications they use.

SetupBusiness

`

component persistent=“true” table=“Setup_Business” schema=“dbo” output=“false”
{

property name=“Business” column=“Business” type=“string” ormtype=“string”;
property name=“BusinessID” column=“BusinessID” type=“numeric” ormtype=“int” fieldtype=“id”;

property name=“Applications” fieldtype=“many-to-many” cfc=“SetupApp” linktable=“Setup_BusinessApp” inversejoincolumn=“AppID”;
}

`

SetupApp

`

component persistent=“true” table=“Setup_App” schema=“dbo” output=“false”
{
property name=“AppID” column=“AppID” type=“numeric” ormtype=“int” fieldtype=“id”;
property name=“Application” column=“Application” type=“string” ormtype=“string” fieldtype=“column”;
property name=“Active” column=“Active” type=“numeric” ormtype=“boolean”;
}

`

My goal is to list all the active business, with all their active applications. By criteria is as follows:

`

var c = BusinessService.newCriteria();
var result = c.isEq(“BusinessID”, c.convertValueToJavaType(“BusinessID”, 3)).isTrue(“BusinessActive”).list();

`

This accurately returns an Array of 1 item (SetupBusiness) with a property “Applications”, which is an array of all the applications that business is using. So 1 business, 99 applications.

Now I wanted to filter out only the active applications for that business, which should be 98. I have tried the following:

`

var c = BusinessService.newCriteria();
var result = c.isEq(“BusinessID”, c.convertValueToJavaType(“BusinessID”, 3)).isTrue(“BusinessActive”).createAlias(“Applications”, “a”).isTrue(“a.Active”).list();

`

Now, my result is an Array of 98 SetupBusiness items, each containing 99 Applications (including the one that is inactive). My expected result is 1 SetupBusiness item, with 98 Applications. What am I over-looking?

Here is the logged SQL, which is correct:

`

SELECT this_.businessid AS businessid902_1_
, this_.business AS business902_1_
, this_.businessactive AS business3_902_1_
, applicatio3_.businessid AS businessid902_
, a1_.appid AS appid
, a1_.appid AS appid904_0_
, a1_.application AS applicat2_904_0_
, a1_.active AS active904_0_
FROM
dbo.setup_business this_ INNER JOIN setup_businessapp applicatio3_ ON this_.businessid = applicatio3_.businessid
INNER JOIN dbo.setup_app a1_ ON applicatio3_.appid = a1_.appid
WHERE this_.businessid = 3
AND this_.businessactive = 1
AND a1_.active = 1;

`

So I guess maybe this has to do more about my entity mapping instead of criteria builder? My goal is for the result to be 1 SetupBusiness entity, with 98 SetupApp entities in the Applications property rather than what I am currently getting (which is 98 SetupBusiness entities). Hopefully that makes sense…

Tristan

Download a trial of databoss www.ortussolutions.com/products/databoss

That will tell you if your entities and relationships are correct or not.

signature0.jpg

Luis F. Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

Thanks, Luis. I got DataBoss up and running and I am willing to bet much of my problem has to do with how these tables are related (or lack thereof) on the schema. SetupBusiness.BusinessID (PK) joins on SetupBusinessApp.BusinessID (though not defined as a FK), and SetupApp.AppID needs to join on SetupBusinessApp.AppID (also not a FK). However, SetupApp.Application is the actual PK for that table, not the AppID column. So when using DataBoss, Hibernate is unable to determine the DDL. If I remove the many-to-many relationship from the entity, then everything loads fine (just not related).

Turn DataBoss logging as well it will spit out your relationship data for devugging

Thanks for the helpful tips. I spent way too many hours on this, but I think I got a solution I can live with. I created the SetupBusiness and SetupApp entities with no relationship to each other, like so:

SetupBusiness

`

component persistent=“true” table=“Setup_Business” schema=“dbo” output=“false”
{
property name=“Business” column=“Business” type=“string” ormtype=“string”;
property name=“BusinessID” column=“BusinessID” type=“numeric” ormtype=“int” fieldtype=“id”;
property name=“IsActive” column=“BusinessActive” type=“numeric” ormtype=“boolean”;

}

`

SetupApp

`

component persistent=“true” table=“Setup_App” schema=“dbo” output=“false”
{
property name=“AppID” column=“AppID” type=“numeric” ormtype=“int” fieldtype=“id”;
property name=“Application” column=“Application” type=“string” ormtype=“string” fieldtype=“column”;
property name=“IsActive” column=“Active” type=“numeric” ormtype=“boolean”;

}

`

Then I created a SetupBusinessApp entity only for the link table, that lists the many-to-many relationships between SetupBusiness and SetupApp:

`

component persistent=“true” table=“Setup_BusinessApp” schema=“dbo” output=“false”
{
property name=“BusinessAppID” column=“BusinessAppID” type=“numeric” ormtype=“int” fieldtype=“id”;
property name=“AppID” column=“AppID” type=“numeric” ormtype=“int” insert=“false” update=“false”;
property name=“BusinessID” column=“BusinessID” type=“numeric” ormtype=“int” insert=“false” update=“false”;
property name=“SetupBusiness” fieldtype=“many-to-one” cfc=“SetupBusiness” fkcolumn=“BusinessID”;
property name=“SetupApp” fieldtype=“many-to-one” cfc=“SetupApp” fkcolumn=“AppID”;
}

`

Now I can individually build criteria on these without getting overly-complex results back. I am sure there is a way to have all of these entities related and get the results back as I expected. However, at this point I feel that is beyond my experience as I have exhausted a lot of effort. :slight_smile:

`

function listApps(event, rc, prc) {
var c = appService.newCriteria();
var result = c.isTrue(“IsActive”).list();
}

function listBusinesses(event, rc, prc) {
var c = businessService.newCriteria();
var result = c.isTrue(“IsActive”).list();
}

function listAppsByBusiness(event, rc, prc) {
var c = businessAppService.newCriteria();
var result = c.isEq(“BusinessID”, c.convertValueToJavaType(“BusinessID”, 3))
.createAlias(“SetupBusiness”, “b”, c.INNER_JOIN)
.isTrue(“b.IsActive”)
.createAlias(“SetupApp”, “a”, c.INNER_JOIN)
.isTrue(“a.IsActive”)
.list();
}

`