Joins are very important when you need to retrieve data from two or more tables based on some condition. In Grails, you can use HQL queries to perform join operations. We have executeQuery to perform joins in grails.

Lets take an example to show how joins can be performed with One-to-Many relationship:

Suppose there are classes Project and Task.

class Project {
     static hasMany = [tasks:Task]
     String name
}

class Task{
        static belongsTo = Project 
       Project project
       String name
}

Assume we have inserted following data:

class BootStrap {
def init = { servletContext ->
if ( Project .count() == 0 ) {
Project prj_1= new Project (name:’Project_1′).save()
new Task(project:prj_1, name:’Task_1′).save()
new Task(project:prj_1, name:’Task_2′).save()
Project prj_2= new Project (name:’Project_2′).save()
new Task(project:prj_2, name:’Task_3′).save()
new Task (project:prj_2, name:’Task_4′).save()
Project prj_3 = new Project (name:’Project_3′).save()
new Task(project:prj_3 , name:’Task_5′).save()
new Task(project:prj_3 , name:’Task_6′).save()
}
}
def destroy = {
}
}

Here, are some problems where we need to retrieve data from both tables and we are going to use joins:

Problem 1. Which Project does Task Task_3 belong?

def result = Project.executeQuery(
“select p from Project p join p.tasks pt where pt.name = ‘Task_3′”)
result.each { project ->
println “Project is ${project.name}”
}

Output will be “Project is Project_1″

Problem 2. How many tasks does Project_3 have?

def result = Project.executeQuery(
“select count(*) from Project p join p.tasks pt where p.name = ‘Project_3′”)
println “${result[0]}”

Output will be “2” as we have 2 tasks for Project_3.

Problem 3. How many tasks does each project have?

def result = Project.executeQuery(
“select p.name, count(*) from Project p join p.tasks pt group by p”)
result.each { item ->
println “Project ${item[0]} has ${item[1]} tasks”
}

Output will be as shown below:

Project  Project_1 has 2 tasks
Project  Project_2 has 2 tasks
Project  Project_3 has 2 tasks

We can perform Join without having any relationship between tables.

---

ProsperaSoft offers Grails development solutions. You can email at info@prosperasoft.com to get in touch with ProsperaSoft Grails experts and consultants.