criteria uses "inner join" instead "left join" approach by default making my query work not the way I planned
Tested and working solution:
def result = A.withCriteria{
createAlias('someObject', 'so', CriteriaSpecification.LEFT_JOIN)
createAlias('so.importantObject', 'imp', CriteriaSpecification.LEFT_JOIN)
or {
isNull('so.importantObject')
eq('imp.interestingFlag', false)
}
}
Solution update as suggested in comment:
def result = A.withCriteria{
createAlias('someObject', 'so', JoinType.LEFT_OUTER_JOIN)
createAlias('so.importantObject', 'imp', JoinType.LEFT_OUTER_JOIN)
or {
isNull('so.importantObject')
eq('imp.interestingFlag', false)
}
}
Use left join to achieve this. This should work, but I haven't tested it live.
def result = A.withCriteria{
someObject {
createAlias("importantObject", "io", CriteriaSpecification.LEFT_JOIN)
or{
isNull('importantObject') // conditional 1
eq('io.interestingFlag', false) // conditional 2
}
}
}
Eidt: based on this post: http://grails.1312388.n4.nabble.com/CriteriaBuilder-DSL-Enhancements-td4644831.html this should also work:
def result = A.withCriteria{
someObject {
isNull('importantObject') // conditional 1
importantObject(JoinType.LEFT) {
eq('interestingFlag', false) // conditional 2
}
}
}
Please post your results on both solutions.
Edit 2: This is a query that is exactly a situation you've described, but differs from your examples. You must start from here, use showSql to debug generated SQLs and fiddle with left joins.
def result = A.withCriteria{
or {
isNull('someObject')
eq('someObject.importantObject.interestingFlag', false)
}
}