Hopefully this is an easy one. I've looked around and no one seems to be asking this question.
In a nutshell, the question is: how can you specify an order-by clause when you have an index table mapping a many-to-many relationship?
Consider the case of Documents and Authors:
Document
docid
name
Author
authid
lastName
firstName
I need to setup a many-to-many relationship, so I can do something like this:
Set authors = Document.getAuthors()
But I want the set of authors to be order by lastname. So I create a set mapping named "authors". The set mapping can take an order-by parameter, but if you try to put lastName in it, it tries to find a lastName field in the "authors" table instead of the "author" table.
I realize I could solve this with a comparator class, but mysql is capable of this simple query by doing a join like this:
select
Author.lastName
from
Document
join authors on authors.docid = Document.docid
join Author on Author.authid = authors.authid
order by Author.lastName
of course you can't put a join in the order-by clause or this would be easy.
Am I approaching this wrong? How can this be done?
thanks
here's a simplified mapping:
Code:
<hibernate-mapping>
<class
name="Author"
table="Author">
<id
name="authid"
column="authid"
type="long">
<generator class="identity"/>
</id>
<property
name="firstName"
type="java.lang.String"/>
<property
name="lastName"
type="java.lang.String"/>
</class>
<class
name="Document"
table="Document">
<id
name="docid"
column="docid"
type="long">
<generator class="identity"/>
</id>
<property
name="name"
type="java.lang.String"/>
<set
name="authors"
order-by="lastName"> <-- HERE'S THE PROBLEM
<key column="authid"/>
<many-to-many
class="Author"
outer-join="auto"/>
</set>
</class>
</hibernate-mapping>
[/b]