When you learned how to specify GORM query restrictions, the equivalent of the SQL WHERE clause, we only queried a single domain class. In this article you'll learn how to join multiple domain classes into your queries.

GORM for SQLaddicts

Welcome to the GORM for SQLaddicts series. A collection of articles dedicated to experienced SQL programmers who want to learn how to query GORM, the Grails object-relational mapper.

Throughout this series you will learn how to

  1. WHERE - Add restrictions to a query
  2. FROM - Work with associations and joins
  3. SELECT - Return multiple properties and/or domain classes

Getting started

To follow along you'll need Grails 3.0.5 and Mercurial. With Grails and Mercurial installed you can download the source code and run the Grails console.

Setup

1
2
3
$ hg clone ssh://hg@bitbucket.org/erosa/gormtut
$ cd gormtut 
$ grails console

Once the Grails console window appears, copy & paste the following code into the Grails console.

1
2
3
4
5
6
7
import com.emmanuelrosa.gormtut.*

Vendor.withNewSession { session ->
    // Run GORM queries within this Closure.
    
    
}

Now you're set up to execute GORM queries. All you need to do is write the query within the Closure and run the code (Script->Run or Command-R).

Domain classes

The examples in this article reference the Vendor, Address, Customer, PurchaseOrder, and Product domain classes. See the diagram below to see how the domain classes are related to each other.

Domain class diagram

The GORM for SQLAddicts series is not for the lazy. You gotta get your fingers cramped to get the best out of it. If your behind is not tingling, fast asleep, you've been getting too much exercise. Sit back down, get the Grails console back up, and lets get to it.

SQL vs GORM joins

In SQL, when you need to reference more that one table, you specify the other table(s) and how they are to be joined.

1
2
3
SELECT * 
FROM   TABLE_A INNER JOIN TABLE_B
       ON TABLE_A.FOREIGN_KEY = TABLE_B.ID

GORM is different.

GORM automatically inner joins domain classes according to their associations. A GORM query can alter the join type (left, right, or Cartesian) but cannot join un-associated domain classes. Lets explore this with an inner join using HQL.

An example

Let's say you want to get all the Vendors in the state of Michigan. The first step is to check which associations you have available in the domain classes involved. Here's the source code.

Vendor.groovy
1
2
3
4
5
6
7
8
9
class Vendor {
    String name
    Address address

    static hasMany = [products: Product]

    static constraints = {
    }
}
Address.groovy
1
2
3
4
5
6
7
8
9
10
11
class Address {
    String street 
    String city
    String state
    String zip
    String country

    static constraints = {
        state nullable: true 
    }
}

The Vendor.address association provides a many-to-one association from Vendor to Address. This means you can join Address and Vendor and then get to the state property via the Address. But before you can create the join you need to create an alias.

Create an alias

The inner join syntax is:

INNER JOIN domainClassAlias.associationProperty AS alias

So the first thing you need is a domain class alias: an alias for the domain class containing the association property. The Vendor domain class contains the address association property, so you need an alias for Vendor. Just like SQL, in HQL an alias is created with AS.

FROM Vendor AS vnd

That was easy.

Staples, Inc

Now that you have the vnd alias for Vendor you're ready to create the join.

Create the join

Using the vnd alias let's create an inner join.

FROM Vendor AS vnd INNER JOIN vnd.address AS addr

Go ahead and run the query in the Grails console. The output looks like this:

1
2
3
4
5
6
7
8
[
    [com.emmanuelrosa.gormtut.Vendor : 1, com.emmanuelrosa.gormtut.Address : 1], 
    [com.emmanuelrosa.gormtut.Vendor : 2, com.emmanuelrosa.gormtut.Address : 2], 
    [com.emmanuelrosa.gormtut.Vendor : 3, com.emmanuelrosa.gormtut.Address : 3], 
    [com.emmanuelrosa.gormtut.Vendor : 4, com.emmanuelrosa.gormtut.Address : 4], 
    [com.emmanuelrosa.gormtut.Vendor : 5, com.emmanuelrosa.gormtut.Address : 5], 
    [com.emmanuelrosa.gormtut.Vendor : 6, com.emmanuelrosa.gormtut.Address : 6]
]

Interestingly, the query returns a list of lists containing the Vendor and Address. You may have expected a list of Vendors. The remedy is the topic of another article but I'll go ahead and introduce it since I'd be helpful right now: You need to add a select clause.

SELECT vnd FROM Vendor AS vnd INNER JOIN vnd.address AS addr

Just select the vnd alias and you'll get a list of Vendors.

1
2
3
4
5
6
7
8
[
    com.emmanuelrosa.gormtut.Vendor : 1, 
    com.emmanuelrosa.gormtut.Vendor : 2, 
    com.emmanuelrosa.gormtut.Vendor : 3, 
    com.emmanuelrosa.gormtut.Vendor : 4, 
    com.emmanuelrosa.gormtut.Vendor : 5, 
    com.emmanuelrosa.gormtut.Vendor : 6
]

Because it makes sense to do so, although it's beyond the scope of this article I'll go ahead and continue using the select clause in the simplest way possible for the remainder of this article.

Unlike SQL, there's no need to specify which fields to join on. In fact, HQL doesn't support the ON clause. Instead, you simply specify which association to use for the join. GORM does the rest. The domain class joined by the association is then given an alias. In this example, addr. Now that you have the join created you can finally add the restriction with a where clause.

Create the restriction

To create the restriction on the state of Michigan, use the addr alias to access the state property.

SELECT vnd FROM Vendor AS vnd INNER JOIN vnd.address AS addr WHERE addr.state = 'MI'

Where and criteria queries perform the join implicitly, but in HQL you need to explicitly join the domain classes to create aliases. Speaking of where and criteria queries...

Inner join

