How to query on a many to many relationship in Grails?

Question

I have the following Domain Classes:

class PreparedToWork {

    String location        
    static hasMany = [assessors: Assessor]        
}

class Assessor {

    //Some Properties

    static belongsTo = [PreparedToWork]     
    static hasMany = [preparedToWork: PreparedToWork]
}

I would like to retrieve all of the Assessors which are prepared to work in a certain location with only the prepared to work ID. I have my join table in the actual database so I'm certain the relationship is fine however have tried various querying options and have failed miserably.

App info:

Grails Version: 3.1.8
Groovy Version: 2.4.6
JVM Version: 1.8.0_60

score 3 · Accepted Answer · answered Jul 26 '16 at 16:50

I think the following is a much more natural way to describe your domain

class Location {

    String name        
    static hasMany = [preparedToWork: Assessor]        
}

class Assessor {    
    //Some Properties

    static belongsTo = [Location]     
    static hasMany = [preparedToWork: Location]
}

Then you can retrieve all of the assessors who are prepared to work in a certain location with

Assessor.executeQuery(
    "from Assessor a join a.preparedToWork l where l.id = ?", [locationId])

I will accept this as the answer as it is succinct and answers my question very well. — kulsoompatel, Jul 29 '16 at 14:54

score 0 · Answer 2 · answered Jul 26 '16 at 17:34

I would setup my domains a little different than what you have. Here is an example.

class Location{
   String name

   static hasMany = [assessors: Assessor]

   static  mapping = {
        assessors joinTable: [name: 'preparedToWork', key:'location_id']
   }
} 

class Assessor {    
    static belongsTo = [Location]  

    static hasMany = [locations: Location]

    static  mapping = {
        assessors joinTable: [name: 'preparedToWork', key:'assessor_id']
    }
}

Once the domains are setup like this, I expect to see 3 tables location assessor preparedToWork : composite key of location_id and assessor_id

If I need to find all the assessors for a specific location, I can use following statements.

def location = Location.where{ name == "some-location" }
def assessors = location.assessors //

Hope this helps.

It should be possible to find all the assessors for a specific location with a single query — Dónal, Jul 27 '16 at 09:14
To find all the assessors for a specific location I think you can easily query the other way. def assessors = Accessor.where{ location.name == "some-location" } // not tested but I think it should work. — elixir, Jul 27 '16 at 19:21

score 0 · Answer 3 · edited Jul 31 '16 at 00:48

0

After trying many different types of queries I used a detached query. I did try solution 1 however I was also querying on many other properties and the query below allowed me to do that.

def criteria = new DetachedCriteria(Assessor)
if (preparedToWork) {
  criteria = criteria.build {
    createAlias("preparedToWork", "p")
    eq("p.id", preparedToWork)
  }
}

criteria.list()

edited Jul 31 '16 at 00:48

Dónal

185,044
174
569
824

answered Jul 29 '16 at 14:48

kulsoompatel

220
1
3
18

Why use a `DetachedCriteria` instead of just a regular criteria query? – Dónal Jul 31 '16 at 00:48
The domain which I am querying (Assessor) contains an embedded domain class. I am also querying on a property from this class but haven't posted the code. – kulsoompatel Aug 01 '16 at 14:40

How to query on a many to many relationship in Grails?

3 Answers3