HQL question

unkyaku · **Joined:** Wed Oct 01, 2003 11:01 pm **Posts:** 23

If I have a class Foo that has a collection of Bars with a mapping like:

Code:

<class name="Foo" table="Foo">
  <id name="id" column="fooId" />
  <set name="bars" table="FooBarAssoc">
    <key column="fooId" />
    <many-to-many column="barId" class="Bar" />
  </set>
  <property name="owner" column="owner" />
</class>

<class name="Bar" table="Bar">
  <id name="id" column="barId" />
  <property name="name" column="name" />
</class>

What would the HQL equivalent of the following SQL?

Code:

select bar.* from foo, bar, foobarAssoc
where foo.owner='me'
and foobarAssoc.fooId = foo.fooId
and bar.barId = foobarAssoc.barId

The best I can come up with is:

Code:

select bar from bar, foo
where foo.owner='me' and bar in elements(foo.bars)

Unfortunately, this produces a subselect. Is there a way to avoid the subselect?

In general, when using many-to-many mappings, is there a way to use the association table directly in HQL queries?

Thanks.

emmanuel · **Posted:** Mon Jun 21, 2004 3:55 am

1. consider setting a bidirectional many-to-many
2.

Code:

select bar from Foo foo join foo.bars bar where foo.name=..

[/quote]