Join map and refer to its key/value in HQL
- by alamar
Suppose I have a map:
<map name="externalIds" table="album_external_ids">
<key column="album_id" not-null="true"/>
<map-key-many-to-many class="Major" column="major_id"/>
<element column="external_id" type="string" not-null="true"/>
</map>
How do I make a HQL meaning "select entities where map key's id == :foo and map value == :bar"?
I can join it using select album from Album album join album.externalIds ids
But how would I then refer to ids' key and value?
ids.key.id = :foo and ids.value = :bar doesn't work, and hibernate doc is silent on this topic.
Naive approaches that didn't work:
select album from Album album join album.externalIds externalId
where index(externalId).id = :foo and externalId = :bar
and
select album from Album album join album.externalIds externalId join index(externalId) major
where major.id = :foo and externalId = :bar
