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:

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

that should be belongs_to :location

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

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

Fred's solution is way better.

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 Cheung 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:

Frederick Cheung 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.

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.

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

Frederick Cheung 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:

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.

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.

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

Frederick Cheung 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..

Frederick Cheung 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..

Not what I meant.

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

Fred

Frederick Cheung 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

Frederick Cheung 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