1

I have a domain class Schedule with a property 'days' holding comma separated values like '2,5,6,8,9'. 

Class Schedule {
   String days
   ...
}

Schedule schedule1 = new Schedule(days :'2,5,6,8,9')
schedule1.save()

Schedule schedule2 = new Schedule(days :'1,5,9,13')
schedule2.save()

I need to get the list of the schedules having any day from the given list say [2,8,11].

Output: [schedule1]

How do I write the criteria query or HQL for the same. We can prefix & suffix the days with comma like ',2,5,6,8,9,' if that helps.

Thanks,

Amit Jain
  • 1,372
  • 8
  • 16

2 Answers2

2

Hope you have a good reason for such denormalization - otherwise it would be better to save the list to a child table.

Otherwise, querying would be complicated. Like:

  def days = [2,8,11]
  // note to check for empty days
  Schedule.withCriteria {
    days.each { day ->
      or {
        like('username', "$day,%") // starts with "$day"
        like('username', "%,$day,%")
        like('username', "%,$day") // ends with "$day"
      }
    }
  }
Victor Sergienko
  • 13,115
  • 3
  • 57
  • 91
  • Thanks Victor! I simplified it by changing days format in schedule as ",2,5,6,8,9," and then using only one like statement i.e like('days', "%,$day,%") – Amit Jain Apr 14 '11 at 05:38
0

In MySQL there is a SET datatype and FIND_IN_SET function, but I've never used that with Grails. Some databases have support for standard SQL2003 ARRAY datatype for storing arrays in a field. It's possible to map them using hibernate usertypes (which are supported in Grails).

If you are using MySQL, FIND_IN_SET query should work with the Criteria API sqlRestriction: http://grails.org/doc/latest/api/grails/orm/HibernateCriteriaBuilder.html#sqlRestriction(java.lang.String) Using SET+FIND_IN_SET makes the queries a bit more efficient than like queries if you care about performance and have a real requirement to do denormalization.

Lari Hotari
  • 5,190
  • 1
  • 36
  • 43
  • Or Oracle table clustering: http://www.frontiernet.net/~rhode/cluster.html. Or what. If we really care about efficiency, we could implement Set for any DBMS with a bit field or a String-emulated bit field. – Victor Sergienko Apr 12 '11 at 12:52
  • Thanks a lot Flare! This looks great I am going to try it out – Amit Jain Apr 14 '11 at 05:42