Grails GORM to return random rows from table?

Question

In my grails application I have:

keywords = Keyword
    .findAll("from Keyword where locale = '$locale' order by rand() ", [max:20])

Assume there are thousands of rows in the table that match the above criteria. But it seems the rows that are returned from the table are not random but in the order the rows are stored in Db although within the context of 20 rows that are returned they are random. For my application to work I want this query to return completely random rows from the table like it could be row id 203 , row id 3789, row id 9087, row id 789, and so on. How is that possible?

Answer 1

I use the following style:

Keyword.executeQuery('from Keyword order by rand()', [max: 9])

and it returns random rows from the entire table (we're using MySQL).

I'm not sure why execute query would behave differently from findAll though.

Answer 2

If you want to use a .withCriteria you can do that workaround:

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

It's important to say that sometimes ( depends on the Criteria query created ) it's necessary to add a 1=1 in sqlRestriction because it adds an "and" condition in generated query.

So if you have a sqle exception use:

sqlRestriction " 1=1 order by rand()"