CreateCriteria - Use logical OR in hasMany association

Question

I want to get instances who contain in their lists (firstsList or SecondsList) a specific user.
In my solution, the create criteria takes into account only the first list of users. It seems to be a bad usage of the logical OR

Domain

class ClassA {
    static hasMany =  [firstsList:User,SecondsList:User]
}

Service

def idList =  ClassA.createCriteria().list () {

projections { distinct ( "id" )
    property("name")
    property("id")
}

or {                 
   firstsList{eq("login", 'John')}             
   SecondsList{eq("login", 'John')}                                 
 }

order("name","desc")

}

return idList

score 1 · Accepted Answer · answered Oct 01 '15 at 10:07

The reason behind this is hibernate by default uses inner join. But in your case you need left join. For that you can use createAlias of createCriteria.

    def idList = ClassA.createCriteria().list() {
        projections { 
            distinct("id")
            property("name")               
        }

        createAlias("firstsList", "fl", JoinType.LEFT_OUTER_JOIN)
        createAlias("SecondsList", "sl", JoinType.LEFT_OUTER_JOIN)
        or {                 
            eq("fl.login", "John")             
            eq("sl.login", "John")                                
        }

        order("name", "desc")
    }

CreateCriteria - Use logical OR in hasMany association

1 Answers1