This article in the GORM for SQLaddicts series describes how to choose which domain class properties to return in your GORM query. It's the equivalent of the SQL select clause.

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, and Product domain classes. See the diagram below to see how the domain classes are related to each other.

Domain class diagram

Throughout this series I've dedicated each article to a single SQL clause: (see the where and from articles). And as you may have noticed, it's impossible to use only one of these SQL clauses in isolation. The same applies to GORM. Both previous articles utilize the select clause in some way. Lets learn about the details.

SELECTing properties

In the where clause article I said not to be concerned with the select clause just yet. Now the time has come to figure this thing out. Say you want to get a list of all the Vendor names. In SQL you can accomplish this with the following query:

SELECT name FROM vendor

Thus far, your queries return domain class instances. As an example, you could list all of the Vendor names by getting all of the Vendor instances and then using Groovy to grab the name properties:

Vendor.findAll()*.name

But lets face it, you can do better. Enough with the suspense. Here's the HQL:

SELECT name FROM Vendor

The HQL is straight-forward: it returns the name property of all the Vendor domain class instances. The output looks like this:

['Bears R Us', 'Bears Emporium', 'Doll House Inc.', 'Furball Inc.', 'Fun and Games', 'Jouets et ours']

Lets add another property:

SELECT name, address FROM Vendor

The output looks like this:

1
2
3
4
5
6
7
8
[
    ['Bears R Us', com.emmanuelrosa.gormtut.Address : 1],
    ['Bears Emporium', com.emmanuelrosa.gormtut.Address : 2],
    ['Doll House Inc.', com.emmanuelrosa.gormtut.Address : 3],
    ['Furball Inc.', com.emmanuelrosa.gormtut.Address : 4],
    ['Fun and Games', com.emmanuelrosa.gormtut.Address : 5],
    ['Jouets et ours', com.emmanuelrosa.gormtut.Address : 6]
]

The output is rows of Lists, with each List containing two elements: the Vendor name and Address. Interesting huh? How about returning an Address property, such as country, instead?

SELECT name, address.country FROM Vendor

The output of which is:

1
2
3
4
5
6
7
8
[
    ['Bears R Us', 'USA'],
    ['Bears Emporium', 'USA'],
    ['Doll House Inc.', 'USA'],
    ['Furball Inc.', 'USA'],
    ['Fun and Games', 'England'],
    ['Jouets et ours', 'France']
]

Cool! Look how easy that was! Lets try one more thing: add the Vendor.products property.

SELECT name, address.country, products FROM Vendor

That didn't go so well. The issue is that Vendor has a one-to-many relationship to Product through the products association. This means the products property is a collection and thus requires a different approach. I'll come back to this. Lets do some criteria and where queries.

Criteria

The equivalent of a select clause in criteria (and where) queries is called a projection. Yea! Yes, I'm talking about those mysterious projections you've seen plastered all over Stack Overflow.

Once you see a projection in action, it will click.

1
2
3
4
5
6
println Vendor.withCriteria {
   projections {
       property('name')
       property('address')           
   }
}

Notice how each selected property corresponds to a call to HibernateCriteriaBuilder.property(String propertyName). Mystery solved

What's with the println? The problem is this: The Address instances are lazy-loaded when the query executes: you get a proxy rather than actual Address instances. The proxy then retrieves the data when you try to access it, as will happen when the Grails console attempts to print the result. The println forces the proxy to resolve within the withNewSession(Closure) Closure preventing this:

For clarity, I'll exclude the println from the remaining examples. Lets replicate the HQL which returns the Vendor.name and Vendor.address.country properties.

1
2
3
4
5
6
7
8
9
Vendor.withCriteria {
   projections {
       property('name')
       
       address {
           property('country')
       }          
   }
}

Ah, this is different. To access a nested property:

  1. call a method named like the first property (ex. address(Closure)).
  2. Within the Closure call property(String) with a property name (ex. country) relative to the property represented by the method call (ex. address).

