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 = { } }
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
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