Hibernate Select Top and Bottom n Rows with Criteria

Question

Let's say I have two tables, Books and Reviews. Reviews has a column, stars, that can have a value between 1 and 5. A Book can have many Reviews.

How would I select all books such that only the top and bottom 3 reviews for each book are returned (instead of all reviews) using the Criteria API?

If the Criteria API is not capable, I am up for other suggestions like HQL, SQL, etc.

Answer 1

You could always set the ordering asc/desc and then limit the results.

ex:

criteria.addOrder(Order.desc("id")); criteria.setMaxResults(1); 

Not sure where it stands with regard to efficiency. I'm assuming that it's doing the filtering after the fact that a Select * has been fired off?

Answer 2

Try this and let me know if it works for you:

  // you should have the DAO class containing this queries 
  // extend HibernateDaoSupport

  List<Tag> topList = getSession().createQuery("FROM Reviews r WHERE r.book = " 
          + book + " ORDER BY r.stars asc").setMaxResults(3).list();

  List<Tag> bottomList = getSession().createQuery("FROM Reviews r WHERE r.book = " 
          + book + " ORDER BY r.stars desc").setMaxResults(3).list();

Answer 3

Answering my own question...

I'm actually surprised at how difficult this is. All solutions that involve querying for Books and each Book having its list of top and bottom Reviews results in N queries per Book on some databases, and for mine in particular (MySql) requires 3N. Of course, someone may be able to figure out a clever way around this, but my playing with the code, raw SQL, researching what others have done, database limitations, etc. appears to always come back to that.

So... I ended up flipping the problem around. Instead of calculating the top and bottom Reviews whenever I query for Books (which is a very frequent query - FYI Books/Reviews are for example purposes - the actual domain is different) I calculate the top/bottom whenever a new Review is submitted. This changes submitting a Review from one "query" into three queries, but submitting a Review is fairly infrequent compared to querying for Books.

To do this, I added two new properties to Book, topReviews and bottomReviews and mapped them as one-to-many lists on Review. I then updated the code that saves new Reviews to query for the top and bottom Reviews for the Book in question (2 queries), sets the top/bottom Reviews properties on the Book (overwriting the previous), and saves the Book. Any Book queries now return these "cached" top and bottom Reviews. I still have a lazy "reviews" property that will have all reviews (not just top/bottom) if desired - another one-to-many mapping.

I am open to other recommendations. Most answers here are in the ball park but miss the problem or won't work due to database limitations or require too many queries.