# Sorting by multiple categories

**URL:** <https://www.ruby-forum.com/t/sorting-by-multiple-categories/94379>\
**Category:** Rails\
**Created:** [May 3, 2006, 8:48am UTC](https://www.ruby-forum.com/t/sorting-by-multiple-categories/94379 "2006-05-03T08:48:55Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![BBBBBB](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/43a26b/32.png) [@BBBBBB](https://www.ruby-forum.com/u/BBBBBB)\
**Post date:** [May 3, 2006, 8:48am UTC](https://www.ruby-forum.com/t/sorting-by-multiple-categories/94379/1 "2006-05-03T08:48:55Z")

</div>

I have a database with a field called ‘category’.

‘category’ contains the categories separated by a “,” i.e. 1, 2, 5

I know with something like:

@products = Product.find(:all, :condition =\> “category = ‘1’”)

I could get some results if any of the ‘category’ fields only contained  
“1” but most contain multiple values.

Does anybody know if there is anything in rails that can deal with this?

Also I was looking at mysql regexp statements, but I’m not smart enough  
to write any sort of valuable regular expression.

Any help would be greatly appreciated. Thanks.

---

<div class="post-metadata">

**Author:** ![BBBBBB](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/43a26b/32.png) [@BBBBBB](https://www.ruby-forum.com/u/BBBBBB)\
**Post date:** [May 3, 2006, 6:45pm UTC](https://www.ruby-forum.com/t/sorting-by-multiple-categories/94379/2 "2006-05-03T18:45:25Z")

</div>

Sort or Filter?

Do you want to sort by the first value, second value, third value?

Or do you want to filter out those that have a one, those that have a 2,  
etc?  
@products = Product.find(:all, :condition =\> “category REGEXP  
‘[^0-9]#{value}[^0-9]’”);

The [^0-9] insures the value is not touching any other numbers, i.e. it  
won’t return 949 when you are searching for 4.

BB wrote:

> I have a database with a field called ‘category’.
> 
> ‘category’ contains the categories separated by a “,” i.e. 1, 2, 5
> 
> I know with something like:
> 
> @products = Product.find(:all, :condition =\> “category = ‘1’”)
> 
> I could get some results if any of the ‘category’ fields only contained  
> “1” but most contain multiple values.
> 
> Does anybody know if there is anything in rails that can deal with this?
> 
> Also I was looking at mysql regexp statements, but I’m not smart enough  
> to write any sort of valuable regular expression.
> 
> Any help would be greatly appreciated. Thanks.

---

<div class="post-metadata">

**Author:** ![BBBBBB](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/43a26b/32.png) [@BBBBBB](https://www.ruby-forum.com/u/BBBBBB)\
**Post date:** [May 3, 2006, 7:06pm UTC](https://www.ruby-forum.com/t/sorting-by-multiple-categories/94379/3 "2006-05-03T19:06:44Z")

</div>

hi…

thought Id join the rails list _waves to everyone_

… you could try using IN ?

@products = Product.find(:all, :condition =\> "category IN (#  
{category}) ")

how about

---

<div class="post-metadata">

**Author:** ![BBBBBB](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/43a26b/32.png) [@BBBBBB](https://www.ruby-forum.com/u/BBBBBB)\
**Post date:** [May 3, 2006, 7:06pm UTC](https://www.ruby-forum.com/t/sorting-by-multiple-categories/94379/4 "2006-05-03T19:06:53Z")

</div>

This is a database design problem rather than a rails one. Rather  
than storing all the category numbers in a ‘category’ column, you  
should probably create a join table linking products to categories  
(categories\_products table with two columns, product\_id and  
category\_id).

Not only will this make writing a query much simpler and more  
efficient, it will also let you make use of rails associations between  
products and categories. e.g:

class Product \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :categories  
end

class Category \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :products  
end

All products in the toys category:  
Product.find :all, :include =\> :categories, :conditions =\>  
[“category.name = ?”, “toys”]

All products in the home or garden categories:  
Product.find :all, :include =\> :categories, :conditions =\>  
[“category.name = ?”, [“home”, “garden”]]

Tom