Order-by in many-to-many relationships

erwinp · **Joined:** Wed Sep 21, 2005 7:16 am **Posts:** 10

Hibernate version: 3.0.2 in JBoss 4.0.2

It seems to be impossible to use order-by in many-to-many relationships. More details about this problem can already be found in topics
http://forum.hibernate.org/viewtopic.php?t=943992 and http://forum.hibernate.org/viewtopic.php?t=942035 so i'm not repeating the details.
Unfortunately in none of these topics a real answer is provided.

One of these topics points to http://forum.hibernate.org/viewtopic.php?t=926084 in which Gavin states that this just isn't supported. However this statement is over two years old so it might be out of date. The change log for Hibernate states "fixed incorrect table alias for order-by on many-to-many" for version 3.0. I assume no fixes will be done on anything that isn't supported!?

Could anyone tell me if there's a way in which i can get this to function properly?

Thanks a lot,
Erwin

jotobjects · **Joined:** Tue Dec 27, 2005 3:30 am **Posts:** 11

I have the same problem - Is it supported or not?

simonwillnauer · **Posted:** Mon Mar 13, 2006 6:13 pm

Hey,
try this:

class Parent{
Set<Child> children;
...
}

class Child{

String name;
Set <Parent> parents;
...
}

Code:

Criteria crit = session.getSession().createCriteria(Parent.class).createAlias("children","child");
crit.add(Restrictions.eq("",new Long(24))); // do your query...
cirt.addOrder(Order.asc("child.name"));
crit.list(); //or cirt.uniqueResult();

hope that helps...

simon

jt_1000 · **Posted:** Mon Mar 13, 2006 6:40 pm

order-by is order from DB
sorted refers to a java Comparator - ordering a collection in your JVM.

Perhaps folks in the previous postings were getting confused between the two.

Check out:

http://www.hibernate.org/hib_docs/v3/reference/en/pdf/hibernate_reference.pdf

page 82, 83 for how to "SORT" collections. That should allow you to do what you want, without having the feature to "order-by" the attribute on the Other Entity side.

jotobjects · **Joined:** Tue Dec 27, 2005 3:30 am **Posts:** 11

Thanks - but yes I could see already that you can use a query or a sort. These are work arounds. A query works if you use a query EVERY place you access the collection, but it does not give you the ordered iterator of the order-by attribute. The sort attribute "might" work (you have to write your own comparator that fetches the association if that is possible).

Apparently nobody seems to think you can use by the order-by attribute for a many-to-many. Too bad the documentation does not just say so.

jt_1000 · **Posted:** Tue Mar 14, 2006 8:34 pm

jotobjects wrote:

Apparently nobody seems to think you can use by the order-by attribute for a many-to-many. Too bad the documentation does not just say so.

Just to be clear - it works for the order-by specified in the intermediate join table - Not for a order-by column on the Other Entity side. Which in a lot of ways make sense. Since the "Other" Entity may be shared in other M-M relationships...etc; and you'd have a non-normalized schema.