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:
[/b]