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 -- 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 -~----------~----~----~----~------~----~------~--~---
On 27 Aug 2008, at 10:59, Jay Pangmi wrote:> > 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 > endthat 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. Thankswell 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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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 Pangmi <rails-mailing-l...@andreas-s.net> wrote:> 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 > -- > 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 rubyonrails-talk@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
Fred's solution is way better. On Aug 27, 3:11 pm, Mukund <marut...@yahoo.com> wrote:> 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 Pangmi <rails-mailing-l...@andreas-s.net> > wrote: > > > 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 > > -- > > 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 rubyonrails-talk@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
On 27 Aug 2008, at 11:05, Frederick Cheung 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)> Fred--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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: ===============================================================================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. -- 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 -~----------~----~----~----~------~----~------~--~---
On Aug 28, 7:46 am, Jay Pangmi <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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> -- > 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 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 -~----------~----~----~----~------~----~------~--~---
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). > > FredThanks 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: ===============================================================================<div id="product-list"> <table cellpadding="5" cellspacing="1" > <% for businesses in @all_details %> <tr valign="top" class="<%= cycle(''list-line-odd'', ''list-line-even'') %>" > <td> <%=businesses.name%> </td> <td> <%=businesses.address%> </td> <td> <%=businesses.location%> </td> </tr> <%end%> </table> </div> =============================================================================== 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. -- 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 -~----------~----~----~----~------~----~------~--~---
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. -- 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 -~----------~----~----~----~------~----~------~--~---
On 28 Aug 2008, at 11:59, Jay Pangmi <rails-mailing-list@andreas- s.net> wrote:> > 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> > -- > 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 -~----------~----~----~----~------~----~------~--~---
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. > > FredHow can I use it? In Business.find(:all,:select => business[:location].....)? Thanks.. -- 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 -~----------~----~----~----~------~----~------~--~---
On 28 Aug 2008, at 12:22, Jay Pangmi wrote:> > 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> > > -- > 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 -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:> businesses = Business.find :all, :select => ..., :joins => ... > businesses.first[:location] instead of businesses.first.location > > FredThanks 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 -- 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 -~----------~----~----~----~------~----~------~--~---
On 28 Aug 2008, at 13:26, Jay Pangmi wrote:> > 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] couldThat''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> 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 > -- > 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 -~----------~----~----~----~------~----~------~--~---