user4965201 user4965201 - 5 months ago 61
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

product.rb

class Product < ActiveRecord::Base

serialize :product_keywords, Array

scope :with_tag, lambda { |tag|

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

}

end


in the view i have

<% Product.all.active.each do |product| %>

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


here when i find the product with the tags which are coming in the array from the checkbox from the scope
with_tag
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

Answer

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}%") }
end

usage

# 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'])