Forcing ActiveRecord to count distinct (with Kaminari)

Question

I'm using Kaminari to paginate some result from a query in which I'm selecting distinct records. Consider the following controller code:

@things = Thing.joins... # create a complex query that produces duplicate results

# I want to select distinct results: this produces the correct results
@things = @things.select("DISTINCT things.*")

# when Kaminari calls count, it will run "SELECT COUNT(*)", instead of 
# "SELECT COUNT(DISTINCT things.*)" we will get the wrong count and extra pages
@things = @things.page(params[:page]).per(10)

The best solution that I can think of is to pass :distinct => true to count, like in this pull request, which was rejected by Kaminari's developer. This SO question discusses the underlying problem. This line of code is the offending call to count.

Are there any workarounds that will provide Kaminari with the correct count that don't involve patching Kaminari? Thanks.

UPDATE:

• Using a scope called "count" is a great suggestion but doesn't work when called on an ActiveRecord::Relation. It works when called on my model class, but that doesn't help.

Answer 1

please refer to the following url.

https://github.com/amatsuda/kaminari/pull/77

https://github.com/tbeauvais/kaminari/commit/23695cbdc4ff1b9fa58c18d4a3c2f18e21451b8b but ,they faild on Rails 3.1.0.

For Rails 3.1.0, create Rails.root/initializers/kaminari_for_distinct.rb. And use the following code.

module Kaminari
  module ActiveRecordRelationMethods
    extend ActiveSupport::Concern
    module InstanceMethods
      def total_count #:nodoc:
        if distinct_column_name.nil?
          c = except(:offset, :limit).count
        else  
          c = except(:offset, :limit).count(distinct_column_name, :distinct => true)
        end
        # .group returns an OrderdHash that responds to #count
        c.respond_to?(:count) ? c.count : c
      end

      # Get the column name used in distinct query.
      # This could have been set on the Model class, or the ActiveRecord::Relation 
      def distinct_column_name
        @distinct_column || distinct_column
      end
    end
  end
end

module Kaminari
  module ConfigurationMethods
    extend ActiveSupport::Concern
    module ClassMethods

      # Set the name of the column to use during .count()
      # Setting this will cause call to count to use: count(:id, :distinct => true) for all the Models paged queries. 
      # Example:
      #   class User < ActiveRecord::Base
      #     use_distinct :id
      #   end
      def use_distinct(column)
        @distinct_column = column
      end

      # Returns the distinct column name set on the Model, or nil if not using distinct
      def distinct_column
        @distinct_column
      end
    end
  end
end


module Kaminari
  module PageScopeMethods
    extend ActiveSupport::Concern
    module InstanceMethods

      # Set the name of the column to use during .count()
      # Setting this will cause call to count to use: count(:id, :distinct => true)
      # Example: User.page(3).per(5).use_distinct(:id)
      def use_distinct(column)
        @distinct_column = column
        self
      end
    end
  end
end

Answer 2

I would suggest a scope on the Model

This might confuse things though so you want to be careful

scope :count, select("DISTINCT things.*")

For more information look here