Ruby on Rails query more than one fields

Question

In my rails app, how can I query more than one fields from my database? Currently when user search for a product based on keywords, the query only get the name field from db:

@products = @products.where("lower(name) LIKE ?", "%#{params[:search_free_text].downcase}%")

I need to include, description and highlight both in text format into the query. Thanks!!

score 3 · Accepted Answer · answered Mar 18 '16 at 08:00

This can be done with SQL like this:

@products = @products.where(
  %w( name description highlight ).map { |column_name| 
    "lower(#{column_name}) LIKE :query" 
  }.join(' OR '),
  query: "%#{params[:search_free_text].downcase}%")

I would say this is worth it to use a scope:

# in your app/models/product.rb
FULLTEXT_COLUMNS = %w( name description highlight )
scope :search, lambda { |query| 
  where(
    FULLTEXT_COLUMNS.map { |c| "lower(#{c}) LIKE :query" }.join(' OR '),
    query: "%#{query.downcase}%"
  )
}

# in your controller:
@products = @products.search(params[:search_free_text])

Deepesh · Answer 2 · 2016-03-18T08:35:04.720

1

You just need to follow the SQL format to do that:

@products = @products.where("lower(name) LIKE :query OR description LIKE :query", query: "%#{params[:search_free_text].downcase}%")

edited Mar 18 '16 at 08:35

answered Mar 18 '16 at 07:43

Deepesh

6,138
1
24
41

Hi, what is `params[:description_text]` is for? – d3bug3r Mar 18 '16 at 07:59
That is just a assumption from my side. You need to pass the `params` for `description` you are receiving from the view – Deepesh Mar 18 '16 at 08:00
1

@Deep: You should not build SQL queries by string interpolation. That is insecure and could be used for [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) attacks. Always use Rails' `where('... LIKE ...')` with [an array or a hash](http://stackoverflow.com/a/26094593/2483313). – spickermann Mar 18 '16 at 08:17
@spickermann Oh okay I have used it at some places in my project too but in model and not in controller, so using in model will be okay where there is no user params? – Deepesh Mar 18 '16 at 08:28
2

When you are absolutely sure it is never used with user input, then that might be okay. But I would suggest to always use the hash and array syntax to be sure that Rails quotes the queries correctly. There is no benefit in not using this security feature. – spickermann Mar 18 '16 at 09:03

Rajarshi Das · Answer 3 · 2016-03-18T13:50:55.593

Try this in a way make a scope in model Product

 class Product < ActiveRecord::Base
  scope :search_ndh, -> (name_text, desc_text, highlight_text){where("lower(name) LIKE :name OR description LIKE :desc OR highlight LIKE :high", name: "%#{name_text.downcase}%", desc: "%#{desc_text}%", high: "%#{highlight_text}%")} 
 end

and now use the scope

@products.search_ndh(params[:search_free_text].downcase, params[:search_description_text], params[:search_highlight_text])

or Arel ways

  products = Product.arel_table
  Product.where(products[:name].matches("%#{params[:search_free_text]}%").or(products[:description].matches("%#{params[:search_description_text]}%")).or(products[:highlight].matches("%#{params[:search_highlight_text]}%")))

or in scope by dynamic fields

class Product < ActiveRecord::Base
  SEARCH_COLUMNS = [:name, :description, :highlight]
  scope :search, lambda{ |q| where(SEARCH_COLUMNS.map{|c| self.arel_table[c].matches("%#{q}%").to_sql}.join(' OR '))} 
end

and then

 Product.search(params[:search_text])

it will generate

Product.search("das")

SELECT "products".* FROM "products" WHERE ("products"."name" LIKE '%das%' OR "products"."description" LIKE '%das%' OR "products"."highlight" LIKE '%das%')

Ruby on Rails query more than one fields

3 Answers3