休眠选择标准的顶部和底部n行 [英] Hibernate Select Top and Bottom n Rows with Criteria

问题描述

假设我有两张桌子，书籍和评论。评论有一个列，星星，可以有1到5之间的值。书可以有许多评论。

我如何选择所有书籍，以便每本书仅使用Criteria API返回顶部和底部3条评论（而不是所有评论）？

如果Criteria API无法使用，那么我可以为HQL，SQL等其他建议提供帮助。

解决方案回答我自己的问题...

实际上我很惊讶这是多么的困难。所有涉及查询图书的解决方案和每本书都有其顶部和底部评论列表的结果在每本书的N个查询中对某些数据库以及特别是我的（MySql）需要3N。当然，有人可能能够找出一个聪明的方法，但是我使用代码，原始SQL，研究其他人已经完成的工作，数据库限制等，似乎总是会回到这个问题。

所以......我最终解决了这个问题。当我查询图书（这是一个非常频繁的查询 - FYI图书/评论仅用于示例目的 - 实际的域名不同）时，我不计算顶部和底部评论，而是在每次提交新评论时计算顶部/底部。这个更改将查询从一个查询提交到三个查询，但与查询图书相比，提交查看相当少见。

为此，我添加了两个新的属性Book，topReviews和bottomReviews，并将它们映射为Review上的一对多列表。然后，我更新了保存新评论的代码，以查询相关图书的顶部和底部评论（2个查询），在图书上设置顶部/底部评论属性（覆盖以前的内容），并保存图书。任何书籍查询现在都会返回这些缓存的顶部和底部评论。我还有一个懒惰的评论属性，如果需要的话，它将包含所有评论（不只是顶部/底部） - 另一个一对多映射。

我打开到其他建议。这里的大多数答案都在球场中，但由于数据库限制或需要太多查询而错过了该问题或不起作用。

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.

解决方案

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.

这篇关于休眠选择标准的顶部和底部n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！