named_scope with :joins does not work properly through HABTM associations

I've had a quick look at this and the problem is that you have ambiguous column names and the select options from your scope are overridden by the association's select options which are constructed like this:

@reflection.options[:select] || "*"

Despite the problem with the ignored scope, what I found weird is that we are using "SELECT *" there instead of a specific select with the qualified table name.

This is apparently done to allow additional attributes on the join table to be selected as well, which I find rather confusing and which is probably a relic from those ancient "push_with_attributes" times:

def test_additional_columns_from_join_table
  assert_date_from_db Date.new(2004, 10, 10), Developer.find(1).projects.first.joined_on.to_date
end

A first proposal of a fix would be to replace the above default SELECT statement generation by the following, which would not yet solve the scope issues but is more safe anyway:

@reflection.options[:select] || "#{@reflection.quoted_table_name}.*"

I'll see if I can work out a patch for the scope stuff while waiting for your comments.

The only ambiguous column being pulled back is "id", and I think the behavior in question is precisely due to what you identified -- it's using "SELECT " instead of "SELECT parts.", and that is causing the wrong objects to come back. I'm glad you were able to pinpoint it, because I'm new to the Rails stack and was struggling to figure out where this was getting overwritten.

If I make that one change -- line 16 of has_and_belongs_to_many_association.rb -- the test shown in my pasted example will pass. That change also solves the problem I'm having in my real application, which prompted me to investigate in the first place.

So, thank you! I'm satisfied that we've resolved the issue, provided that change makes its way in.

I included a link to a failing test in my original report: http://pastie.org/352661

I meant a failing test in AR test suite http://guides.rails.info/contrib...

Sorry, correct link is http://guides.rails.info/contrib...

I've merged the earlier-linked failing test into the AR test suite and attached the diff here.

I am running into a similar issue, where my :select on the named_scope is ignored. It happens regardless of whether the main association has :select with specific fields or not. Could you please advise on whether the fix for this is coming or there is another workaround?

I am also having a similar problem in rails 2.3.2 and 2.3.4

Ditto on having my :select ignored by named_scope in a habtm relation, e.g.,:

foo.bars.my_scope

I end up with garbage records because of column name collision on the join.

Seems like this bug surfaces even without :select conditions.
For example in a case like this:

class Key < ActiveRecord::Base
  has_many :values
end

class Value < ActiveRecord::Base
  belongs_to :key
  default_scope :joins => :key, :order => "keys.name"
end

class Foo < ActiveRecord::Base
  has_and_belongs_to_many :values
end

Now when i access Foo.first.values it will do a "SELECT *" across 2 joins which results in "keys" table id-s shadowing "values" table id-s.

SELECT * FROM "values" INNER JOIN "keys" ON "keys".id = "values".key_id INNER JOIN "foos_values" ON "values".id = "foos_values".value_id WHERE ("foos_values".foo_id = 1) ORDER BY keys.name;

Right off the bat i would say that "SELECT 'values'.*" i.e the association endpoint table name would solve this issue, although i have no idea what else would break in result of this.

Since the 'default_select' method used in 'construct_finder_sql' in active_record/base.rb uses the '' as last resort, there is IMO no need to default to '' in has_and_belongs_to_many_association.rb. The attached patch fixed my problems with named_scopes and habtm associations.

I appear to be hitting this issue as well in 2.3.9 on OS X Snow Leopard. I have discovered a workaround that appears to work though. Using the model names in the original report (Car HABTM Parts; Parts belongs_to PartType), you can redefine the ordered named_scope like so:

class Parts < ActiveRecord::Base
  has_and_belongs_to_many :cars
  belongs_to :part_type

  # Workaround for named_scope :ordered, :conditions => ..., :joins => :part_type
  def self.ordered
    scoped(:conditions => ..., :joins => :part_type)
  end
end

This workaround appears to correctly put the table name in the SELECT query (e.g., SELECT parts.*). Looking at the named_scope code in ActiveRecord though, I don't see why scoped should behave any differently, since they both appear to ultimately end up calling ActiveRecord::NamedScope::Scope.new(self, scope, &block).

I see the milestone for this issue was moved from 2.x to 3.x; is there any chance we could see the fix for this in 2.x?