• Registered: 2012-10-04
  • Posts: 2

Topic: Activerecord newbee - simple query

Struggling with formulating a simple join. I am pretty new to activerecord and ruby.I have a database with tables 'bugs' and 'longdesc'. Here are my classes.class

Bug < ActiveRecord::Base
  has_many :longdescs
endclass

Longdesc < ActiveRecord::Base
  belongs_to :bugs
end

I am trying to formulate a simple query joining these two tables but am having no luck. I can get a query without joining to work fine. For example, the following works perfectly:

bugs = Bug.select("bug_id,bug_status,short_desc").where(:bug_id => ARGV)

Now I want to join this with the longdescs table which has column 'thetext' and has bugs.bug_id as the foreign key. Here is the plain ole sql:

select B.bug_id, B.bug_status, B.short_desc, L.thetext from bugs B, longdesc L
where B.bug_id in (300,301) and B.bug_id = L.bug_id

Any assistance would be greatly appreciated!

Last edited by rjlindal (2012-10-04 15:51:59)

  • jamesw
  • Administrator
  • Offline
  • From: Leeds, UK
  • Registered: 2008-06-07
  • Posts: 2,701

Re: Activerecord newbee - simple query

Open up a rails console and try

>> bug = Bug.find(ARGV)
>> bug.longdescs.each do |ld|
>> puts ld.thetext
>> end

Replacing ARGV with whatever id it is you are looking for

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)
  • Registered: 2012-10-04
  • Posts: 2

Re: Activerecord newbee - simple query

Thanks for the reply Jamesw!

I finally found the solution I was looking for. The following got me exactly what I wanted...

bugs = Bug.joins(:longdescs).select("bugs.bug_id,bugs.bug_status,bugs.priority,bugs.bug_severity,
                                                           bugs.short_desc,longdescs.thetext").where(:bug_id => ARGV)

Rick

  • jamesw
  • Administrator
  • Offline
  • From: Leeds, UK
  • Registered: 2008-06-07
  • Posts: 2,701

Re: Activerecord newbee - simple query

Nie one. Now I see what you were looking for! lol smile

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)