Bug: Only Limit 1 Query

[hatsu38]

👀 Before submitting...

• I upgraded to pagy version 8.4.0
• I searched through the Documentation
• I searched through the Issues
• I searched through the Q&A

🧐 REQUIREMENTS

• I am providing a VALID code file that confirms the bug
• I am NOT posting any of the USELESS THINGS listed above
• I am aware that this issue will be automatically closed if the code file is missing or INVALID

💬 Description

I regularly use the acts_as_tenant Gem to develop in a multi-tenant environment.

When using Pagy version 8.4, only one Post is displayed despite there being multiple Posts, and the query has a Limit: 1.

In contrast, with Pagy version 8.3, two Posts are displayed. This issue can be reproduced with the following code.

# frozen_string_literal: true

# Starting point to reproduce rails related pagy issues

# DEV USAGE
#    pagy clone rails
#    pagy ./rails.ru

# URL
#    http://0.0.0.0:8000

# HELP
#    pagy -h

# DOC
#    https://ddnexus.github.io/pagy/playground/#2-rails-app

VERSION = '8.4.0'

# Gemfile
require 'bundler/inline'
require 'bundler'
Bundler.configure
# gemfile(ENV['PAGY_INSTALL_BUNDLE'] == 'true') do
#   source 'https://rubygems.org'
#   # gem 'oj'
#   # gem 'puma'
#   # gem 'rails'
#   # gem 'sqlite3'
# end

# require 'rails/all'     # too much stuff
require 'action_controller/railtie'
require 'active_record'

OUTPUT = Rails.env.showcase? ? IO::NULL : $stdout

# Rails config
class PagyRails < Rails::Application # :nodoc:
  config.root = __dir__
  config.session_store :cookie_store, key: 'cookie_store_key'
  Rails.application.credentials.secret_key_base = 'absolute_secret'

  config.logger = Logger.new(OUTPUT)
  Rails.logger  = config.logger

  routes.draw do
    root to: 'comments#index'
    get '/javascript' => 'pagy#javascript'
  end
end

# AR config
dir = Rails.env.development? ? '.' : Dir.pwd  # app dir in dev or pwd otherwise
unless File.writable?(dir)
  warn "ERROR: directory #{dir.inspect} is not writable (the pagy-rails-app needs to create DB files)"
  exit 1
end

# Pagy initializer
require 'pagy/extras/pagy'
require 'pagy/extras/items'
require 'pagy/extras/overflow'
Pagy::DEFAULT[:items] = 10
Pagy::DEFAULT[:overflow] = :last_page
Pagy::DEFAULT[:jsonapi] = true

Pagy::DEFAULT.freeze

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: "#{dir}/tmp/pagy-rails.sqlite3")
ActiveRecord::Schema.define do
  create_table :companies, force: true do |t|
    t.string :name
  end

  create_table :posts, force: true do |t|
    t.string :title
    t.references :company
  end

  create_table :tags, force: true do |t|
    t.string :name
    t.references :company
  end

  create_table :tag_and_posts, force: true do |t|
    t.references :company
    t.references :post
    t.references :tag
  end
end

# Models

class Company < ActiveRecord::Base # :nodoc:
  has_many :posts
  has_many :tags
end # :nodoc:

class Post < ActiveRecord::Base # :nodoc:
  belongs_to :company
  has_many :tag_and_posts
  has_many :tags, through: :tag_and_posts
end # :nodoc:

class Tag < ActiveRecord::Base # :nodoc:
  belongs_to :company
  has_many :tag_and_posts
  has_many :posts, through: :tag_and_posts
end # :nodoc:

class TagAndPost < ActiveRecord::Base # :nodoc:
  belongs_to :company
  belongs_to :post
  belongs_to :tag
end # :nodoc:

# DB seed
1.upto(2) do |pi|
  company = Company.create!(name: "Company #{pi}")
  1.upto(3) do |i|
    post = Post.create!(title: "Post #{i}", company: company)
    1.upto(3) do |j|
      tag = Tag.create!(name: "Tag #{j}", company: company)
      TagAndPost.create!(company: company, post: post, tag: tag)
    end
  end
end

# Down here to avoid logging the DB seed above at each restart
ActiveRecord::Base.logger = Logger.new(OUTPUT)

# Helpers
module CommentsHelper
  include Pagy::Frontend
end

# Controllers
class CommentsController < ActionController::Base # :nodoc:
  include Rails.application.routes.url_helpers
  include Pagy::Backend

  def index
    company = Company.find(1)
    tag = company.tags.first
    posts = tag.posts.where(company: company)
    @pagy, @posts = pagy(posts)
    render json: {
      posts: @posts.map do |post| {
        id: post.id,
        title: post.title,
      } end
    }
  end
end

# You don't need this in real rails apps (see https://ddnexus.github.io/pagy/docs/api/javascript/setup/#2-configure)
class PagyController < ActionController::Base
  def javascript
    file = "pagy#{'-dev' if ENV['DEBUG']}.js"
    render js: Pagy.root.join('javascripts', file).read
  end
end

run PagyRails

Query

I, [2024-05-28T20:08:15.523702 #8264]  INFO -- : Started GET "/" for 127.0.0.1 at 2024-05-28 20:08:15 +0900
D, [2024-05-28T20:08:15.660622 #8264] DEBUG -- :   Company Load (0.5ms)  SELECT "companies".* FROM "companies" WHERE "companies"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
D, [2024-05-28T20:08:15.669299 #8264] DEBUG -- :   Tag Load (0.3ms)  SELECT "tags".* FROM "tags" WHERE "tags"."company_id" = ? ORDER BY "tags"."id" ASC LIMIT ?  [["company_id", 1], ["LIMIT", 1]]
D, [2024-05-28T20:08:15.673523 #8264] DEBUG -- :   Post Count (0.1ms)  SELECT COUNT(*) FROM "posts" INNER JOIN "tag_and_posts" ON "posts"."id" = "tag_and_posts"."post_id" WHERE "tag_and_posts"."tag_id" = ? AND "posts"."company_id" = ?  [["tag_id", 1], ["company_id", 1]]
D, [2024-05-28T20:08:15.673772 #8264] DEBUG -- :   Post Load (0.0ms)  SELECT "posts".* FROM "posts" INNER JOIN "tag_and_posts" ON "posts"."id" = "tag_and_posts"."post_id" WHERE "tag_and_posts"."tag_id" = ? AND "posts"."company_id" = ? LIMIT ? OFFSET ?  [["tag_id", 1], ["company_id", 1], ["LIMIT", 1], ["OFFSET", 0]]

[ddnexus]

Thank you @hatsu38

It might be related to #704.

Since I have no laptop available here (for a while) @benkoshy could you confirm and check whether the fix for #704 would solve also this one?

Thank you

[hatsu38]

@ddnexus Using the #707 gem "pagy", github: "ddnexus/pagy", branch: "fix-mysql-server-issue" fixed the issue! Thank you!

[benkoshy]

Thank you @hatsu38

It might be related to #704.

Since I have no laptop available here (for a while) @benkoshy could you confirm and check whether the fix for #704 would solve also this one?

Thank you

Will review on Monday 3rd June.