#1167 has_one :through using belongs_to produces incorrect SQL - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#1167 ✓resolved

has_one :through using belongs_to produces incorrect SQL

Reported by John Wilger | October 4th, 2008 @ 01:54 AM | in 2.x

Using the following model classes:

class Foo < ActiveRecord::Base
end

class Bar < ActiveRecord::Base
  belongs_to :foo
end

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

The following code:

Baz.create.foo

will result in a SQL query with an incorrect WHERE clause and an exception similar to the following:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such
column: bars.bar_id: SELECT "foos".* FROM "foos"
INNER JOIN bars ON foos.id = bars.foo_id
WHERE (("bars".bar_id = 1))

I'v attached a test case which proves the bug.

0001-Added-test-case-to-prove-bug-in-has_one-through-usi.patch 4 KB

Comments and changes to this ticket

Dan Pickett November 2nd, 2008 @ 02:44 PM
I've experienced the same issue - I will try to work on a patch this week
Frederick Cheung December 20th, 2008 @ 01:53 PM
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.
Frederick Cheung December 20th, 2008 @ 02:00 PM
- State changed from “new” to “resolved”
You flagged this item as spam.
Glenn Powell February 2nd, 2009 @ 04:01 PM
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?
You flagged this item as spam.
Glenn Powell February 2nd, 2009 @ 04:02 PM
My post got chewed up, but I think you can see the gist of it
You flagged this item as spam.
Brian Landau February 12th, 2009 @ 09:20 PM
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"?
You flagged this item as spam.
ronin-38963 (at lighthouseapp) April 24th, 2009 @ 09:24 PM
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.
You flagged this item as spam.
ronin-38963 (at lighthouseapp) April 24th, 2009 @ 09:35 PM
Turns out I had my :joins statement wrong--sorry.
You flagged this item as spam.
Emrys Ingersoll September 23rd, 2009 @ 04:02 PM
I can also confirm this is still an issue.
Dan Pickett October 28th, 2009 @ 10:08 PM
I've attached a patch that verifies this is fixed in 2-3-stable with a concrete test case. Please review and advise
- ho_bt_through_test.patch 1.5 KB
Dan Pickett October 28th, 2009 @ 10:08 PM
- Assigned user set to “Frederick Cheung”
You flagged this item as spam.
Matthew Vincent November 13th, 2009 @ 05:23 PM
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?