Why does .order doesn't get executed in my query?

Question

I have slightly modified version for Kaminari to find on which page my record is (original code is here: https://github.com/kaminari/kaminari/wiki/FAQ#how-can-i-know-which-page-a-record-is-on):

module KaminariHelper
  extend ActiveSupport::Concern

  # Detecting page number in pagination. This method allows you to specify column name, order,
  # items per page and collection ids to filter specific collection.
  #
  # Options:
  #  * :by - specifies the column name. Defaults to :id.
  #  * :order - specifies the order in DB. Defaults to :asc.
  #  * :per - specifies amount of items per page. Defaults to object model's default_per_page.
  #  * :nulls_last - if set to true, will add "nulls last" into the order.
  #  * :collection_ids - array of ids for the collection of current class to search position in specific collection.
  #    If it's ommited then search is done across all objects of current object's model.
  #
  def page_num(options = {})
    column     = options[:by] || :id
    order      = options[:order] || :asc
    per        = options[:per] || self.class.default_per_page
    nulls_last = options[:nulls_last] == true ? "nulls last" : ""
    operator   = (order == :asc ? "<=" : ">=")

    data = if options[:collection_ids].present?
      self.class.where(id: options[:collection_ids])
    else
      self.class
    end

    # 1. Get a count number of all the results that are listed before the given record/value.
    # 2. Divide the count by default_per_page or per number given as an option.
    # 3. Ceil the number to get a proper page number that the record/value is on.
    (
      data.where("#{column} #{operator} ?", read_attribute(column))
          .order("#{column} #{order} #{nulls_last}").count.to_f / per
    ).ceil
  end
end

However when I test it for some weird reasons .order doesn't seem to be executed. Here is the sql output in rails console:

2.3.1 :005 > email.page_num(by: :sent_at, order: :desc, per: 25, collection_ids: user.emails.ids, nulls_last: true)
   (1.1ms)  SELECT "emails"."id" FROM "emails" WHERE "emails"."deleted_at" IS NULL
 AND "emails"."user_id" = $1  [["user_id", 648]]
   (1.5ms)  SELECT COUNT(*) FROM "emails" WHERE "emails"."deleted_at" IS NULL AND
 "emails"."id" IN (35946, 41741) AND (sent_at >= '2016-01-22 14:04:26.700352')
 => 13

Why does .order is not applied in the final SQL query? Is there a way to execute it? Otherwise the code doesn't make sense since there are no guarantee it'll give me proper page number.

score 0 · Answer 1 · answered Apr 26 '17 at 23:09

Rails does ignore the order clause when counting.

Instead of relying on Rails' count method, try counting manually:

 data.where("#{column} #{operator} ?", read_attribute(column))
      .order("#{column} #{order} #{nulls_last}")
      .select('COUNT(*) c').first.c.to_f / per

Why does .order doesn't get executed in my query?

1 Answers1