Join sql query in rubyonrails

hi, I have two tables: businesses and locations. The relationship is the
same location can have many businesses. For e.g: Telstra is in sydney
and optus can be in sydney as well. Here’s how I have defined relation:

================================================================================
class Business < ActiveRecord::Base
belongs_to :locations
end

class Location < ActiveRecord::Base
has_many :businesses
end

Now I want to use this query to only retrieve the location from the
locations table whose id is present in the businesses table:

SELECT location
FROM Locations AS l
WHERE l.id IN
(SELECT b.locationID
FROM Businesses AS b);

So, I was wondering how I can implement this in rubyonrails either by
“find” or “find_by_sql” method. Thanks

On 27 Aug 2008, at 10:59, Jay P. wrote:

=

=

=

=

class Business < ActiveRecord::Base
belongs_to :locations
end

that should be belongs_to :location

=

=
(SELECT b.locationID

======================================================================

So, I was wondering how I can implement this in rubyonrails either by
“find” or “find_by_sql” method. Thanks

well find_by_sql just takes a raw chunk of sql so that is easy enough
with find you could do

Location.find :all, :joins => :business (which does an inner join
which does the same thing as what you’ve got above, but usually more
efficiently)

Fred

Depends on performance versus readability. The ORM route can make
your code readable.

Location.find(:all, :include=>:businesses).map { |itr| itr.location if
itr.businesses.length > 0 }

For performance you can always use SQL of your liking, use the
find_by_sql or Model.connection.execute.

Regards,
Mukund

On Aug 27, 2:59 pm, Jay P. [email protected]
wrote:

has_many :businesses
FROM Businesses AS b);

So, I was wondering how I can implement this in rubyonrails either by
“find” or “find_by_sql” method. Thanks

Posted viahttp://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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~–~—

Fred’s solution is way better.

On Aug 27, 3:11 pm, Mukund [email protected] wrote:

Mukund

belongs_to :locations

SELECT location
FROM Locations AS l
WHERE l.id IN
(SELECT b.locationID
FROM Businesses AS b);

So, I was wondering how I can implement this in rubyonrails either by
“find” or “find_by_sql” method. Thanks

Posted viahttp://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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~–~—

On 27 Aug 2008, at 11:05, Frederick C. wrote:

with find you could do

Location.find :all, :joins => :business (which does an inner join
which does the same thing as what you’ve got above, but usually more
efficiently)

oops that should be :joins => :businesses (it needs to match the
association name)

Frederick C. wrote:

oops that should be :joins => :businesses (it needs to match the
association name)

Hi Fred, you have been really a great help for me and of course you guys
for sharing your knowledge. One thing that left me wondering is why is
it that it should be “belongs_to :location” and not “:locations” and
“has_many :businesses” and not “:business”. I though both will use the
actual name of the tables in the database but instead one uses and other
don’t.
And one more on sql query coz I came across a situation where I need
data from both tables for which I would use SQL query as:

SELECT b.name, b.address, l.location
FROM businesses AS b
JOIN locations AS l
ON b.location_id=l.id;

but again, as always, I need help to implement this on rails. I tried
like this:

Business.find(:all, :select => ‘businesses.name, businesses.address,
locations.location’, :joins => :location)

but the output appears without any location. So, how should I code.
Thanks.

Frederick C. wrote:

That’s normal: the output in the console is hardwired not to show any
extra attributes. They’re still there though (just try and access
them).

Fred

Thanks again for the reply. I’m trying to fetch those records in a web
page with this code:
IN CONTROLLER:

class AdminController < ApplicationController
def find_all
@all_details=Business.find(:all, :select => ‘businesses.name,
businesses.address, locations.location’, :joins => :location)
end
end

IN VIEW:

<% for businesses in @all_details %> <%end%>
<%=businesses.name%> <%=businesses.address%> <%=businesses.location%>
================================================================================

First two is displayed fine but the last one which is the location from
locations table isn’t displayed but I can see some space extra for it.
So, where am I going wrong.

On Aug 28, 7:46 am, Jay P. [email protected]
wrote:

Frederick C. wrote:

oops that should be :joins => :businesses (it needs to match the
association name)

Hi Fred, you have been really a great help for me and of course you guys
for sharing your knowledge. One thing that left me wondering is why is
it that it should be “belongs_to :location” and not “:locations” and
“has_many :businesses” and not “:business”. I though both will use the
actual name of the tables in the database but instead one uses and other
don’t.

singular associations (has_one, belongs_to) use the singular (so
has_one :location, :belongs_to location)
plural associations (has_many, has_and_belongs_to_many) use the
plural.

=========================================================================== =====
Business.find(:all, :select => ‘businesses.name, businesses.address,
locations.location’, :joins => :location)
=========================================================================== =====
but the output appears without any location. So, how should I code.
Thanks.

That’s normal: the output in the console is hardwired not to show any
extra attributes. They’re still there though (just try and access
them).

Fred

On 28 Aug 2008, at 11:59, Jay P. <[email protected]
s.net> wrote:

Business.find() I even tried locations.location AS location coz in sql
it gives its own name if not mentioned explicitly. But the result is
the
same.

I’d guess it’s because the extra attribute you’re getting has the same
name as an association so the accessor is getting squashed by the
association accessor (which returns nil because you’re not selecting
location_I’d). Try using business[:location] to access your extra
attribute or aliasing the column name.

Fred

I tried to print that in console as well with this code:

for datas in @all_details
puts datas.name
puts datas.address
puts datas.location
end

It gives name and address but nil in the location. In the :select in
Business.find() I even tried locations.location AS location coz in sql
it gives its own name if not mentioned explicitly. But the result is the
same.

Frederick C. wrote:

I’d guess it’s because the extra attribute you’re getting has the same
name as an association so the accessor is getting squashed by the
association accessor (which returns nil because you’re not selecting
location_I’d). Try using business[:location] to access your extra
attribute or aliasing the column name.

Fred

How can I use it? In Business.find(:all,:select =>
business[:location]…)? Thanks…

On 28 Aug 2008, at 12:22, Jay P. wrote:

How can I use it? In Business.find(:all,:select =>
business[:location]…)? Thanks…

Not what I meant.

businesses = Business.find :all, :select => …, :joins => …
businesses.first[:location] instead of businesses.first.location

Fred

Frederick C. wrote:

businesses = Business.find :all, :select => …, :joins => …
businesses.first[:location] instead of businesses.first.location

Fred

Thanks fred from the bottom of my heart. Thanks for everything. I now
got my code do what I wanted. Btw, businesses.first[:location] could
only retrieve same data. To be honest, I couldn’t figure out how I can
implement it in for loop but it worked well for single line execution.
However, the one I implemented locations.location AS location worked. I
just changed the alias name. Thanks again

On 28 Aug 2008, at 13:26, Jay P. wrote:

Frederick C. wrote:

businesses = Business.find :all, :select => …, :joins => …
businesses.first[:location] instead of businesses.first.location

Fred

Thanks fred from the bottom of my heart. Thanks for everything. I now
got my code do what I wanted. Btw, businesses.first[:location] could
That’s only an example, it obviously only gets the first one. To go
back to an earlier example of yours:

for datas in @all_details
puts datas.name
puts datas.address
puts datas[:location]
end

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs