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! 🙂