0

I have a many-to-many relationship between two objects with a join table. I need to be able to select 5 random children based on Parent and date, excluding some children records. I am stuck. Any ideas?

   Parent {
      static hasMany = [children: Child]
    }

    Child {
      Date dob
      static belongsTo = [Parent]
      static hasMany = [parents: Parent]

      static namedQueries {

        randomFiveChildrenBornAfter { parentid, dob, excludeChildren->

          qt 'dob', dob            
          parents {
            eq 'id',parentid
          }
          // not in(excludeChildren) ?? order by rand() ??
        } 
      } 
   }
Micor
  • 1,502
  • 4
  • 20
  • 39

2 Answers2

2

Are these literally Parent/Child relationships (as in humans)? If so, the set of children is likely to be very small and I'd probably just do it in memory rather than through a sql query.

parent.children
    .findAll { dob >= fiveYearsAgo }
    .sort { Math.random() }
    .with { it.size() >= 5 ? it.subList(0,5) : it }
Ted Naleid
  • 26,511
  • 10
  • 70
  • 81
  • Thanks Ted for your response. Unfortunately its not literal and a parent object can have thousands of children so it will get rather expensive doing it in memory... Any way to do it with a query using criterias? – Micor Jun 30 '10 at 04:58
  • In that case, you'd have to do it in the database. Unfortunately, there isn't a database agnostic way of getting a random row. See this stackoverflow post for details on how to write an HQL criteria that gets random rows with a limit: http://stackoverflow.com/questions/2810693/hibernate-criteria-api-get-n-random-rows – Ted Naleid Jul 01 '10 at 00:35
0

The best workaround if you want to use the withCriteria method is this:

User.withCriteria{
eq 'name', 'joseph'
sqlRestriction " order by rand()"
}

It's important to say that sometime ( depends on the Criteria query created ) it's necessary to add a " 1=1 " in sqlRestriction cause it add an "and" condition in generated query. So if you have a sqle exception use:

sqlRestriction " 1=1 order by rand()"
Giuseppe Iacobucci
  • 423
  • 2
  • 14