Forum: Ruby on Rails Sorting by multiple categories

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
627e2ddff2a657a4dd5e4080e16a55b0?d=identicon&s=25 BB (Guest)
on 2006-05-03 08:48
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.
4ae3c0b56fcf66f8383ecb73cc8d956e?d=identicon&s=25 Christopher Winslett (Guest)
on 2006-05-03 18:45
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.
79711168ab87c719d3c25446035f3932?d=identicon&s=25 Chris Farmiloe (Guest)
on 2006-05-03 19:06
(Received via mailing list)
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
2f46d76f0e5db4dc318b03be07ebaac4?d=identicon&s=25 Tom Ward (Guest)
on 2006-05-03 19:06
(Received via mailing list)
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
This topic is locked and can not be replied to.