问题:

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?

回答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.

回答2:

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

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: