{"id":4453,"date":"2011-11-01T11:28:55","date_gmt":"2011-11-01T05:58:55","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=4453"},"modified":"2016-11-30T13:06:46","modified_gmt":"2016-11-30T07:36:46","slug":"criteria-query-with-left-outer-join","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/criteria-query-with-left-outer-join\/","title":{"rendered":"Criteria Query with Left Outer Join"},"content":{"rendered":"

In Grails app development<\/a>, when using criteria queries, we often find cases for Outer joins. Lets take an example. We have two domain classes with us:<\/p>\n

[java]
\nclass Blog {<\/p>\n

\tString title
\n\tString content<\/p>\n

\tstatic hasMany = [comments: Comment]<\/p>\n

static constraints = {
\n }
\n}
\n[\/java]<\/p>\n

and<\/p>\n

[java]
\nclass Comment {<\/p>\n

\tString text<\/p>\n

\tstatic belongsTo = [blog: Blog]<\/p>\n

static constraints = {
\n }
\n}
\n[\/java]<\/p>\n

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:<\/p>\n

[java]
\nList blogStats = Blog.createCriteria().list {
\n\t\tcreateAlias(‘comments’, ‘c’)
\n \tprojections {
\n \tgroupProperty(‘id’)
\n \tgroupProperty(‘title’)
\n\t\t\tcount(‘c.id’)
\n }
\n}
\n[\/java]<\/p>\n

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<\/a>. The createAlias method can take another parameter to specify that we want the join to be a Left outer join.<\/p>\n

We’ll need to add the following import:<\/p>\n

[java]import org.hibernate.criterion.CriteriaSpecification[\/java]<\/p>\n

And the our query looks like:<\/p>\n

[java]
\nList blogStats = Blog.createCriteria().list {
\n createAlias(‘comments’, ‘c’, CriteriaSpecification.LEFT_JOIN)
\n projections {
\n\t\t groupProperty(‘id’)
\n \tgroupProperty(‘title’)
\n \t\tcount(‘c.id’)
\n }
\n}
\n[\/java]<\/p>\n

Looks pretty simple now that I know it \ud83d\ude42 How about you? Hope it helps.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":48},"categories":[7],"tags":[4840,4005,4264],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/4453"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=4453"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/4453\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=4453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=4453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=4453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}