Getting the latest 10 registered users - Spring security Grails

Question

I need to get the last 10 registered users (normal users) in my application for statistics. The application has two roles: normal user and administrator user.

In my User class (Spring security), I have the dateCreated field and I can obtain the last 10 registered users in my controller with this query:

User.listOrderByDateCreated(max: 10, order: 'desc')

But I just want to get it between normal users, excluding administrator. With this query, I can obtain all normal users:

UserRole.findAllByRole(role).user

What query have I to run? Thanks.

Answer 1

Surprisingly this is a tricky one, because User doesn't have a direct handle on Role, so the GORM helpers don't help as much. Using straight Groovy list manipulation we can get what you want.

def users = User.list().findAll { it.authorities.contains role }
.sort { it.dateCreated }
.reverse()
.take(10)

//or…

def users = UserRole.findAllByRole(role).user
.sort { it.dateCreated }
.reverse()
.take(10)

However, if you have a large number of users this would be an inefficient way to get 10 of them. A better option may be to use Hibernate criteria:

def users =  UserRole.createCriteria().list(max: 2) { 
  eq "role", role
  user { 
    order 'dateCreated', 'desc'
  }
  projections { property 'user' } 
}

Or if you want, you can query using HQL via executeQuery():

def users = User.executeQuery("from User where id in (select user.id from UserRole where role.id = :roleId) order by dateCreated desc", [roleId: role.id], [max: 10])