user4965201 user4965201 - 3 months ago 42x
Ruby Question

Ruby on rails search in serialized array through the checkbox

Hi I have a rails app in which i am storing the tags in the serialized form


class Product < ActiveRecord::Base

serialize :product_keywords, Array

scope :with_tag, lambda { |tag|

where(["product_keywords LIKE ?", "%"+tag.join("%")+"%"])



in the view i have

<% do |product| %>

<% do |keywords| %>
<div class="checkbox">
<%= check_box_tag("with_tag[]", keywords, false) %> <%= keywords.capitalize %>
<% end %>
<% end %>

here when i find the product with the tags which are coming in the array from the checkbox from the scope
it only fetches the record based on the last checkbox checked

you can get a clear vision by the query occuring

product_keywords LIKE '%tshirt%printed%'

the product_keywords are stored in the array on the table.

how can i find the records if the multiple repetitive tags are stored in the products


With LIKE You should generate product_keywords LIKE "%#{tag}%" for every tag.

Finally yor should join them with SQL 'OR' or 'AND' depending on situation (any tag matching - use OR, all tags matching - use AND)

Assuming you want to have 'AND' you can easily iterate over all tags to set scope, something like this:

class Product < ActiveRecord::Base
  serialize :product_keywords, Array
  scope :with_tag, lambda { |tag| where(["product_keywords LIKE ?", "%#{tag}%") }


# we have selected tags in tags
products = Product.all
tags.each { |tag| products = products.with_tag(tag) }

this will generate where condition for each tag. Many where conditions are joined in AR by sql 'AND'

example sql output for tags set to ['tshirt', 'printed']

... WHERE product_keywords LIKE "%tshirt%" AND product_keywords LIKE "%printed%"

If you want to 'OR' the tags conditions (matching any) you should build the full SQL where clause, for example:

scope :with_any_tag, lambda { |tags|
  # build SQL stmt for all tags, i.e.
  # (product_keywords LIKE ?) OR (product_keywords LIKE ?)
  # replace 'OR' with 'AND' if you need all-matching instead of any-matching
  sql_stmt = tags.collect { |tag| "(product_keywords LIKE ?)" }.join(' OR ')

  # prepare sql placeholder values, i.e.
  # %tsrhit%, %printed%
  sql_placeholders = tags.collect {|tag| "%#{tag}%"}

  # pass conditions and placeholders to where as a list
  # first param is a stmt, then all placeholders
  where([sql_stmt] + sql_placeholders) 

This is more general solution, as you can change OR to AND and vice-versa, depending on the situation.

And then use it this way:

products = Product.with_any_tag(['tshirt', 'printed'])