GORM Query on Multiple Parent and Child Objects With Parent's Property

Question

FYI, feel free to suggest a better title for this. I have the following domain model (we have no control over this):

class Foo {
    int id
    String baz
    Date someDate
    static hasMany = [bars:Bar]
    static mapping = {
        id composite: [ "id", "baz" ]
    }
}

class Bar {
    int id
    String baz
    Date someDate
    Foo foo
    static mapping = {
        id composite: ["id", "baz", "someDate"]
        columns {
            foo([:]) {
                column name: "id"
                column name: "baz"
            }
        }
    }
}

My problem is: I have a List of Foos and I need to find all Bars where Foo.someDate == Bar.someDate, but only in one query instead of a query for each Foo, with no lazy loading involved. Also setting Bars to eager fetching is not an option.

For example if this is my data (pseudocode, I'm combining id and baz into simply "id" for simplicity):

[
    Foo (someDate:4/1/2013)
        |___ bars: [{someDate:12/4/2012, id:1}, {someDate:4/1/2013, id:2}]
    Foo (someDate:5/10/2012)
        |___ bars: [{someDate:{4/1/2013, id:3}
    Foo (someDate:3/3/2013)
        |___ bars: [{someDate:3/3/2013, id:4}, {someDate:9/5/2013, id:5}]
]

I need to return the Bars with id 2 and 4 in one query. I'm really not sure how to approach this.

It could be HQL if necessary but it's preferred to be a GORM query.

Answer 1

Try this:

Bar.executeQuery("select b from Bar b join b.foo as f where b.someDate = f.someDate and f in :fooList", [fooList: fooList])