README.md

Description

My issue was about ordering and pagination. I was ordering a query on a column (of_the_moment_weight) that could have multi times the same value. Tehcnically, all entries could have the same value.

The result is that some entry appear on multiple page. But it shouldn't be the case.

Solution

Use two columns for ordering.

limit_offset_tests.rb

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  # Activate the gem you are reporting the issue against.
  gem "rails", "~> 5.1.6.2"
  gem "pg"
end

require "active_record"
require "minitest/autorun"
require "logger"

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "test", encoding: "unicode")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension "pgcrypto"
  enable_extension "unaccent"

  create_table "hotels", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
    t.uuid "city_id"
    t.string "name"
    t.integer "of_the_moment_weight", default: 0, null: false
    t.index ["city_id"], name: "index_hotels_on_city_id"
  end

  create_table "cities", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
    t.string "name"
    t.string "country_code", limit: 2
    t.string "continent"
  end

  add_foreign_key "hotels", "cities"
end

class Hotel < ActiveRecord::Base
  belongs_to :city
end

class City < ActiveRecord::Base
  has_many :hotels

  scope :by_country_code, ->(country_code) { where(country_code: country_code) }
  scope :by_continent_name, ->(continent_name) { where("lower(unaccent(continent)) = lower(unaccent(?))", continent_name) }
end

class BugTest < Minitest::Test
  def before_setup
    City.create!(name: "Paris", country_code: "FR", continent: "Europe")
    City.create!(name: "Nantes", country_code: "FR", continent: "Europe")
    City.create!(name: "Lyon", country_code: "FR", continent: "Europe")
    City.create!(name: "Amsterdam", country_code: "NL", continent: "Europe")

    City.find_each do |city|
      (1..100).each do |i|
        Hotel.create!(name: "Hotel #{city.name} #{i}", city: city)
      end
    end
  end

  def test_with_city
    hotels_page_1 = Hotel.order(of_the_moment_weight: :desc, id: :asc)
      .where(city: City.first)
      .limit(9).offset(0)

    hotels_page_2 = Hotel.order(of_the_moment_weight: :desc, id: :asc)
      .where(city: City.first)
      .limit(9).offset(9)

    assert_operator hotels_page_1.last, :"!=", hotels_page_2.last
  end

  def test_with_country_code
    hotels_page_1 = Hotel.order(of_the_moment_weight: :desc, id: :asc)
      .joins(:city).merge(City.by_country_code("FR"))
      .limit(9).offset(0)

    hotels_page_2 = Hotel.order(of_the_moment_weight: :desc, id: :asc)
      .joins(:city).merge(City.by_country_code("FR"))
      .limit(9).offset(9)

    assert_operator hotels_page_1.last, :"!=", hotels_page_2.last
  end

  def test_with_continent_name
    hotels_page_1 = Hotel.order(of_the_moment_weight: :desc, id: :asc)
      .joins(:city).merge(City.by_continent_name("Europe"))
      .limit(9).offset(0)

    hotels_page_2 = Hotel.order(of_the_moment_weight: :desc, id: :asc)
      .joins(:city).merge(City.by_continent_name("Europe"))
      .limit(9).offset(9)

    assert_operator hotels_page_1.last, :"!=", hotels_page_2.last
  end
end