CODEWITHSUNDEEP
mysqlruby-on-railsruby
user11581234
user11581234

Reputation:

Query data that match string by two columns

Assume that I have products table with two columns category and name. Now user can find product by category or name or both of category and name

For example, a product has category computer and name Dell. User enter in search box "Computer", "Dell" or "Dell computer" or "computer Dell", both can return that product

Any idea about Activerecord query for this?

Upvotes: 2

Views: 86

Answers (2)

rlarcombe
rlarcombe

Reputation: 2986

Full text search is really what you need for your use case.

You might consider using Elasticsearch which is dead easy to set up with the Searchkick gem.

Another option for more complex searching, that wouldn't require a seperate search index, would be the Ransack gem.

But to answer your specific question, I suggest defining a class method in your product model, that converts the search term to an array of strings, and then performs the look up, something like:

def self.by_name_or_category(search)

  return None unless search.present?

  keywords = search.split(' ').map {|k| "%#{k}%" }

  where('name ilike any ( array[?] )', keywords)
    .or(Product.where('category ilike any ( array[?] )', keywords))

end

Upvotes: 0

quyetdc
quyetdc

Reputation: 1585

If you're using mysql, you can use CONCAT to join category and name, then, make a LIKE query as normal

Product.where("LOWER(CONCAT(name, ' ', category)) LIKE :query OR LOWER(CONCAT(category, ' ', name)) LIKE :query", query: "%#{params[:query].downcase}%")

With this query, both of the cases: Dell, Computer, Dell Computer or Computer Dell can be found

Upvotes: 2

Related Questions