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?

has_many and belongs_ro are your friends :slight_smile:

class Article < ActiveRecord::Base
has_many :spec_items


class SpecItem < ActiveRecord::Base
belongs_to :article

needs article_id in db for that


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| %>

  • <%= spec.name %>
  • <% end %>

    would for example show a list with name of spec_items

    for details check doc about ActiveRecord::Associations

    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.

    yep, then give us some details of your db, i’m to lazy to refactor your
    sql :slight_smile:
    how do the tables and cols look and reference each other?
    is this spec_item referencing itself?