CODEWITHSUNDEEP
grailsgrails-orm
Brian
Brian

Reputation: 555

Grails GORM conditional left outer join

Say I have the following domain objects:

class Top {  
  User createdBy  
  Other other  
  static hasMany = [  
    children: Child,  
  ]  
}

class Child {
  User createdBy
  static belongsTo = [
    top: Top,
  ]
}

And given User myUser and Other myOther, I want to select all the Tops where

top.other.id = myOther.id AND
join all children if top.createdBy = myUser OR
if top.createdBy != myUser, join only the children where child.createdBy = myUser (or maybe no children)

Here's what I tried (amongst many other failed variations):

Top.createCriteria().list{
  eq('other', myOther)
  createAlias('children', 'c', JoinType.LEFT_OUTER_JOIN)
  children {
    or {
      isNull('c')
      eq('createdBy', myUser)
      and {
        ne('createdBy', myUser)
        eq('c.createdBy', myUser)
      }
    }
  }
}

But this fails with "org.hibernate.QueryException: duplicate association path: children" and a useless stack trace.

Any hints? Thanks in advance!

Upvotes: 0

Views: 1476

Answers (2)

Satish
Satish

Reputation: 61

For conditional inner join with Grails 3

import org.hibernate.criterion.Restrictions

...
...

Top.createCriteria().list{   
    eq('other', myOther)   
    createAlias('children', 'c', JoinType.LEFT_OUTER_JOIN, Restrictions.or(Restrictions.eq("c.createdBy", myUser))   

    ne('createdBy', myUser)

}

This will add condition at criteria level, and generate sql nearby identical to below one.

Select
...
...

left outer join
        child ch1_ 
            on top_.id=ch1_.id 
            and (
                ch1_.createdBy=? 
            ) 
where
 top_.createdBy <> ?

Upvotes: 0

Sudhir N
Sudhir N

Reputation: 4096

You can avoid the "duplicate association path" error by doing the left join as shown below. and remove the alias

children(CriteriaSpecification.LEFT_JOIN) {
    or {
     ........
  }

Not tested but try this

Top.createCriteria().list{
  eq('other', myOther)
  or {
    eq "createdBy", myUser
    children(CriteriaSpecification.LEFT_JOIN) {
        eq "createdBy", myUser
    }
  }

}

Upvotes: 2

Related Questions