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;
Results
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;
Results
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;
Results
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)
Results
["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)
Results
["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".)
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