In Hibernate 2.1.2, I am trying to make a join query such as this :
Code:
select cat from Cat as cat left join cat.colors[0] as col order by col.name
Here is the associated mapping :
Code:
<class name="Cat" table="CAT">
<id ... />
<list name="colors" table="CAT_COLOR">
<key column="CAT_ID" />
<index column="IDX" />
<many-to-many column="COLOR_ID" class="Color" />
</list>
</class>
<class name="Color" table="COLOR">
<id ... />
<property name="name" type="string" />
</class>
I have searched through the documentation and it looks like the only coherent syntax. But it does not work.
I also tried theses syntaxes :
Code:
select cat from Cat as cat left join cat.colors as col order by col[0].name
and
Code:
select cat from Cat as cat left join cat.colors as col where index(col) = 0 order by col.name
But none of these are accepted by Hibernate, the problem being obviously the indexed collection.
I would like to know if Hibernate supports this kind of queries and how to write it in HQL.
I would have expected it to translate to this SQL :
Code:
select cat from CAT as cat left join CAT_COLOR as cat_color on (cat_color.cat_id = cat.id AND cat_color.idx= 0) left join COLOR as color on cat_color.color_id = color.id order by color.name
The same query without the [0] works well but of course does not have the join on the collection index in the generated SQL.
Thanks in advance
William
Login
FAQ
Search