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. -- Posted via http://www.ruby-forum.com/.
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
On 3 May 2006, at 07:48, 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.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
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 On 5/3/06, BB <bbsaid1@yahoo.com> 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. > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- email : tom at popdog.net
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.
--
Posted via http://www.ruby-forum.com/.