How to do a join with Rails

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?

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

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.

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?

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