Grails HQL Join Examples

Feb 08, 2015 Snippet comments

This post will show how to perform HQL Joins with one to many relationships and other cases. Joins are important when you need to check two or more tables to retrieve the required information

HQL Join - One To Many

Below are simple domain classes in one to many relationship:

package asia.grails.test
class Category {
    static hasMany = [subCategories:SubCategory]
    String name
 }

package asia.grails.test
class SubCategory {
    static belongsTo = Category
    Category category
    String name
}

This represent a hierarchical category. Assuming we insert these data:

class BootStrap {
    def init = { servletContext ->
        if ( Category.count() == 0 ) {
            Category color = new Category(name:'Color').save()
                new SubCategory(category:color, name:'Red').save()
                new SubCategory(category:color, name:'Green').save()
                new SubCategory(category:color, name:'Blue').save()
            Category shape = new Category(name:'Shape').save()
                new SubCategory(category:shape, name:'Square').save()
                new SubCategory(category:shape, name:'Circle').save()
            Category size = new Category(name:'Size').save()
                new SubCategory(category:size, name:'Small').save()
                new SubCategory(category:size, name:'Medium').save()
                new SubCategory(category:size, name:'Large').save()
        }
    }
    def destroy = {
    }
}

Here are some problems

Which category does subcategory Medium belong?

def result = Category.executeQuery(
    "select c from Category  c join c.subCategories sc where sc.name = 'Medium'")
result.each { category ->
    println "Category is ${category.name}"
}

This will print "Category is Size" if run against the data above.

How many subcategories does Size have?

def result = Category.executeQuery(
    "select count(*) from Category  c join c.subCategories sc where c.name = 'Size'")
println "${result[0]}"

This will print "3" as our data has 3 sub categories for size.

How many subcategories does each category have?

def result = Category.executeQuery(
    "select c.name, count(*) from Category  c join c.subCategories sc group by c")
result.each { item ->
    println "Category ${item[0]} has ${item[1]} sub-categories"
}

This will print:

Category Color has 3 sub-categories
Category Shape has 2 sub-categories
Category Size has 3 sub-categories

HQL Join - No Relationship

Just like in SQL, we can join any tables even when not in one-to-many relationship. For example, if we have this domain and data:

package asia.grails.test
class Person {
    String firstName
    String lastName
    int age
}

class BootStrap {
    def init = { servletContext ->
        if (Person.count()==0) {
            new Person(firstName: 'John', lastName: 'Doe', age: 10).save()
            new Person(firstName: 'Rex', lastName: 'Smith', age: 12).save()
            new Person(firstName: 'Amy', lastName: 'Johnson', age: 8).save()
        }
    }
    def destroy = {
    }
}

So we have 3 records in the database table. We can do this HQL:

def result = Person.executeQuery(
    "select count(*) from Person p1, Person p2")
println "${result[0]}"

This will print the answer "9" as 3 x 3 = 9.

This will print all the combinations.

def result = Person.executeQuery("select p1, p2 from Person p1, Person p2")
result.each { item->
    def p1 = item[0]
    def p2 = item[1]
    def p1Name = "${p1.lastName}, ${p1.lastName}"
    def p2Name = "${p2.lastName}, ${p2.lastName}"
    println "First person is ${p1Name} and second person is ${p2Name}"
}

And the output is this:

First person is Doe, Doe and second person is Doe, Doe
First person is Smith, Smith and second person is Doe, Doe
First person is Johnson, Johnson and second person is Doe, Doe
First person is Doe, Doe and second person is Smith, Smith
First person is Smith, Smith and second person is Smith, Smith
First person is Johnson, Johnson and second person is Smith, Smith
First person is Doe, Doe and second person is Johnson, Johnson
First person is Smith, Smith and second person is Johnson, Johnson
First person is Johnson, Johnson and second person is Johnson, Johnson