How to order results by closest match to query?

Question

I have a field where I'm using Jquery TokenInput to autocomplete a Product. To find the products I'm doing:

def token
   @products = Product.order(:name)
   respond_to do |format|
     format.html
     format.json { render json: @products.where("name ILIKE ?", "%#{params[:q]}%").limit(10).order('name DESC') }
   end
end

If I have a product named Rice and do a search on the field, typing in "r", "ri", and "ric", I want the closest result to be the one that matches the query. Instead though, if I have over 10 different types of Rices in the database, the results will be "out of order" with Rice being placed around 6th or 9th place out of 10 founded rices. To me, Rice should come first and than Rice Ball or Cake but instead I get something like:

Brown Rice # First result in Tokeninput
Rice Cake
Yellow Rice
Long Grain Rice
Rice Ball
Chinese Rice
Super Rice
Rice
New Rice
The Super Rice Game

Even if I type in "Rice" exactly I get something similar to the above. I want it to be the 1st choice if r, ri, ric or rice is typed in but my results don't believe in order, they only want chaos. How would this be done?

user2422869 · Accepted Answer · 2014-03-16T11:55:22.740

You may add the following method to your Product model:

def self.good_search(criteria)
  regexp = /#{criteria}/i; # case-insensitive regexp based on your string

  result = order(:name).where("name ILIKE ?", "%#{criteria}%").limit(10)
  result.sort{|x, y| (x =~ regexp) <=> (y =~ regexp) } 
end

Here I use the match operator (=~) which returns position where regexp matched. The earlier the "rice" met the closer to the top of result array string'll be. This method might not be good for big arrays, but for 10 elements it's okay.

And here's your controller:

def token
  respond_to do |format|
    format.html
    format.json { render json: Product.good_search(params[:q]) }
  end
end

Good luck!

score 2 · Answer 2 · answered Mar 16 '14 at 12:24

Full Text Search

We've done something like this - you're looking for full text search

We've done something similar to what you're seeking at http://firststopcosmeticshop.co.uk -- the way we've done it is like this:

#config/routes.rb
match 'search(/:search)', :to => 'products#search', :as => :search, via: [:get, :post]

#app/controllers/products_controller.rb
def search      
    @products = Product.search(params[:search])
    respond_to do |format|
        format.js   { render :partial => "elements/livesearch", :locals => {:search => @products, :query => params[:search]} }
        format.html { render :index }
    end
end

#app/models/product.rb
def self.search(search)
 basic_search(name: search, description: search).take(5) #-> uses textacular gem
end

This uses the textactular gem for PGSQL, but hopefully gives you some idea as to how to load perform the search

Code

For you, I would recommend performing your search like this:

def token
   @products = Product.where(name: "%#{params[:q]}%").order(name: :desc)
   respond_to do |format|
     format.html
     format.json { render json: @products.to_json }
   end
end

Although this won't answer your question directly, I hope it will give you some information on efficiency etc

score 2 · Answer 3 · answered Apr 15 '16 at 18:36

I solved my search with this query and I think that you can use it as well and would make your search results much better. Given a query string as input I used the following sql:

select items.*
from items
where lower(items.name) like 'query%' or items.name like '% query%'
order by case when lower(items.name) like 'query%' then 1 else 2 end,
items.name ASC

this will return all the items which have at least one work matching the result ordered with the ones which starts the match first.

In Rails this would be something like:

Item.where("lower(items.name) like '#{downcase_query}%' or 
            lower(items.name) like '% #{downcase_query}%'").
     order("case when lower(items.name) like '#{downcase_query}%' then 1 else 2 end, 
            items.name ASC")

score 0 · Answer 4 · answered Mar 16 '14 at 12:04

In general, the key in ruby to sort something is by using either .sort or .sort_by

For instance, an example is this:

%w{ banana apple pear fig }.sort_by {|word| word.length} # => ["fig", "pear", "apple", "banana"]

Now in your example, you need to specify which criterium you wish to use.

Given that you want to return Rice first, and then Rice Ball, I would suggest to add a sorting method that preferrentially returns Rice first, then works on the remaining results.

How to order results by closest match to query?

4 Answers4