Can't eager load self_and_ancestors association in Rails 4.1

[jturkel]

Not sure if this is the same issues as in #70 but eager loading the self_and_ancestors association in Rails 4.1 generates invalid SQL.

Consider the following model:

class Category < ActiveRecord::Base
  acts_as_tree
end

Attempting to run Category.includes(:self_and_ancestors).to_a generates the following invalid SQL:

SELECT "categories".* 
FROM "categories"  
WHERE "categories"."id" IN (3, 2, 1)  
ORDER BY "category_hierarchies".generations asc

This worked fine in Rails 4.0. A full test case can be found in https://gist.github.com/jturkel/82df7f9e4ccf96df14a5.

It looks like the problematic order on the self_and_ancestors relation is unnecessary in Rails 4.1 since the ActiveRecord::Associations::Preloader::ThroughAssociation preserves the order of the source association.

[jturkel]

I take back my comment about the ActiveRecord::Associations::Preloader::ThroughAssociation in Rails 4.1 preserving the order of the "through" association. Temporary insanity.

[jturkel]

Rails 3.2 and 4.0 respect the order of the through association when eager loading has many through associations. Rails 4.1 doesn't although it does respect the ordering when lazy loading the association. I've filed bug rails/rails#17864 about this issue. Hopefully that will get fixed and then closure_tree can just drop the order on the self_and_ancestors and the self_and_descendants associations (at least when the order option isn't passed to acts_as_tree).

[estum]

To solve this I just recreated association after acts_as_tree:

acts_as_tree
has_many :self_and_descendants, ->{ select("DISTINCT ON(comments.id) comments.*").
                                      except(:joins).joins(:descendant_hierarchies).
                                      order("comments.id asc") }, 
                                  through: :descendant_hierarchies, 
                                  source: :descendant

I was going to make a commit, but old AR compatibility keeping turns changes to very massive construction. So, does it have a sense to make a pull request?

[jturkel]

@estum - It looks like your workaround changes the sort order of the self_and_descendants association from being ordered by generations to being ordered by id. If you switch the order to order("#{_ct.quoted_hierarchy_table_name}.generations asc") you'll unfortunately hit the same Rails eager loading bug :(

[incubus]

Same problem with 4.2.1.rc2:

AddressElement.includes(:self_and_ancestors).where(aolevel: 4).first

  AddressElement Load (53.4ms)  SELECT  "address_elements".* FROM "address_elements" WHERE "address_elements"."aolevel" = $1  ORDER BY "address_elements"."id" ASC LIMIT 1  [["aolevel", 4]]
  AddressElementHierarchy Load (59.9ms)  SELECT "address_element_hierarchies".* FROM "address_element_hierarchies" WHERE "address_element_hierarchies"."descendant_id" IN (629)  ORDER BY "address_element_hierarchies".generations asc
  AddressElement Load (1.1ms)  SELECT "address_elements".* FROM "address_elements" WHERE "address_elements"."id" IN (629, 1097528, 1091593)  ORDER BY "address_element_hierarchies".generations asc

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "address_element_hierarchies"

SELECT "address_elements".* FROM "address_elements" WHERE "address_elements"."id" IN (629, 1097528, 1091593)  ORDER BY "address_element_hierarchies".generations asc

[jturkel]

There is a slightly dodgy workaround for the issue:

1. Monkey patch Rails with the change from PR Honour joining model order in has_many :through associations when eager loading rails/rails#18766 which fixes eager loading a has_many through association ignores order of through association rails/rails#17864 and will hopefully go into the next Rails 4.1/4.2 patches.
2. Override the closure tree associations to be:

has_many :self_and_descendants, through: :descendant_hierarchies, source: :descendant
has_many :self_and_ancestors, through: :ancestor_hierarchies, source: :ancestor

With the Rails patch in place the associations will be properly ordered by the through association from the hierarchy table.

[bendilley]

I can confirm that adding the below after acts_as_tree works on Rails 4.2.6

has_many :self_and_descendants, through: :descendant_hierarchies, source: :descendant
has_many :self_and_ancestors, through: :ancestor_hierarchies, source: :ancestor

[mceachen]

Can we get a simple unit test for this, so we can make sure a workaround doesn't break other version/platform combinations (and it doesn't re-break in the future)?

[alexisraca]

Is this fix still relevant? Can't make it work with the through relationships mentioned in the comments

[will89]

In doing some quick testing, this seems to work in rails 5.1.6 and ruby 2.5.1, but in rails 5.1.4 and ruby 2.4.3 this issue still exists.

[atitan]

We're using closure_tree 7.0.0 with rails 5.1.6 and ruby 2.5.1. The same problem exists.

#<ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'category_hierarchies.generations' in 'order clause': SELECT `categories`.* FROM `categories` WHERE `categories`.`id` IN (1, 54, 2, 3, 4, 6, 7, 55, 8, 9, 46) ORDER BY `category_hierarchies`.generations ASC />

[ryanb]

I ran into this issue using goldiloader which does automatic eager-loading. I ended up disabling goldiloader for this method by adding the following to the model.

# Disable goldiloader for this closure tree method since it fails
def self_and_ancestors
  self.auto_include_context = nil
  super
end