Ordering by number of relationships in Hibernate

Question

Hi,

I've got two models which have a one-to-many relationship. Lets say its an auction for a product and the product can have many bids.

Basically what I'd like to do is pull out all the products but order them by the number of bids they've received.

What I've got so far is:

"select p from Product as p join p.bids b where b.product=p order by COUNT(b) ASC";

However, this only seems to pull out one product with bids on it. If i want to order during a hibernate query I can normally do something like

"select p from Product as p ORDER BY p.name";

I thought I could just do something along the lines of

"select p from Product as p ORDER BY COUNT(p.bids)";

But I cannot.

Does anyone haev any advice or experience with a problem like this?

Thanks, -gearoid

Answer 1

You can put a property on your product called numberOfBids, and then map it like this

<property name="numberOfBids">
   <formula>
      (SELECT (count(bids.id) WHERE bids.product_id = product_id)))
   </formula>
</property>

Now the only issue with this method is the query in the formula tags MUST be in straight SQL. So it is database specific. In this query, bids is the nane of the bids table in the database, and product_id is the name of the field in products that holds the id of the product. Also, you must add a property of type long to your Product data object as well.

Once you have done this, you can now do your query

select p from Product as p ORDER BY COUNT(p.numberOfBids)

Should work like a charm.