Grails - Need to restrict fetched rows based on condition on join table

Question

Hi guys,

I have these two domains Car and Driver which have many-to-many relationship. This association is defined in table tblCarsDrivers which has, not surprisingly, primary keys of both the tables BUT additionally also has another boolean field deleted. Herein lies the problem. When I find/get query on domain Car, I am fetched all related drivers irrespective of their deleted status in tblCarsDrivers, which is expected.

I need to put a clause/constraint to exclude the deleted associations from the list of fetched records.

PS: I tried using an association domain CarDriver in joinTable name but that seems not to work. Apparently it expects only table names, not maps.

PPS: I know its unnatural to have any other fields besides the mapping keys in mapping table but this is how I got it and it cant be changed.

Car domain is defined as such -

class Car {
    Integer id
    String name

    static hasMany = [drivers:Driver]

    static mapping = {
        table 'tblCars'
        version false
        drivers joinTable:[name: 'tblCarsDrivers',column:'driverid',key:'carid']
    }
}

Thanks!

Answer 1

I know its unnatural to have any other fields besides the mapping keys in mapping table but this is how I got it and it cant be changed.

This is not at all unusual. If you want to store properties about the relationship, this is the obvious solution. You should reinstate your association domain CarDriver which has a deleted property in addition to a relationship to Car and Driver, and you should then be able to write a query which excludes the deleted drivers.

A comprehensive example of how to define such a mapping is provided here.