Sorting by multiple categories

Rails
1

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.

2

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.

3

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

4

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