Rails - Dec 2007 - Querying one table to find all possible combinations

I am building an online shop and have products with specs - a shirt can
have colours and sizes. There are multiple colours and multiple sizes.
Obviously the shop has to be able to offer each combination to the
shopper. I want to write a query that will automatically insert all
possible combinations into the spec combinations table. Here''s my
current sql query that finds each combination:

select si1.id as ''1'', si2.id as ''2'' #,
si3.spec_id as ''3''
from spec_items as si1
left JOIN spec_items as si2 on si2.spec_id = Y
#left JOIN spec_items as si3 on si3.spec_id = Z
where si1.spec_id = X

spec_items is the table that lists the various colours, sizes, etc for
each spec.

My question is: Is there a way to do this in Rails without having to
resort to find_by_sql?
-- 
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
-~----------~----~----~----~------~----~------~--~---

has_many and belongs_ro are your friends :)

class Article < ActiveRecord::Base
  has_many :spec_items
  ...
end

class SpecItem < ActiveRecord::Base
  belongs_to :article
  # needs article_id in db for that
  ...
end

in the controller find your article:
@article = Article.find(article_id_you_want)

in the view something like that:
<% @article.spec_item.each do |spec| %>
  <li><%= spec.name %></li>
<% end %>

would for example show a list with name of spec_items

for details check doc about ActiveRecord::Associations
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html
-- 
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
-~----------~----~----~----~------~----~------~--~---

Thanks for the reply, but I don''t think you quite understood what I was
asking. Basically I need to get all combinations of each of the 
specifications, i.e. small and green, small and blue, small and red, 
medium and green, medium and blue, medium and red, etc.
-- 
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
-~----------~----~----~----~------~----~------~--~---

yep, then give us some details of your db, i''m to lazy to refactor your
sql :)
how do the tables and cols look and reference each other?
is this spec_item referencing itself?
-- 
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
-~----------~----~----~----~------~----~------~--~---