CODEWITHSUNDEEP
grailsgrails-orm
Hikaru Shindo
Hikaru Shindo

Reputation: 2913

Query many-to-many with createCriteria in Grails

How do I query a many-to-many by using createCriteria? Here are my models,

class Role {
    String name
    static hasMany = [users: User]
}
class User {
    String name
    String email
}

And I have 3 tables that generated by GORM in my database,

role              role_user                  user 
---------------   -------------------------  ---------------------------------
|id  |name    |   |role_users_id |user_id |  |id |name     |email            |
---------------   -------------------------  ---------------------------------
|1   |Owner   |   |1             |1       |  |1  |Harry    |[email protected]   |
|2   |Designer|   |2             |2       |  |2  |Hermione |[email protected]|
|3   |Cleaner |   |3             |3       |  |3  |Ron      |[email protected]     |
---------------   -------------------------  ---------------------------------

I want to get users who is a 'owner' and 'designer' and I have to use createCriteria because I am going to use pagination.

Upvotes: 0

Views: 287

Answers (1)

Alexander Suraphel
Alexander Suraphel

Reputation: 10613

Using your relationships it's hard to query based on the user table but you can get what you want by the following: 

List<User> users = []

Role.withCriteria { 
    or { 
       eq( "name", "Owner")
       eq( "name", "Designer") 

    }
}.each { users += it.users }

If you are willing to change your schema and add Role role to User, you can do the following: 

List<User> users = User.createCriteria().list() { 
    role { 
       eq( "name", "Owner")
       eq( "name", "Designer") 
    }
}

FYI the syntax of createCriteria is the same as withCriteria.

Upvotes: 1

Related Questions