HQL joins in Grails: Part Deux

Question

This is an extension of the question I asked here

I have a relationship like this

class Foo {
    static hasMany = [bars: Bar]
}

class Bar {
    // Has nothing to tie it back to Foo or Thing
}

class Thing {
    static hasMany = [bars: Bar]
}

I have an instance of Thing. I want to get all instances of Foo that are associated with all instances of Bar that are associated with the instance of Thing that I have.

Is what I want possible via HQL (is HQL somehow aware of the indirect relationship between Thing and Foo)?

UPDATE:

Here is a picture of a possible relationship.

If I had Thing1 and I wanted all the instance of Foo that are indirectly associated with it via Bar then the solution I need would return Foo1 and Foo2

Answer 1

Would something like this work ?

Foo.withCriteria {
    bars {
        in('id', thing.bars.collect { it.id })
    }
}

Answer 2

Something like this should work:

select foo from Foo foo
where not exists (select thingBar.id from Thing thing left join thing.bars thingBar
                  where thing.id = :thingId
                  and thingBar.id not in (select fooBar.id from Foo foo2
                                          left join foo2.bars fooBar
                                          where foo2.id = foo.id))

EDIT: Now that you've explained what you wanted with a nice picture, it's simpler. In fact what you want is all the Foos which are linked to at least one bar (and not all the bars) also linked to the Thing. The query would thus be:

select foo from Foo foo
inner join foo.bars fooBar
where fooBar.id in (select thingBar.id from Thing thing inner join thing.bars thingBar)