How to use subselect HQL in a Hibernate filter condition?

Question

I want to use Hibernate's filters, to filter "MyEntity" objects, using a suselect of "AnotherEntity" in the filter condition. The configuration looks something like this:

<hibernate-mapping>

  <filter-def name="myFilter" condition="someProperty in (select x.property1 from AnotherEntity x where property2 = :property2)">
    <filter-param name="property2" type="long"/>
  </filter-def>

  <class name="com.example.MyEntity" table="SOME_TABLE">
    <id name="OID" column="O_ID" type="long">
      <generator class="hilo">
        <param name="table">oid_id</param>
        <param name="column">next_id</param>
      </generator>
    </id>
    <version name="hibernateVersion" column="hibernate_version" unsaved-value="negative"/>
    <property name="someProperty"/>
    <filter name="myFilter"/>
  </class>

  <class name="com.example.AnotherEntity" table="ANOTHER_TABLE">
    <composite-id>
      <key-many-to-one name="property1" ... />
      <key-many-to-one name="property2" ... />
    </composite-id>
  </class>

</hibernate-mapping>

Which gives me an org.hibernate.exception.SQLGrammarException: could not execute query respectively an SQLException Table "ANOTHERENTITY" not found, since the generated SQL statement contains "AnotherEntity" instead of the mapped table "ANOTHER_TABLE" as if the mapping were not found. However, when I just execute the subselect

select x.property1 from AnotherEntity x where property2 = :property2

it just works fine.

What do I miss here? Is my config wrong? Can I use Suselect HQL in filters?

Answer 1

As it turns out, filter conditions don't support HQL in all its glory. You have to use SQL or more precisely write a WHERE clause fragment. That means you have to use table and column names rather than entity and property names.

However, you can use named placeholders just fine. Sticking to the example in the question you would write your condition like this:

<filter-def name="myFilter" condition="SOME_COLUMN in (select x.COLUMN_X from ANOTHER_TABLE x where COLUMN_Y = :foo)">
    <filter-param name="foo" type="long"/>
  </filter-def>

It seems like this condition is attached to the query after the HQL got translated to SQL but before placeholder replacement is done. At least for version 3.x of Hibernate.