Querying one table to find all possible combinations

Rails
1

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?

2

has_many and belongs_ro are your friends :slight_smile:

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

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

    would for example show a list with name of spec_items

    for details check doc about ActiveRecord::Associations

    3

    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.

    4

    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?