Grails: Complex Many-To-Many GORM Query

Question

I have 5 tables:

User (Mark)
Enterprise (Google)
EnterpriseUser (Mark,  Google)
EnterpriseRight (LOGIN)
EnterpriseUserRight (Mark-Google, LOGIN)

I would like to use GORM to query the database for all enterprises that Mark (Current User) has LOGIN rights to. I'd then like to be able to sort and paginate the results.

I previously did it by building a List, but I'd refer to return a ResultSet (which can still be sorted / paginated / queried)

My User.class (domain object) attempt (generating a List<Enterprise>)

public List<Enterprise>getEnterprises() {
    def list = new ArrayList<Enterprise>()
    for (EnterpriseUser eu: this.enterpriseUsers) {
        if (this.hasEnterpriseRights(eu.enterprise, [EnterpriseRight.LOGIN])) {
            list.add(eu.enterprise)
        }
    }
    return list
}

(but I can't sort and paginate these results because they're now a list. Would be nice if this query could return a resultset)

In English:

Find all Enterprise associated to this User where this user has the EnterpriseRight with the String name "LOGIN". List in Alphabetical Order, Give me 10 results from Offset 10.

I'd post my code here, but I have no idea where to start.

Domain Objects Are:

class User {

    static hasMany = [userSystemRights: UserSystemRight, enterpriseUsers: EnterpriseUser]

    String email;   
    String passwordHash;
}    

class Enterprise {

    static hasMany = [enterpriseUsers: EnterpriseUser, measurements: Measurement]

    String name = ""
    String tradingName = ""
}

class EnterpriseUser {

    static belongsTo = [enterprise: Enterprise, user: User]
    static hasMany = [enterpriseUserRights: EnterpriseUserRight]

    Enterprise enterprise
    User user
    String interest
}

class EnterpriseUserRight {

    static belongsTo = [enterpriseUser: EnterpriseUser, enterpriseRight: EnterpriseRight]

    EnterpriseUser enterpriseUser
    EnterpriseRight enterpriseRight

}

class EnterpriseRight {

    public static final String LOGIN = "LOGIN"
    public static final String MANAGE_USERS = "MANAGE_USERS"
    public static final String MANAGE_SETTINGS = "MANAGE_SETTINGS"

    static hasMany = [enterpriseUserRights: EnterpriseUserRight]    

    String name
}

Answer 1

I don't know if Grails also has a special DSL for queries, but in HQL, you would do:

select distinct enterprise from EnterpriseUserRight enterpriseUserRight
inner join enterpriseUserRight.enterpriseRight enterpriseRight
inner join enterpriseUserRight.enterpriseUser enterpriseUser
inner join enterpriseUser.enterprise enterprise
where enterpriseRight.name = 'LOGIN'
and enterpriseUser.user = :user
order by enterprise.name