It takes some getting used to, but it's not so bad. So, what can a where query do for you?

Where query

Projections work differently in where queries. Up first are the name and address properties.

1
2
3
4
5
6
Vendor
    .where {}
   .projections {
       property('name')
       property('address')
   }.list()

I modified the where query syntax I normally use to clarify the fact that in where queries the projections are specified outside of the GormEntity.where(Closure) Closure. Otherwise, the projections Closure is the same as the one used in the equivalent criteria query. But, there's a caveat. Lets see what happens when you try to project address.country.

1
2
3
4
5
6
7
8
9
Vendor
    .where {}
   .projections {
       property('name')
       
       address {
           property('country')
       }          
   }.list()

It doesn't work. What a downer. In short, projections are just better supported by criteria queries.

Earlier on you tried selecting/projecting the Vendor.products property and got a nasty error. Lets discover what you can do to handle such Collection properties.

SELECTing collections

Since you know SQL, the easiest way to learn how to deal with Collections is to look at what the output looks like:

1
2
3
4
5
6
7
8
9
10
11
[
    ['Bears R Us', com.emmanuelrosa.gormtut.Product : 1],
    ['Bears R Us', com.emmanuelrosa.gormtut.Product : 2],
    ['Bears R Us', com.emmanuelrosa.gormtut.Product : 3],
    ['Doll House Inc.', com.emmanuelrosa.gormtut.Product : 4],
    ['Doll House Inc.', com.emmanuelrosa.gormtut.Product : 5],
    ['Doll House Inc.', com.emmanuelrosa.gormtut.Product : 6],
    ['Doll House Inc.', com.emmanuelrosa.gormtut.Product : 7],
    ['Fun and Games', com.emmanuelrosa.gormtut.Product : 8],
    ['Fun and Games', com.emmanuelrosa.gormtut.Product : 9]
]

Keeping in mind that a Vendor has a one-to-many relationship with Product, you can see in the table-like output above what looks like an... inner join! Do you see it? OK, let me show you the query that produced that output.

SELECT vnd.name, product FROM Vendor AS vnd INNER JOIN vnd.products AS product

By joining the Product domain class through the existing Vendor.products association each related Product becomes available in the query. Note that to select the Vendor.name the domain class alias (ex vnd) must be used. Joining is covered thoroughly in the from clause article.

Lets build on this and select a Product property.

SELECT vnd.name, product.name FROM Vendor AS vnd INNER JOIN vnd.products AS product

Complimentary output:

1
2
3
4
5
6
7
8
9
10
11
[
    ['Bears R Us', '8 inch teddy bear'],
    ['Bears R Us', '12 inch teddy bear'],
    ['Bears R Us', '18 inch teddy bear'],
    ['Doll House Inc.', 'Fish bean bag toy'],
    ['Doll House Inc.', 'Bird bean bag toy'],
    ['Doll House Inc.', 'Rabbit bean bag toy'],
    ['Doll House Inc.', 'Raggedy Ann'],
    ['Fun and Games', 'King doll'],
    ['Fun and Games', 'Queen doll']
]

Yep, it's that easy. Moving on...

Criteria query

I'll tell you right off the bat that you cannot project the associated domain classes like you can with HQL. For instance, neither one of these queries will work:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// This won't work.
Vendor.withCriteria {
   projections {
       property('name')
       property('products')           
   }
}

// Neither will this.
Vendor.withCriteria {
   createAlias('products', 'product')
   
   projections {
       property('name')
       property('product')           
   }
}

But, you can project an association's property. This works beautifully:

1
2
3
4
5
6
7
8
9
Vendor.withCriteria {
   projections {
       property('name')
       
       products {
           property('name')
       }
   }
}

Since where query projections do not support nested properties, I'm closing the curtain.

Conclusion

I hope you've enjoyed the GORM for SQLaddicts series so far. In this article you learned how to select properties, and association properties using HQL's select clause and criteria and where query projections. I hope you've enjoyed this series of articles.