Order a set of translations, according to a specific language

Assuming two classes Product and ProductTranslation. The product has a property Product.Title, which is a set of ProductTranslation. If I have two languages, e.g. 开发者_StackOverflow社区en and de in my system, the set of Product.Title would hold two entries for each product.

Is there any way to formulate a HQL which returns me a list of products, ordered by a specific language, e.g. en? Or is this kind of sorting only possible after the DB-access within memory, e.g. with Linq or any comparator?

Thx for any ideas! sl3dg3

Edit: Despite the ordering I still would like to receive the whole set of ProductTranslation.

It's not clear whether you mean ordering using a particular culture, or just simple ordering according to the matching translations. If it's the latter, that just sounds like a join. In LINQ it would be something like:

string language = "en";
var query = from product in db.Product
            join translation in db.ProductTranslation
                                  .Where(t => t.Language == language)
                 on product.Id equals translation.productId
            orderby translation.Title
            select new { Product = product, translation.Title };

I don't know what the equivalent HQL would be, but I'm sure HQL handles joins with no problems... (If you can use LINQ to NHibernate, that should handle it fine too of course.)

EDIT: Just looking at the HQL docs, I suspect you can use

from Product as product
inner join fetch product.Translations translation
where translation.language = 'en'
order by translation.title asc

I figured out a possible solution: It can be done with a double-join:

FROM Product AS product
INNER JOIN FETCH product.Translations ordered_trans
INNER JOIN FETCH product.Translations trans
WHERE ordered_trans.language = 'en'
ORDER BY ordered_trans.Title ASC