Autocomplete via Sphinx

using Rails & Select2

Nasos Psarrakos / psarrakos@gmail.com / nasospsa

The problem:

Searching for 'chick'en..

Food table, 1000 chickens → no pretty results

Example

Search Term: "chick"

SELECT long_desc FROM foods
WHERE long_desc LIKE '%CHICK%'
LIMIT 7;

long_desc
----------------------------------------------------------------------------------------
 Chicken, gizzard, all classes, cooked, simmered
 Chicken, broiler, rotisserie, BBQ, breast meat only
 Chicken, broilers or fryers, meat and skin and giblets and neck, raw
 Chicken, broilers or fryers, meat and skin and giblets and neck, cooked, fried, batter
 Chicken, broilers or fryers, meat and skin and giblets and neck, cooked, fried, flour
 Chicken, broilers or fryers, meat and skin and giblets and neck, roasted
 Chicken, broilers or fryers, meat and skin and giblets and neck, stewed

Possible Solution #1

postgresql :: Full Text Search

Example with "chick"

SELECT long_desc, ts_rank_cd(to_tsvector(long_desc), query) AS rank
FROM foods, to_tsquery('chick') query
WHERE to_tsvector(long_desc) @@ query
ORDER BY rank DESC
LIMIT 7;

           long_desc            | rank
--------------------------------+------
 CHICK-FIL-A, Chick-n-Strips    |  0.2
 SMART SOUP, Moroccan Chick Pea |  0.1
 CHICK-FIL-A, hash browns       |  0.1
						

Example with "chicken"

SELECT long_desc, ts_rank_cd(to_tsvector(long_desc), query) AS rank
FROM foods, to_tsquery('chicken') query
WHERE to_tsvector(long_desc) @@ query
ORDER BY rank DESC
LIMIT 7;

                                       long_desc                                        | rank
----------------------------------------------------------------------------------------+------
 SUPPER BAKES MEAL KITS, Garlic Chicken with pasta (chicken not included)               |  0.2
 SUPPER BAKES MEAL KITS, Lemon Chicken with herb rice (chicken not included)            |  0.2
 CAMPBELL'S Red and White, GOLDFISH Pasta with Chicken in Chicken Broth, condensed      |  0.2
 SUPPER BAKES MEAL KITS, Cheesy Chicken with pasta (chicken not included)               |  0.2
 SUPPER BAKES MEAL KITS, Herb Chicken with rice (chicken not included)                  |  0.2
 SUPPER BAKES MEAL KITS, Traditional Roast Chicken with stuffing (chicken not included) |  0.2
 SUPPER BAKES MEAL KITS, Southwestern-Style Chicken w/rice (chicken not included)       |  0.2
						

Possible Solution #2

Sphinx via thinking-sphinx

Thinking Sphinx Index Example

ThinkingSphinx::Index.define :food, :with => :real_time do
  indexes long_desc, {
    sortable: true,
    enable_star: true,
    min_infix_len: 3
  }
end

Searching..

Food.search('chick*', limit: 10).map(&:long_desc)

["CAMPBELL'S Red and White, GOLDFISH Pasta with Chicken in Chicken Broth, condensed",
 "CHICK-FIL-A, Chick-n-Strips",
 "SUPPER BAKES MEAL KITS, Cheesy Chicken with pasta (chicken not included)",
 "SUPPER BAKES MEAL KITS, Garlic Chicken with pasta (chicken not included)",
 "SUPPER BAKES MEAL KITS, Herb Chicken with rice (chicken not included)",
 "SUPPER BAKES MEAL KITS, Lemon Chicken with herb rice (chicken not included)",
 "SUPPER BAKES MEAL KITS, Southwestern-Style Chicken w/rice (chicken not included)",
 "SUPPER BAKES MEAL KITS, Traditional Roast Chicken with stuffing (chicken not included)",
 "Babyfood, meat, chicken, strained",
 "Babyfood, meat, chicken, junior"]
						

Searching..#2

Food.search('chick*', {
  match_mode: :extended,
  ranker: :SPH04,
  limit: 10
}).map(&:long_desc)

["CHICK-FIL-A, Chick-n-Strips",
 "Chicken, broiler, rotisserie, BBQ, breast meat only",
 "Chicken, broilers or fryers, meat and skin and giblets and neck, raw",
 "Chicken, broilers or fryers, meat and skin and giblets and neck, cooked, fried, batter",
 "Chicken, broilers or fryers, meat and skin and giblets and neck, cooked, fried, flour",
 "Chicken, broilers or fryers, meat and skin and giblets and neck, roasted",
 "Chicken, broilers or fryers, meat and skin and giblets and neck, stewed",
 "Chicken, broilers or fryers, meat and skin, raw",
 "Chicken, broilers or fryers, meat and skin, cooked, fried, batter",
 "Chicken, broilers or fryers, meat and skin, cooked, fried, flour"]
						

Ranking

SPH_RANK_SPH04, added in version 1.10-beta, is generally based on the default SPH_RANK_PROXIMITY_BM25 ranker, but additionally boosts the matches when they occur in the very beginning or the very end of a text field. Thus, if a field equals the exact query, SPH04 should rank it higher than a field that contains the exact query but is not equal to it. (For instance, when the query is "Hyde Park", a document entitled "Hyde Park" should be ranked higher than a one entitled "Hyde Park, London" or "The Hyde Park Cafe".)

http://sphinxsearch.com/docs/current.html#builtin-rankers

New Class to hold custom attributes

class FoodSphinx < ActiveRecord::Base
  after_save ThinkingSphinx::RealTime.callback_for(:food_sphinx)

  has_many :meal_foods, foreign_key: 'foodID', primary_key: 'ndb_no'

  def is_popular
    ...
  end
end

New Index as well

ThinkingSphinx::Index.define :food_sphinx, :with => :real_time do
  indexes long_desc, sortable: true, enable_star: true, min_infix_len: 3

  has ndb_no, type: :string

  #If people have made meals with that food
  has meal_foods.count, as: 'mf_count', type: :integer

  has is_popular, as: 'popular', type: :boolean
end

The Search

term = 'chick'
FoodSphinx.search(
  "(^#{term}$) | (^#{term}*) | (#{term}*)  | (#{term})",
  match_mode: :extended,
  ranker: :SPH04,
  select: '*, weight() as w',
  order: 'mf_count desc, popular desc, w desc'
)

The JS part

this.$food.select2({
  ...
  sorter: function(data) {
    var firsts = [],
        rest = [];

    _(data).each(function(r) {
      (collection.get(r.id)) ? firsts.push(r) : rest.push(r);
    });

    return firsts.concat(rest);
  },
  ...
});

The near future

ThinkingSphinx::Index.define :food_sphinx, :with => :real_time do
  indexes long_desc, sortable: true, enable_star: true, min_infix_len: 3

  has ndb_no, type: :string

  #If people have made meals with that food
  has meal_foods.count, as: 'mf_count', type: :integer

  has is_popular, as: 'popular', type: :boolean

  ###
  has is_used_by_me
  has times_used_by_me
  has is_created_by_me
  has is_used_by_my_network
  ###

end

Questions

thank you