has_one :through using belongs_to produces incorrect SQL

I've experienced the same issue - I will try to work on a patch this week

The root cause was that has_one through is a special case of has_many through and has many through didn't allow the through reflection to be a belongs_to.

This was fixed for 2.2 in this commit which resolves the has one through issue too.

I'm actually still seeing something which I believe is a result of this problem. I have the same relationship as above:

class Foo < ActiveRecord::Base end

class Bar < ActiveRecord::Base belongs_to :foo end

class Bat < ActiveRecord::Base belongs_to :bar has_one :foo, :through => :bar end

(I use Bat because it's easier to pluralize than Baz) If I call the through attribute directly, then it works:

Bat.first.foo => #

But if I try to include the :foo association in any find conditions, then I get a similar error to the one above.

Bat.all(:include => :foo, :conditions => { 'foos.id' => 1 }) ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'bars.bar_id' in 'on clause': SELECT bats.id AS t0_r0, bat.bar_id AS t0_r1, foos.id AS t1_r0 FROM bats LEFT OUTER JOIN bars ON (bats.id = bars.bar_id) LEFT OUTER JOIN foos ON (foos.id = bats.foo_id) WHERE (foos.id = 1)

It seems as though the join on condition (bats.id = bars.bar_id) has simply reversed the foreign/primary keys here. So it should be: bats.bar_id = bars.id Correct?

My post got chewed up, but I think you can see the gist of it

I can confirm that I'm experiencing the same issues as Glenn is. Where the foreign/primary keys seem reversed (or something along those lines).

Can we see this no longer marked as "resolved"?

I'm having the same problem when I search Baz for Foos with a certain attr using a named scope defined in Baz in ActiveRecord 2.3.

Baz.with_Foo_attr(attr) yields: ActiveRecord::StatementInvalid: PGError: ERROR: column bars.bar_id does not exist

Baz.first.Foo works.

Turns out I had my :joins statement wrong--sorry.

I can also confirm this is still an issue.

I've attached a patch that verifies this is fixed in 2-3-stable with a concrete test case. Please review and advise

I am having this issue with 2.3.4, the issue isn't so much in the has_one :through a belongs_to relationship. Using Dan's patch example, there is no issue doing membership.member_type. However, it breaks once you do something more complex such as:

Membership.find :first, :include=>:member_type, :conditions=>['member_types.type = ?','VIP']

Would produce an error

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'members.member_id'

My specific example is as follows, and maybe I just have something incorrectly setup:

class Locus < ActiveRecord::Base
  belongs_to :square
  has_many :artifacts
end

class Artifact 
  belongs_to :locus
  has_one :square, :through=>:locus
end

So now if I want to find all the objects found in one excavation square, I would do the following:

Artifact.find :all, :include=>:square, :conditions=>['squares.code = ?','8K11']

Which instead produces the error:

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'loci.locus_id'

Any thoughts?