Grails GORM conditional left outer join

Question

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!

Answer 1

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

}