Now that you know how to create an inner join in HQL, let's do the same with where and criteria queries. Dynamic finders don’t support joins so they're not discussed in this article. Where queries are up first.

Where query

To create a restriction on a property of an association, simply use Groovy's property access.

1
2
3
Vendor.where {
    address.state == 'MI'
}.list()

In the query above, the state property of the address association is used in a restriction. And of course, the address association is in the Vendor domain class. Notice that unlike HQL, there's no need to create an alias. You simply use the association's property name. Let's see the equivalent criteria query.

Criteria query

To create a restriction on a property of an association, you need to...

  1. Call a method with the same name as the association.
  2. Provide a Closure as method's argument.
  3. Within the Closure use any of the association's properties.
1
2
3
4
5
Vendor.withCriteria() {
    address {
        eq('state', 'MI')
    }
}

In the query shown above, the address(Closure) method informs the HibernateCriteriaBuilder that properties accessed within the Closure should be looked up in the Vendor's Address. The HibernateCriteriaBuilder.eq(String, Object) method called within the Closure will use the state property of the Address when creating the restriction.

The where and criteria queries you just learned about don't show any indication of joins, unlike the HQL query. That's about to change. Left outer join is next.

Left outer join

To demonstrate a left outer join lets query for Customers lacking a PurchaseOrder. Here are the domain classes involved.

Customer.groovy
1
2
3
4
5
6
7
8
9
10
11
class Customer {
    String name
    Address address
    String contact
    String email

    static hasMany = [orders: PurchaseOrder]

    static constraints = {
    }
}
PurchaseOrder.groovy
1
2
3
4
5
6
7
8
9
class PurchaseOrder {
    Date orderDate
    Customer customer

    static hasMany = [items: Item]

    static constraints = {
    }
}

To find the Customers without a PurchaseOrder you'll use the Customer.orders association. Lets do this in HQL first.

HQL query

The HQL query will likely bore you.

SELECT cust FROM Customer AS cust LEFT OUTER JOIN cust.orders AS ord WHERE ord = null

When you run the query you'll see there's only one qualifying Customer.

1
[com.emmanuelrosa.gormtut.Customer : 2]

By default, where and criteria queries use an inner join. However, the join type of a criteria query can be changed to a left outer join. Unfortunately, the join type of a where query cannot be changed, so this is as far as we can get with where queries.

Criteria query

First, import the Hibernate join type you intend to use. I'm going to import all of them with the following statement:

import static org.hibernate.sql.JoinType.*

Next, create an alias for the orders association while specifying the join type.

1
2
3
4
5
import static org.hibernate.sql.JoinType.*

Customer.withCriteria {
    createAlias('orders', 'ord', LEFT_OUTER_JOIN)
}

An alias is created with HibernateCriteriaBuilder.createAlias(String associationPath, String alias, int joinType). And finally, use the ord alias with `HibernateCriteriaBuilder.isNull(String) to create the is null restriction.

1
2
3
4
5
6
import static org.hibernate.sql.JoinType.*

Customer.withCriteria {
    createAlias('orders', 'ord', LEFT_OUTER_JOIN)
    isNull('ord.id')
}

Bonus: Lets cheat

The null check type of query used in the example is quite common in SQL. GORM offers another way to achieve the same thing. By looking for Customers with an orders collection of size 0, you can get the same result without the left outer join.

HQL
FROM Customer WHERE size(orders) = 0
Where
1
2
3
Customer.where {
    orders.size() == 0
}.list()
Criteria
1
2
3
Customer.withCriteria {
    sizeEq('orders', 0)
}

Up next is the uncommon but sometimes useful Cartesian product/cross join.

Cartesian/cross join

For a cross join (Cartesian product) you’re down to HQL. It's unsupported by where and criteria queries. Similar to SQL, you simply need to list the domain classes separated by a comma:

1
FROM Vendor AS vnd, Product AS prd'

It's that simple.

Association direction

Because GORM joins domain classes based on their associations, the associations impact how you can write your queries. Something to consider is the direction of your associations. Lets say there’s a unidirectional many-to-one association from Product to Vendor.

Product.groovy
1
2
3
4
5
6
7
8
9
10
class Product {
    String name
    String description
    BigDecimal price

    static belongsTo = [vendor: Vendor]

    static constraints = {
    }
}
Vendor.groovy
1
2
3
4
5
6
7
class Vendor {
    String name
    Address address

    static constraints = {
    }
}

To get a list of Vendors who don’t have Products, you’d have to use a right outer join like this:

1
2
3
SELECT vnd 
FROM Product AS prd RIGHT OUTER JOIN prd.vendor AS vnd 
WHERE prd.id is null'

It’s possible to do a right outer join with a criteria query, but you’d have to use a projection to return a Vendor property, such as its id.

1
2
3
4
5
6
7
8
9
10
11
import static org.hibernate.sql.JoinType.*

Product.withCriteria {
    createAlias 'vendor', 'vnd', RIGHT_OUTER_JOIN
    
    projections {
        property 'vnd.id'
    }
    
    isNull 'id'
}

Alternatively, a bidirectional association simplifies the queries. For instance, with a one-to-many association from Vendor to Product...

Vendor.groovy
1
2
3
4
5
class Vendor {
    ...
    static hasMany = [products: Product]
    ...
}

You get the option of using a where query.

HQL
FROM Vendor WHERE size(products) = 0'
Where
1
2
3
Vendor.where {
    products.size() == 0
}.list()
Criteria
1
2
3
Vendor.withCriteria {
    sizeEq('products', 0)
}

Conclusion

In this article you learned about joining domain classes in GORM queries, and you even learned a tiny little bit about the HQL select clause. Need a refresher on the where clause? Want to learn about select'ing with GORM queries? There's an article for that.