I want to do an autocomplete with Ruby and Sequel

Question

I am using Sequel with prostgres and Sinatra. I want to do an autocomplete search. I’ve verified my jQuery which sends a GET works fine.

The Ruby code is:

get '/search' do
  search = params[:search]
  DB[:candidates].select(:last).where('last LIKE ?', '_a_').each do |row|
    l = row[:last] 
  end
end

The problem is the Sequel query:

I have tried every possible configuration of the query that I can think of with no luck.

So, for example, in the above query I get all the people who have "a" in their last name but when I change the query to:

DB[:candidates].select(:last).where('last LIKE ?', 'search')

or

DB[:candidates].select(:last).where('last LIKE ?', search)  # (without '')

I get nothing.

I have done warn params.inspect which indicates the param search is being passed, so I am stuck.

Any ideas how the query should be written?

Finally, the second part of the question the results (when it works with '_a_') are rendered as {:last=>"Yao"} I would like just Yao, how can I do that?

I have tried numerous different types of query including raw SQL but no luck. Or is the approach just plain wrong?

Answer 1

Just installed Sequel and made working example:

require "rubygems"
require "sequel"

# connect to an in-memory database
DB = Sequel.sqlite

# create an items table
DB.create_table :items do
  primary_key :id
  String :name
  Float :price
end

# create a dataset from the items table
items = DB[:items]

# populate the table
items.insert(:name => 'abc', :price => rand * 100)
items.insert(:name => 'def', :price => rand * 100)
items.insert(:name => 'ghi', :price => rand * 100)
items.insert(:name => 'gui', :price => rand * 100)

# print out the number of records
puts "Item count: #{items.count}"

# print out the average price
puts "The average price is: #{items.avg(:price)}"

recs = items.select(:name).where(Sequel.like(:name, 'g%'))
recs.each do |rec|
  puts rec.values
end

I think you will get the point.

UPDATED

So in your case you should try this:

DB[:candidates]
   .select(:last)
   .where(Sequel.like(:last, "#{search}%"))
   .map{|rec| rec.values}.flatten

It should return array of found strings.

Answer 2

Copy/pasting from the Sequel documentation:

You can search SQL strings in a case sensitive manner using the Sequel.like method:

items.where(Sequel.like(:name, 'Acme%')).sql
#=> "SELECT * FROM items WHERE (name LIKE 'Acme%')"

You can search SQL strings in a case insensitive manner using the Sequel.ilike method:

items.where(Sequel.ilike(:name, 'Acme%')).sql
#=> "SELECT * FROM items WHERE (name ILIKE 'Acme%')"

You can specify a Regexp as a like argument, but this will probably only work on PostgreSQL and MySQL:

items.where(Sequel.like(:name, /Acme.*/)).sql
#=> "SELECT * FROM items WHERE (name ~ 'Acme.*')"

Like can also take more than one argument:

items.where(Sequel.like(:name, 'Acme%', /Beta.*/)).sql
#=> "SELECT * FROM items WHERE ((name LIKE 'Acme%') OR (name ~ 'Beta.*'))"

Open up a Sequel console (not your Sinatra app) and play with the query until you get results back. Since you say you want only the last column your query should be something like:

# Search anywhere inside the last name
DB[:candidates].where( Sequel.ilike(:last, "%#{search}%") ).select_map(:last)

# Find last names starting with the search string
DB[:candidates].where( Sequel.ilike(:last, "#{search}%") ).select_map(:last)

Uglier alternatives:

DB[:candidates]
  .select(:last)
  .where( Sequel.ilike(:last, "%#{search}%") )
  .all
  .map{ |hash| hash[:last] }

DB[:candidates]
  .select(:last)
  .where( Sequel.ilike(:last, "%#{search}%") )
  .map( :last )

---

If you want to rank the search results by the best matches, you might be interested in my free LiqrrdMetal library. Instead of searching on the DB, you would pull a full list of all last names into Ruby and use LiqrrdMetal to search through them. This would allow a search string of "pho" to match both "Phong" as well as "Phrogz", with the former scoring higher in the rankings.