CODEWITHSUNDEEP
hibernategrailsgrails-ormgrails-3.1
kulsoompatel
kulsoompatel

Reputation: 230

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

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:

Upvotes: 0

Views: 1209

Answers (3)

kulsoompatel
kulsoompatel

Reputation: 230

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()

Upvotes: 0

elixir
elixir

Reputation: 1442

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.

Upvotes: 0

Dónal
Dónal

Reputation: 187499

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])

Upvotes: 3

Related Questions