将使用命名范围对错误计数的奇数进行分页 [英] will paginate miscounting oddness with named scopes

问题描述

我最近将查询分为4个命名范围，以使其更易于重新排序，并且将其分页，否则该方法始终可以正常工作，现在在计算页数时遇到了问题.

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])

例如，分页显示有6页，每页15页，但是当您进入第4页时，第5-6页消失了……如果您尝试直接跳到5或6，则它们不存在.

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

但是

c=@location = Place.loc(params[:id]).datem(user_cart.getDate,user_cart.getDays).order_by_external_desc

c.size = 56

为前者生成的sql比后者生成的sql短约8行，而忽略了我的sql HAVING子句，这导致它返回更多结果...

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?

推荐答案

在使用group_by/having时-就像在datem named_scope中所做的那样-ActiveRecord会错误地计数错误.我认为您对此无能为力.我也不确定这是否一定是ActiveRecord中的错误-还是只是由于生成的SQL的性质而在使用ActiveRecord时实际上无法正确计算的事情.

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'.

执行此操作的方法是在单独的SQL语句(为您提供正确结果的SQL语句)中处理计数，并将其添加到分页结果中，如下所示:

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:

total_entries = Place.count(sql) # or the combinations of named_scopes etc with a .size or whichever one gives you the correct count

Place.scopes.paginate(:per_page => 15, :page => params[:page], :total_entries => total_entries) # where scopes are all of your named scopes as you have in your examples

这篇关于将使用命名范围对错误计数的奇数进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！