Querying entities which have a specific collection entry using HQL

Question

I have Item objects which have a collection of attributes defined as a map. Now I want to retrieve all objects which have a specific attribute (by name, locale and value)

An expecert of the Item mapping:

<map table="ITEM_ATTRIBUTES" name="attributes">
  <key column="item_id" foreign-key="fk_itmr_attrbts_itmrs"/>
  <composite-index class="LocalizedKey">
    <key-property name="name" column="name"/>
    <key-property name="locale" column="locale" length="32"/>
  </composite-index>
  <element column="value" type="escapedString" not-null="true"/>
</map>

This HQL (with :key as LocalizedKey and :value as String)

from Item item
where item.attributes[:key] = :value

doesn't work and produces the following output

error: composite-index appears in []

I created a workaround by using plain SQL in my query. But i'd like to know if there is a way to do this in HQL.

my plain-sql workaround:

select i.* from items i
left join item_attributes a on a.item_id = i.id
where a.name = :name
and a.locale = :locale
and a.value = :value

Answer 1

The documentation hints that the index() operator should return you the key from your map. Given that your key is a composite object, I'm not totally certain what you're going to be able to do with that in your query. I apologize for not being able to test this out before giving you the suggestion, but here's what I'd try just to see what you get:

from Item item join item.attributes attr where attr.index().name = :name 
       and attr.index().locale = :locale and attr.value = :value