Queries generated incorrectly in class methods

[danielwaterworth]

Steps to reproduce

# frozen_string_literal: true

require "bundler/inline"

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

  gem "rails"
  gem "sqlite3", "~> 1.4"
end

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

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

ActiveRecord::Schema.define do
  create_table :categories do |t|
    t.integer :parent_category_id
  end
end

class Category < ActiveRecord::Base
  def self.parents
    Category.where(id: select(:parent_category_id))
  end
end

class BugTest < Minitest::Test
  def test_association_stuff
    parent = Category.create!(parent_category_id: nil)
    category = Category.create!(parent_category_id: parent.id)

    assert_equal([parent.id], Category.where(id: Category.where(id: category.id).select(:parent_category_id)).pluck(:id))
    assert_equal([parent.id], Category.where(id: category.id).parents.pluck(:id))
  end
end

Expected behavior

These two assertions should be equivalent. The only difference between them is that, in the second case, some of the logic is contained in a class method.

Actual behavior

The second query is generated incorrectly. The part that filters for a specific id is put into the top-level query when it shouldn't have been.

System configuration

Rails version: 7.1.3.3
Ruby version: 3.3.0

[Jay0921]

One of the differences between the first and second assertions is that the second assertion chains the parents method to the Category.where(id: category.id) query. In other words, it calls the parents method on the result of the Category.where(id: category.id) query. Since the parent record is not included in the result of the Category.where(id: category.id) query, calling the parents method on this result will not return the parent record.

To prove this, you can call Category.parents.pluck(:id), which should pass the test. In this case, all Category records will be available for the parent chain method.

assert_equal([parent.id], Category.parents.pluck(:id))

Trivial: Instead of using a class method, you can use a scope for the same purpose.

[vipulnsward]

Hi @danielwaterworth , thanks for the report. The two queries are not the same. If you were to expand the self.parents method on the base scope in the second query, it would look something like this:

Category.where(id: category.id).parents.pluck(:id) #expands to

Category.where(id: category.id).where(id: Category.where(id: category.id).select(:parent_category_id)).pluck(:id))

Your base base scope still will have .where(id: category.id) on it.

The first one is this:

Category.where(id: Category.where(id: category.id).select(:parent_category_id)).pluck(:id))

hence the difference.

Let me know if you still some issue with this. Thanks!