将使用命名范围对错误计数的奇怪情况进行分页
我最近将查询分成 4 个命名范围，以便更容易重新排序和分页，否则一直工作正常，但现在在计算页数时出现问题。
named_scope :loc, lambda { |id| { :conditions => ['location_id = ?', id ] } }
named_scope :datem, lambda { |*args| { :joins => :scannables, :conditions => [ "scannables.bookdate BETWEEN ? and ?", (args[0].to_date || 3.days.from_now), (args[0].to_date+(args[1] || 3)) ], :group => 'scannables.hostel_id', :having => 'SUM(scannables.available) > ' + ((args[1] || 3).to_i-1).to_s } }
named_scope :order_by_external_desc, :include => :external_ratings, :order => 'SUM(scannables.available) DESC, external_ratings.rating DESC'
named_scope :order_by_external_asc, :include => :external_ratings, :order => 'SUM(scannables.available) DESC, external_ratings.rating ASC'
像这样使用，并在末尾抛出分页...
@location = Place.loc(params[:id]).datem(user_cart.getDate,user_cart.getDays).order_by_external_desc.paginate(:per_page => 15, :page => params[:page])
例如，分页将显示有 6 页，每页 15 页，但是当您到达第 4 页时，第 5-6 页消失...并且如果您尝试跳到第 5 页或第 6 页直接说它们不存在。
看着它，我意识到分页的问题是，
c=@location = Place.loc(params[:id]).datem(user_cart.getDate,user_cart.getDays).order_by_external_desc.size
c = 78
然而，
c=@location = Place.loc(params[:id]).datem(user_cart.getDate,user_cart.getDays).order_by_external_desc
c.size = 56
为前者生成的sql比后者短约8行，并且忽略了我的sql HAVING子句，这导致它返回更多结果......
任何想法如何解决这个问题？
I recently broke up my query into 4 named scopes to make it easier to re-sort and will paginate which otherwise always worked fine now has problems calculating the number of pages.
named_scope :loc, lambda { |id| { :conditions => ['location_id = ?', id ] } }
named_scope :datem, lambda { |*args| { :joins => :scannables, :conditions => [ "scannables.bookdate BETWEEN ? and ?", (args[0].to_date || 3.days.from_now), (args[0].to_date+(args[1] || 3)) ], :group => 'scannables.hostel_id', :having => 'SUM(scannables.available) > ' + ((args[1] || 3).to_i-1).to_s } }
named_scope :order_by_external_desc, :include => :external_ratings, :order => 'SUM(scannables.available) DESC, external_ratings.rating DESC'
named_scope :order_by_external_asc, :include => :external_ratings, :order => 'SUM(scannables.available) DESC, external_ratings.rating ASC'
Used like so with paginate thrown on the end...
@location = Place.loc(params[:id]).datem(user_cart.getDate,user_cart.getDays).order_by_external_desc.paginate(:per_page => 15, :page => params[:page])
Paginate will for example show that there are 6 pages of 15 each but when you get to page 4, pages 5-6 disappear... and if you try jumping to 5 or 6 directly they don't exist.
Looking at it, I realized to problem will paginate is having is that
c=@location = Place.loc(params[:id]).datem(user_cart.getDate,user_cart.getDays).order_by_external_desc.size
c = 78
however
c=@location = Place.loc(params[:id]).datem(user_cart.getDate,user_cart.getDays).order_by_external_desc
c.size = 56
The sql getting generated for the former is about 8 lines shorter than that of the latter and neglects my sql HAVING clause which causes it to return more results...
Any ideas on how to fix this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当使用 group_by/having 时 - 正如您在日期命名范围中所做的那样 - 不幸的是，ActiveRecord 的计数错误。我认为你对此无能为力。我也不确定这是否一定是 ActiveRecord 中的错误 - 或者只是在使用 ActiveRecord 时由于生成的 SQL 的性质而实际上无法正确计算的问题。
无论哪种方式，您都需要解决这个“错误”。
执行此操作的方法是在单独的 SQL 语句（给出正确结果的语句）中处理计数，并将其添加到分页结果中，如下所示：
When using a group_by/having - as you do in your datem named_scope - ActiveRecord gets the count wrong unfortunately. I don't think there is much you can do about that. I am also not certain whether this is necessarily a bug in ActiveRecord - or just a thing that can't actually be calculated correctly when using ActiveRecord by the nature of the SQL generated.
Either way - you will need to work around this 'bug'.
The way to do this is to tackle the count in a separate SQL statement (the one that gives you the correct results), and adding it to the paginate results like so: