{"id":4453,"date":"2011-11-01T11:28:55","date_gmt":"2011-11-01T05:58:55","guid":{"rendered":"https:\/\/2thenew.online\/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:\/\/2thenew.online\/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":68,"footnotes":""},"categories":[7],"tags":[4840,4005,4264],"class_list":["post-4453","post","type-post","status-publish","format-standard","hentry","category-grails","tag-grails","tag-grails-development","tag-groovy-and-grails"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t