How to do a join with Rails

Rails
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 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

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.

–
Phlip
http://c2.com/cgi/wiki?AssertYinYang

4

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

5

On May 21, 10:30 pm, Rails T. <rails-mailing-l…@andreas-
s.net> 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?