GORM 'or' criteria issue

Question

or {
   userTerm {
      and {
        le('startDate', endDate)
        ge('endDate', startDate)
      }
   }
   schoolTerm {
      and {
         le('startDate', endDate)
         ge('endDate', startDate)
      }
   }
}

'or' condition should return the result either from userTerm or schoolTerm object but it does not return the expected result.

I checked individual condition and that worked perfectly fine but when I add 'or' condition with both userTerm and schoolTerm then it returns empty list.

This used to work fine in grails 1.3.7 but fails in 2.2.4 What can be the issue? Please help.

post you domain classes and provide information in whilch list you want this. — user1791574, Nov 07 '13 at 09:23
Mind, from Grails 2.0 associations are fetched using inner join where as for earlier versions outer join was used. You might be facing the case where you would need outer join. Have a look at this [question](http://stackoverflow.com/questions/19390720/grails-2-x-createcriteria-or-doesnt-work-for-nested-associations) — dmahapatro, Nov 07 '13 at 13:37
but outer join selects all the rows irrespective of above condition matched or not... I need rows which will satisfy either schoolTerm or userTerm conditions. — user1298426, Nov 07 '13 at 16:48

score 0 · Answer 1 · answered Jul 03 '14 at 14:37

I have been solving this using left join, after upgrading a 1.3.7 project to 2.2.x. It's very annoying for a large project. I'm still investigating to see if there's an alternative.

or {
   userTerm(CriteriaSpecification.LEFT_JOIN) {
      and {
        le('startDate', endDate)
        ge('endDate', startDate)
      }
   }
   schoolTerm(CriteriaSpecification.LEFT_JOIN) {
      and {
         le('startDate', endDate)
         ge('endDate', startDate)
      }
   }
}

GORM 'or' criteria issue

1 Answers1