Criteria Query with Left Outer Join

01 / Nov / 2011 by Himanshu Seth 7 comments

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.

FOUND THIS USEFUL? SHARE IT

comments (7)

  1. akash

    CriteriaSpecification.LEFT_JOIN has become deprecated. We can use JoinType.LEFT_OUTER_JOIN for recent versions.

    Reply
  2. Himanshu Seth

    @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:

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *