Join the map and refer to its key / value in HQL

Question

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 HQL meaning "select entities where map identifier = =: foo and map value ==: bar"?

I can join it using select album from Album album join album.externalIds ids But how can I then refer to the key and the value of the identifiers? ids.key.id =: foo and ids.value =: bar does not work, and hibernate doc is silent about this.

Naive approaches that did not 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 

Answer 1

I believe your request should look like this:

 select album from Album album where album.externalIds['foo'] = 'bar' 

Hope this helps,

Vincent Giger