thebusyant-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-Jun-10 16:42 UTC
using Model.find for inner joins and to ''dedupe'' a result set
Hi, I''m using rails 1.2.3 and mysql 5 I have a collection of models like this: Car has_many Passengers; Passenger has_many Pocket_Items ***I want to issue a Car.find command from rails where I find all cars where at least 1 passenger is carrying a knife (or whatever) in his/ her pocket.*** The details are shown below: Table 1: cars (id, license_plate, make, model) Table 2: passengers (id, car_id, first_name, last_name) Table 3: pocket_items (id, passenger_id, item_description) The description of the Car model would be something like this: class Car < ActiveRecord::Base has_many :passengers has_many :pocket_items, :through => :passengers end so, e.g. car # 1 may contain 2 passengers. passenger 1 may have a wallet and a knife, whereas passenger 2 may have a knife and keys. So, now let''s say that I want to find all cars where at least 1 passenger is carrying a knife. in SQL, I might issue the following statement select c.id, c.license_plate, c.make, c.model from cars c inner join passengers p on (p.car_id = c.id) inner join pocket_items pi on (pi.passenger_id = p.id) where pi.item_description = ''Knife'' this works, except that if a car contains 2 people, each carrying a knife, the same car will be returned on 2 separate rows. obviously, this can be eliminated by changing the statement above to "select distinct ...." My questions are: 1. Is there another (more elegant) way of demanding an INNER JOIN from rails aside from issuing it directly from the :joins option within the find method? 2. Is there a rails idiom for ''deduping'' my result set (i.e. I only want a result set of UNIQUE cars from my find query. From the agile development book, ed 2 (page 341), one can ''dedupe'' *child* objects using the modifier :uniq => true. However, i''m not sure if there''s a way to do this on the *parent* object. 3. are there any (more efficient) approaches to using rails to accomplish the query outlined above? i.e. I want to find all cars where at least 1 passenger carries a knife. thanks! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
bryanl
2007-Jun-21 13:02 UTC
Re: using Model.find for inner joins and to ''dedupe'' a result set
As for "deduping" your result set, have a look here: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M000992 You can pass the :select flag to find with something like this -> ":select => ''disctinct cars.*''" --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---