How to do a join with Rails

rubyonrails-talk
1

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?

2

Frew Schmidt 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

3

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.

4

Frew Schmidt 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

The generated SQL looks right to me, although it is the same as when I had :joins => :other_record.

Any other hints?

5

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