Ordering a many-to-many

Here's a quick post on ordering by properties in a many-to-many relationship, using HQL. I'm working on a site at the moment where the developer has set up a many-to-many to mapping† between a Post and a Category.

In order to be able to sort by properties you may think that you'd write this in HQL:

posts = ORMExecuteQuery( "
  select c.posts
  from Category c 
  where c = :Category 
  order by c.posts.published desc ", 
  { Category=rc.Category }
);

You run that you'll get an error along the lines of illegal attempt to dereference collection. Instead you want to do this

posts = ORMExecuteQuery( "
  select p
  from Category c join c.posts p 
  order by p.published desc ", 
  { Category=rc.Category }
);

† I personally don't use the many-to-many mapping that ColdFusion ORM supports as I don't find it very flexible, which means you may need to change it later. Instead I prefer to create an intermediate entity called PostCategory and then do Post - o2m - PostCategory - m2o - Category.

• Posted in:
• ColdFusion
• Hibernate

2 comments

1. Can you describe some of the limitations with many-to-many?
   
   Comment by Rory – September 15, 2012
2. @Rory, if you imagine that you've got a "Post - m2m - Category" relationship. The relationship works fine. However, at a later date you decide that you want to track who created the relationship and / or when it was added. With a m2m you have no place to store that information. You need a new entity to manage that data. You you end up replacing the "Post - m2m - Category" with "Category - o2m - PostCategory - m2o - Post" in your model which could require a lot of work. By using the intermediary entity from the start you can easily add properties and behaviour with minimal effort.
   
   I hope I've explained that OK!
   
   Comment by John Whish – September 18, 2012