Criteria Query with Left Outer Join
In Grails app development, when using criteria queries, we often find cases for Outer joins. Lets take an example. We have two domain classes with us:
[java]
class Blog {
String title
String content
static hasMany = [comments: Comment]
static constraints = {
}
}
[/java]
and
[java]
class Comment {
String text
static belongsTo = [blog: Blog]
static constraints = {
}
}
[/java]
Now, lets say, we want get the title of all the blogs along with the count of the comments on it. The query that we try is:
[java]
List blogStats = Blog.createCriteria().list {
createAlias(‘comments’, ‘c’)
projections {
groupProperty(‘id’)
groupProperty(‘title’)
count(‘c.id’)
}
}
[/java]
Interestingly, we do not get what we want. We do get a list of list of id, title and comments count, but only for those blogs for which comments exist. So, with creating an alias, we just created a join but what we need is a left outer. As always, stackoverflow had the answer. The createAlias method can take another parameter to specify that we want the join to be a Left outer join.
We’ll need to add the following import:
[java]import org.hibernate.criterion.CriteriaSpecification[/java]
And the our query looks like:
[java]
List blogStats = Blog.createCriteria().list {
createAlias(‘comments’, ‘c’, CriteriaSpecification.LEFT_JOIN)
projections {
groupProperty(‘id’)
groupProperty(‘title’)
count(‘c.id’)
}
}
[/java]
Looks pretty simple now that I know it 🙂 How about you? Hope it helps.
CriteriaSpecification.LEFT_JOIN has become deprecated. We can use JoinType.LEFT_OUTER_JOIN for recent versions.
@Shubham: Not sure what you mean by “Creating alias in another statement”. But if you are looking at reusing a part of the criteria, may be this helps you:
after creating “Blog.createCriteria()” can i use create alias in another statement?
Very helpful blog. Thanks a lot!
amazing it is really helpful with little control if one is not willing to use hql
Simple, neat and very useful. Thanks.
Thanks a lot! It saves my life! 🙂