# How to do a join with Rails **URL:** \ **Category:** Rails\ **Created:** [May 22, 2008, 5:09am UTC](https://www.ruby-forum.com/t/how-to-do-a-join-with-rails/138338 "2008-05-22T05:09:31Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![frew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c0e974/32.png) [@frew](https://www.ruby-forum.com/u/frew)\ **Post date:** [May 22, 2008, 5:09am UTC](https://www.ruby-forum.com/t/how-to-do-a-join-with-rails/138338/1 "2008-05-22T05:09:31Z")
Hey all, I am trying to do a regular old join with ActiveRecord. This is the code that a friend suggested: A.find(:all, :conditions =\> {‘b.foo’ =\> 25}, :joins =\> [:b]) The idea here is to find all of the A’s that have b’s with a method foo equal to 25. I am getting an error saying ActiveRecord::StatementInvalid: Mysql::Error: Unknown column ‘b.foo’ Now, if I look at my db/schema.rb I have something along these lines: create\_table “b”, :force =\> true do |t| … t.integer “foo” end Can anyone tell me what I am doing wrong? ---
**Author:** ![frew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c0e974/32.png) [@frew](https://www.ruby-forum.com/u/frew)\ **Post date:** [May 22, 2008, 5:30am UTC](https://www.ruby-forum.com/t/how-to-do-a-join-with-rails/138338/2 "2008-05-22T05:30:48Z")
Frew S. wrote: > A.find(:all, :conditions =\> {‘b.foo’ =\> 25} ← becareful of SQL injection here A.find(:all, :conditions =\> [“b.foo = ?”, 25], :joins =\> {:b =\>{}} ) look to your B relational table, is it has many or belongs\_to, it would be :b or :bs. Good luck. And make sure that A contains A.b\_id so that in join, A.b\_id = B.id in the real SQL Statement. Reinhart [http://teapoci.blogspot.com](http://teapoci.blogspot.com) ---
**Author:** ![frew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c0e974/32.png) [@frew](https://www.ruby-forum.com/u/frew)\ **Post date:** [May 22, 2008, 5:40am UTC](https://www.ruby-forum.com/t/how-to-do-a-join-with-rails/138338/3 "2008-05-22T05:40:30Z")
frioux wrote: > I am trying to do a regular old join with ActiveRecord. Don’t think of AR as a way to replicate entire SELECT statements. Think of it as glue between models. Then go to A and add has\_many :bs > A.find(:all, :conditions =\> {‘b.foo’ =\> 25}, :joins =\> [:b]) > > The idea here is to find all of the A’s that have b’s with a method > foo equal to 25. “method”? you mean “field”, right? A.bs.find\_by\_foo(25) See - anything to do with the join was taken care of for you. Because you declared the has\_many itself, in a more convenient spot, AR did not need to be reminded. A.bs.find automatically finds Bs that join to As. Next, if you actually needed a condition, it would might look like: :conditions =\> [‘b.foo = ?’, 25] …but I suspect your version might work to, because I’m unaware of anything claiming that “conditions followed by a hash” DSL niche. – Phlip [http://c2.com/cgi/wiki?AssertYinYang](http://c2.com/cgi/wiki?AssertYinYang) ---
**Author:** ![frew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c0e974/32.png) [@frew](https://www.ruby-forum.com/u/frew)\ **Post date:** [May 22, 2008, 6:29am UTC](https://www.ruby-forum.com/t/how-to-do-a-join-with-rails/138338/4 "2008-05-22T06:29:02Z")
I figured it out. I had this: OilRecord.find(:all, :conditions =\> [‘other\_record.car\_id = ?’, 1], :joins =\> :other\_record) I needed this: OilRecord.find(:all, :conditions =\> [‘other\_records.car\_id = ?’, 1], :joins =\> :other\_record) I ended up just playing with the mysql console to see what the actual issue was. Silly me! Thanks for the help fellas. -fREW ---
**Author:** ![frew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c0e974/32.png) [@frew](https://www.ruby-forum.com/u/frew)\ **Post date:** [May 22, 2008, 5:41am UTC](https://www.ruby-forum.com/t/how-to-do-a-join-with-rails/138338/5 "2008-05-22T05:41:40Z")
On May 21, 10:30 pm, Rails T. \ wrote: > Frew S. wrote: > > > A.find(:all, :conditions =\> {‘b.foo’ =\> 25} ← becareful of SQL injection here > > A.find(:all, :conditions =\> [“b.foo = ?”, 25], :joins =\> {:b =\>{}} ) > > look to your B relational table, is it has many or belongs\_to, it would > be :b or :bs. Good luck. And make sure that A contains A.b\_id so that in > join, A.b\_id = B.id in the real SQL Statement. Thanks for your quick response. I am still not having any luck here. Maybe I have a stupid error that I am not seeing. Here is my actual query: OilRecord.find(:all, :conditions =\> [‘other\_record.car\_id = ?’, 1], :joins =\> {:other\_record =\> {}}) there is a belongs\_to relationship from OilRecord to :other\_record. I am getting the following error: ActiveRecord::StatementInvalid: Mysql::Error: Unknown column ‘other\_record.car\_id’ in ‘where clause’: SELECT `oil_records`.\* FROM `oil_records` INNER JOIN `other_records` ON `other_records`.id = `oil_records`.other\_record\_id WHERE (other\_record.car\_id = 1) from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active\_record/connection\_adapters/abstract\_adapter.rb:150:in `log' from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active_record/connection_adapters/mysql_adapter.rb:281:in `execute’ from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active\_record/connection\_adapters/mysql\_adapter.rb:481:in `select' from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active_record/connection_adapters/abstract/database_statements.rb:7:in `select\_all\_without\_query\_cache’ from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active\_record/connection\_adapters/abstract/query\_cache.rb:55:in `select_all' from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active_record/base.rb:532:in `find\_by\_sql’ from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active\_record/base.rb:1233:in `find_every' from /usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/ active_record/base.rb:503:in `find’ from (irb):69 from :0 The generated SQL looks right to me, although it is the same as when I had :joins =\> :other\_record. Any other hints?