I''m really struggling with this one and would appreciate some help:
Supplier has_and_belongs_to_many Parts
Given a list of parts (or part_id''s) I need to find the suppliers who
carry ALL those parts. Folks on IRC suggested I use something like
this :
parts.each {|p| p.suppliers...}
to collect a list of suppliers, but many parts would mean many queries
and I''d still have to some how filter out the suppliers who
don''t
carry one or more of the parts.
help! can someone point me in the right direction please? I''m pretty
sure the answer lies in LEFT JOIN, just not sure how
thanks
al
I don''t yet have enough rails experience to answer how best to do this in rails... However, this is an INNER JOIN between suppliers and parts. On 7/9/05, Alan Bullock <liststuff-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''m really struggling with this one and would appreciate some help: > > Supplier has_and_belongs_to_many Parts > > Given a list of parts (or part_id''s) I need to find the suppliers who > carry ALL those parts. Folks on IRC suggested I use something like > this : > > parts.each {|p| p.suppliers...} > > to collect a list of suppliers, but many parts would mean many queries > and I''d still have to some how filter out the suppliers who don''t > carry one or more of the parts. > > help! can someone point me in the right direction please? I''m pretty > sure the answer lies in LEFT JOIN, just not sure how > > thanks > al > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Also, this is accomplished using a suppliers_parts junction table to contain id to id associations. On 7/9/05, Ken Barker <ken.barker-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I don''t yet have enough rails experience to answer how best to do this > in rails... > > However, this is an INNER JOIN between suppliers and parts. > > On 7/9/05, Alan Bullock <liststuff-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I''m really struggling with this one and would appreciate some help: > > > > Supplier has_and_belongs_to_many Parts > > > > Given a list of parts (or part_id''s) I need to find the suppliers who > > carry ALL those parts. Folks on IRC suggested I use something like > > this : > > > > parts.each {|p| p.suppliers...} > > > > to collect a list of suppliers, but many parts would mean many queries > > and I''d still have to some how filter out the suppliers who don''t > > carry one or more of the parts. > > > > help! can someone point me in the right direction please? I''m pretty > > sure the answer lies in LEFT JOIN, just not sure how > > > > thanks > > al > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
On Sat, 2005-07-09 at 16:38 +0100, Alan Bullock wrote:> Given a list of parts (or part_id''s) I need to find the suppliers who > carry ALL those parts.> can someone point me in the right direction please? I''m pretty > sure the answer lies in LEFT JOIN, just not sure howFirst, this has nothing to do with joins. There isn''t a "single" query option that will give you want you want. Here is the best way to go about it Assumptions - you have an array with the partID list contained called parts. You also have a parts_suppliers table that has the joins of which suppliers have which parts. You want to execute the following Suppliers.find_by_sql("SELECT * FROM SUPPLIERS WHERE SUPPLIER_ID IN ( SELECT SUPPLIER_ID FROM PARTS_SUPPLIERS WHERE PART_ID IN (#{parts.join(",")}) GROUP BY SUPPLIER_ID HAVING COUNT(PART_ID) #{parts.length})") That will return you the collection of suppliers which meet your needs. John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
On 10.7.2005, at 00:30, John W Higgins wrote:> On Sat, 2005-07-09 at 16:38 +0100, Alan Bullock wrote: >> Given a list of parts (or part_id''s) I need to find the suppliers who >> carry ALL those parts. > >> can someone point me in the right direction please? I''m pretty >> sure the answer lies in LEFT JOIN, just not sure how > > First, this has nothing to do with joins. There isn''t a "single" query > option that will give you want you want.How''s this: select s.id from suppliers s join parts_suppliers ps on (s.id = ps.supplier_id) where ps.part_id in (#{parts.join(",")}) group by s.id having count(s.id) = #{parts.length} I know, it only gives you the id''s but you can add fields to it as needed. The only reason for posting this was to demonstrate that there probably *is* a single query way to do this as well. MySQL is AFAIK still very bad in optimizing subqueries. But as long as the performance is not a problem (or you''re using some more advanced RDBMS), John''s code is probably the better way to go. //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails