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:

class Blog {

	String title
	String content

	static hasMany = [comments: Comment]

    static constraints = {
    }
}

and

class Comment {

	String text

	static belongsTo = [blog: Blog]

    static constraints = {
    }
}

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:

List blogStats = Blog.createCriteria().list {
		createAlias('comments', 'c')
            	projections {
                	groupProperty('id')
                	groupProperty('title')
			count('c.id')
            }
}

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:

import org.hibernate.criterion.CriteriaSpecification

And the our query looks like:

List blogStats = Blog.createCriteria().list {
      createAlias('comments', 'c', CriteriaSpecification.LEFT_JOIN)
      projections {
		         groupProperty('id')
                	groupProperty('title')
         		count('c.id')
      }
}

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 comment -