Unexpected ORDER BY Clause From has_many :through

rubyonrails-talk
1

I was recently surprised by how order is handled within a has_many :through relationship. Below is a script that demonstrates the problem:

require 'bundler/inline'

gemfile do
 source 'https://rubygems.org'
 gem 'activerecord', require: 'active_record'
 gem 'sqlite3'
 gem 'minitest'
 gem 'minitest-bang', require: 'minitest/bang'
end

require 'minitest/spec'
require 'minitest/autorun'

describe 'has_many :through with order' do
  let!(:widget) { Widget.create! }

  let!(:group1) { Group.create! position: 1, widget: }
  let!(:item1) { Item.create! position: 1, group: group1 }
  let!(:item2) { Item.create! position: 2, group: group1 }

  let!(:group2) { Group.create! position: 2, widget: }
  let!(:item3) { Item.create! position: 1, group: group2 }
  let!(:item4) { Item.create! position: 2, group: group2 }

  it 'should return items sorted by group then within group' do
    # puts widget.items.to_sql
    expect( widget.items.to_a ).must_equal [item1, item2, item3, item4]
  end
end

ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'
ActiveRecord::Migration.verbose = false

ActiveRecord::Schema.define version: 1 do
  create_table :widgets
  create_table :groups do |t|
    t.belongs_to :widget, null: false
    t.integer :position, null: false
  end
  create_table :items do |t|
    t.belongs_to :group, null: false
    t.integer :position, null: false
  end
end

class Widget < ActiveRecord::Base
  has_many :groups, -> { order :position }
  has_many :items, through: :groups
  # has_many :items, -> { order 'groups.position, items.position' }, through: :groups
end

class Group < ActiveRecord::Base
  belongs_to :widget
  has_many :items, -> { order :position }
end

class Item < ActiveRecord::Base
  belongs_to :group
end

I expected the ORDER BY clause would end up being:

ORDER BY "groups"."position" ASC, "items"."position" ASC

but if you un-comment the puts line you will see it actually ends up being:

ORDER BY "items"."position" ASC, "groups"."position" ASC

I am able to work around this by explicitly providing an order clause on the has_many :through relationship. You can see in my script a commented out version of that relationship. Swap which has_many :through is commented out and the tests will pass.

I am interested in feedback regarding if my expectation of how this should work is wrong or if Rails is not doing the right thing and perhaps I should investigate changing Rails to fix this. Interested in hearing the opinions of others. Thanks!

2

This is better suited as an issue on rails/rails IMHO

3

Thanks for the suggestion. I had thought about that but wasn’t really quite sure the problem was Rails or my expectations. Will give a try over there and see what they think.