Excluding Item from Result List When Querying by Many to Many Relationship

Question

I'm a fairly new user of Grails and I'm struggling to do some more complex querying with the GORM setup.

I've got two domain objects/tables, LOCATION and REASON that have a many to many mapping, managed by the LOCATION_REASON table:

Location.groovy:

class Location {
    String description
    // other fields

    static hasMany = [reasons: Reason]

    static mapping = {
        sort description:"asc"
        id generator:'native', params:[sequence:'ATTENDANCE_TRACKER.ID_SEQ']
        reasons joinTable: [name: 'LOCATION_REASON', key: 'LOCATION_ID', column: 'REASON_ID'], cascade: 'none'
    }

    static namedQueries = {
        findAllWhereReasonIsNot { reasonId ->
            reasons { ne('id', reasonId) }
        }
    }
    //Other stuff
}

Reason.groovy:

class Reason {
    String description
    //other fields

    static hasMany = [locations: Location]

    static mapping = {
        id generator:'native', params:[sequence:'ATTENDANCE_TRACKER.ID_SEQ']
        locations joinTable: [name: 'LOCATION_REASON', key: 'REASON_ID', column: 'LOCATION_ID'], cascade: 'none'
    }
    //Other stuff
}

The named query, 'findAllWhereReasonIsNot', isn't functioning like I'd like it to. I want a query that accomplishes fetching all Locations that aren't already associated with the specified reason.

In SQL terms, I want this:

select * from location where id not in(select location_id from location_reason where reason_id = :reasonId);

But what the current implementation is accomplishing looks more or less (edited to only include relevant info, and have reasonable 'select as' names) like this once the hibernate SQL is generated:

select * from 
( 
   select 
    this_.id as loc_location_id, 
    this_.description as location_description,
    reasons3_.LOCATION_ID as loc_reas_location_id, 
    reasons3_.REASON_ID as loc_reas_reason_id,
    reasons_al1_.id as reas_reason_id,    
    reasons_al1_.description as reason_description
  from location this_ 
  inner join location_reason reasons3_ on this_.id=reasons3_.LOCATION_ID 
  inner join reason reasons_al1_ on reasons3_.REASON_ID=reasons_al1_.id 
  where (reasons_al1_.id <> :reasonId) 
  order by lower(this_.description) asc 
);

This results in a list of several copies of each location, one for each reason associated with it. This prevents the resulting list from actually excluding the location associated with the original reasonId, as other reasons are associated with that location.

I did some research on Criteria and HQL, but can't get a working query to generate... Seems like Criteria and HQL have some limitations so I'm not even sure if it's possible. Does anyone know how to accomplish that simple 'not in' subquery in the named query?

Answer 1

So with much trial and error, I've got a working HQL query:

Location.findAll("from Location loc where loc not in (select l from Location l join l.reasons as r where r.id = ?) order by loc.description")

Many thanks to cfrick for getting me pointed in the right direction. I had briefly looked at HQL before, but had misunderstood some Grails documentation, thinking it only supported the 'where' clause of HQL. I also had gotten rather hung up on trying to access the join table directly

Here's what I learned:

• Grails supports HQL through the methods find(), findAll() and executeQuery(). The latter supports custom result sets, whereas the first two are tied to Domain instances
• The Groovy console is AWESOME. Didn't know it existed before, but it's an excellent way to speed up troubleshooting your programmatic queries
• HQL only works with your domain class, but has some funky syntax to allow you to access your join table record (LOCATION_REASON, in my case): "from Location l join l.reasons as r where r.id = ?"
• In order to isolate the domain instance (either the Location or the Reason, in my example) from that join record, you need to select the table name (or its alias): "select l from Location l"