Associations
You can navigate associations in criteria queries in several ways:
    Dot notation for many-to-one relationships ONLY
    Helper methods to create inner criterias or joins: createCriteria(), joinTo()

Dot Notation Navigation

This type of navigation is the easiest but ONLY works with many-to-one associations. Let's say you have a User entity with a Role and the Role has the following properties: id, name, slug and you want to get all users that have the role slug of admin and are active. Then you could do this:
1
// Using virtual services
2
function findAllAdmins(){
3
return newCriteria()
4
.isTrue( "active" )
5
.eq( "role.slug", "admin" )
6
.list();
7
}
Copied!

Joins

You can also use the joinTo() method, which previously was called createAlias() to create joins to related associations. Let's check out the method signature first:
1
/**
2
* Join an association, assigning an alias to the joined association
3
*
4
* You can also use the following alias method : <code>joinTo()</code>
5
*
6
* @associationName The name of the association property: A dot-separated property path
7
* @alias The alias to assign to the joined association (for later reference).
8
* @joinType The hibernate join type to use, by default it uses an inner join. Available as properties: criteria.FULL_JOIN, criteria.INNER_JOIN, criteria.LEFT_JOIN
9
* @withClause The criterion to be added to the join condition (ON clause)
10
*/
11
any function joinTo(
12
required string associationName,
13
required string alias,
14
numeric joinType=this.INNER_JOIN,
15
any withClause
16
)
Copied!
The arguments can be further explained below:
    associationName : This is the name of the property on the target entity that is the association
    alias : This is the alias to assign it so you can reference it later in the criterions following it
    joinType : By default it is an inner join. The available joins are: INNER_JOIN, FULL_JOIN, LEFT_JOIN
    withClause : This is the criterion (so it's a restriction) to be added to the join condition, basically the ON clause.
1
// Using virtual services
2
function findAllAdmins(){
3
return newCriteria()
4
.isTrue( "active" )
5
.joinTo( "role", "r" )
6
.eq( "r.slug", "admin" )
7
.list();
8
}
9
10
// no join type, but withClause
11
r = newCriteria()
12
.joinTo(
13
associationName="users",
14
alias="u",
15
withClause=getRestrictions().like( "u.lastName", "M%" )
16
)
17
.list();
Copied!

Inner Criterias

The last journey to query on associations is to pivot the root entity of the criteria to an association. This means that you will create a new criteria object based on the previous criteria, but now the target entity is the one you assign. PHEW! That's a mouthful. Basically, it's a nice way to traverse into the join and stay in that entity.
This is accomplished via the createCriteria() method or the nice dynamic alias: with{entity}() method.
1
/**
2
* Create a new Criteria, "rooted" at the associated entity and using an Inner Join
3
*
4
* @associationName The name of the association property to root the restrictions with
5
* @alias The alias to use for this association property on restrictions
6
* @joinType The hibernate join type to use, by default it uses an inner join. Available as properties: criteria.FULL_JOIN, criteria.INNER_JOIN, criteria.LEFT_JOIN
7
* @withClause The criteria to use with the join
8
*/
9
any function createCriteria(
10
required string associationName,
11
string alias,
12
numeric joinType,
13
any withClause
14
)
15
16
// Dynamic Methods
17
with{AssociationName}( joinType )
Copied!
The arguments can be further explained below:
    associationName : This is the name of the property on the target entity that is the association
    alias : This is the alias to assign it so you can reference it later in the criterions following it
    joinType : By default it is an inner join. The available joins are: INNER_JOIN, FULL_JOIN, LEFT_JOIN
    withClause : This is the criterion (so it's a restriction) to be added to the join condition, basically the ON clause.
Now remember that you are rooting the criteria in this association, so you can't go back to the original entity properties.
1
var c = newCriteria("User");
2
var users = c.like("name","lui%")
3
.withAdmins().like("name","fra%")
4
.list();
Copied!
Last modified 2yr ago