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
