# Querying one table to find all possible combinations

**URL:** <https://www.ruby-forum.com/t/querying-one-table-to-find-all-possible-combinations/122395>\
**Category:** Rails\
**Created:** [December 7, 2007, 8:51am UTC](https://www.ruby-forum.com/t/querying-one-table-to-find-all-possible-combinations/122395 "2007-12-07T08:51:04Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![zayne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/51bf81/32.png) [@zayne](https://www.ruby-forum.com/u/zayne)\
**Post date:** [December 7, 2007, 8:51am UTC](https://www.ruby-forum.com/t/querying-one-table-to-find-all-possible-combinations/122395/1 "2007-12-07T08:51:04Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![zayne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/51bf81/32.png) [@zayne](https://www.ruby-forum.com/u/zayne)\
**Post date:** [December 7, 2007, 10:21am UTC](https://www.ruby-forum.com/t/querying-one-table-to-find-all-possible-combinations/122395/2 "2007-12-07T10:21:54Z")

</div>

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

- \<%= spec.name %\>
\<% end %\>

would for example show a list with name of spec\_items

for details check doc about ActiveRecord::Associations

> **[ActiveRecord::Associations::ClassMethods](https://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html)**
>
> Associations are a set of macro-like class methods for tying objects together through foreign keys.

---

<div class="post-metadata">

**Author:** ![zayne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/51bf81/32.png) [@zayne](https://www.ruby-forum.com/u/zayne)\
**Post date:** [December 7, 2007, 11:28am UTC](https://www.ruby-forum.com/t/querying-one-table-to-find-all-possible-combinations/122395/3 "2007-12-07T11:28:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zayne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/51bf81/32.png) [@zayne](https://www.ruby-forum.com/u/zayne)\
**Post date:** [December 7, 2007, 11:48am UTC](https://www.ruby-forum.com/t/querying-one-table-to-find-all-possible-combinations/122395/4 "2007-12-07T11:48:07Z")

</div>

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?