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?