How to retrieve list with max, offset and sort parameters with Grails ORM

Question

I have a grails application with User and Group domain objects. The User has many Group objects and the Group object contains many User objects:

class User implements Serializable {

    static constraints = {
        usergroups nullable: true
    }

    static mapping = {
        usergroups cascade: 'all-delete-orphan'
    }

    static hasMany = [
        usergroups: Group
    ]

    static mappedBy = [
        usergroups : "creator"
    ]
}

class Group {

    static belongsTo = [
        creator : User
    ]

    static hasMany = [
        members : User
    ]

    static constraints = {
        creator nullable: false
        members nullable: true, maxSize: 100
    }
}

Given a Group object, can I retrieve the members with max, offset and sortBy parameters? Something like...

def members = User.where {
   /* how to specify only the users in 'group.members'? */
}.list(
  max: max, 
  offset: offset, 
  sortBy : sortBy
);

Edit

To try and fix the problem I have altered the User class to contain a joinedgroups field...

class User implements Serializable {

    static constraints = {
        usergroups nullable: true
        joinedgroups nullable: true
    }

    static mapping = {
        usergroups cascade: 'all-delete-orphan'
    }

    static hasMany = [
        usergroups: Group
        joinedgroups: Group
    ]

    static mappedBy = [
        usergroups : "creator",
        joinedgroups : "creator" // if I don't add this Grails complains there is no owner defined between domain classes User and Group. 
    ]
}

But now when I try to retrieve all the User's usergroup objects in another part of my application only a single usergroup is returned...

    def groups = Group.where {
        creator.id == user.id
    }.list(max: max, offset: offset, sort: sortBy); // should return 3 groups but now only returns 1

This query worked before so maybe adding the extra mappedby entry in User has caused the problem. Is the new mappedby field in User incorrect?

Answer 1

If all the groups that contains a user are saved in the usergroups field, you could use:

def query = User.where {
    usergroups { id == myGroup.id }
}

def users = query.list(max: 10, offset: 0, sort : "id")