Rails - May 2008 - 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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 21, 10:30 pm, Rails Terrorist <rails-mailing-l...@andreas-
s.net> wrote:
> 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
        from :0


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

Any other hints?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---