Criteria Builder Search on entity relationship

Communities ColdBox HMVC
1

Hi guys,

Well I’m back on trying to get my head round the Criteria Builder and running into a bit I just can’t get my head round.

I have 2 entities Project and Client, project has a many-to-one relationship with Client.

I want to search on multiple properties of a project. Example search terms could be:

364836
Audi A6 Brochure 2013
Audi

So I’m trying to return all projects that match either the project number, project title or the name of the client. I have the following code so far

`

var cProjects = projectService.newCriteria();
cProjects.cache(true);
cProjects.order(“projectNumber”, “desc”);
cProjects.or(
cProjects.restrictions.ilike(“projectNumber”, “#rc.string#%”),
cProjects.restrictions.ilike(“projectTitle”, “#rc.string#%”)
);
var projects = cProjects.list(max=100, asQuery=false);

`

I can’t figure out how to add the criteria for the search term to be like the client name into the disjunction.

`
cProjects.createCriteria(“company”).ilike(“companyName”, JavaCast( “string”, “#rc.string#%”));

`

Any help would be great.

Thanks,
Richard

2

Hi Richard–

You can use createAlias() to add what fundamentally becomes a join in SQL to the client entity.

For example:

cProjects = projectService.newCriteria();

// add alias (join)

cProjects.createAlias( “Client”, “cli” );

// now that we have the alias, we can use it elsewhere for criteria

cProjects.or(

cProjects.restrictions.ilike( “projectNumber”, “#rc.string#%” ),

cProjects.restrictions.ilike( “projectTitle”, “#rc.string#%” ),

cProjects.restrictions.ilike( “cli.ClientNameProperty”, “#rc.string#%” )

);

Just be sure to use the alias that you set up (“cli”, or whatever you choose) when referencing the relationship’s property name.

Good luck!

3

For more detailed info, check out the Associations section in the CriteriaBuilder docs: http://wiki.coldbox.org/wiki/ORM:CriteriaBuilder.cfm

4

Wow nice, that works like a charm. I love criteria builder its pretty awesome.

Thanks Joel.

Richard

5

I love it too! :slight_smile:

Glad it did the trick!