CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4full-text-search
Kathan
Kathan

Reputation: 1458

PG Search not Returning Exact Matches First - Rails 4

I am using PgSearch on my Rails 4 app. Been stuck trying to figure out how to boost exact matches when returning search results.

Currently, if a user searches for "coke"... "coke zero" will be displayed first... followed by "coke". I would like "coke" to be first.

Here is my code:

include PgSearch
pg_search_scope :search, :against => { :specific => 'A', :title => 'B', :aka => 'C'},
                         :using => { dmetaphone: {}, tsearch: { dictionary: 'english' }, 
                                     trigram: {:threshold => 0.3} },
                         ignoring: :accents

Any help is much appreciated.

Upvotes: 1

Views: 685

Answers (3)

Spark.Bao
Spark.Bao

Reputation: 5743

you can try "normalization: 2" as well: https://github.com/Casecommons/pg_search#normalization

(post here just for some other guys meet the same problem as me but not caused by ordering by updated_at default)

an example:

  pg_search_scope :search_by_name,
                  against: :name,
                  using: { tsearch: { prefix: true }}

[22] pry(main)> Category.search_by_name('Los Angeles').pluck(:name)
   (3.7ms)  SELECT "categories"."name" FROM "categories" INNER JOIN (SELECT "categories"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("categories"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'Los' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'Angeles' || ' ''' || ':*')), 0)) AS rank FROM "categories" WHERE (((to_tsvector('simple', coalesce("categories"."name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'Los' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'Angeles' || ' ''' || ':*'))))) AS pg_search_a6216ea03e578f212dd604 ON "categories"."id" = pg_search_a6216ea03e578f212dd604.pg_search_id ORDER BY pg_search_a6216ea03e578f212dd604.rank DESC, "categories"."id" ASC
=> ["Los Angeles Angels",
 "Los Angeles Lakers",
 "Los Angeles angels",
 "Los Angeles Dodgers",
 "Los Angeles Rams",
 "Los Angeles",
 "Los Angeles Clippers"]

——

  include PgSearch
  pg_search_scope :search_by_name,
                  against: :name,
                  using: { tsearch: { prefix: true, normalization: 2 }}

[20] pry(main)> Category.search_by_name('Los Angeles').pluck(:name)
   (3.4ms)  SELECT "categories"."name" FROM "categories" INNER JOIN (SELECT "categories"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("categories"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'Los' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'Angeles' || ' ''' || ':*')), 2)) AS rank FROM "categories" WHERE (((to_tsvector('simple', coalesce("categories"."name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'Los' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'Angeles' || ' ''' || ':*'))))) AS pg_search_a6216ea03e578f212dd604 ON "categories"."id" = pg_search_a6216ea03e578f212dd604.pg_search_id ORDER BY pg_search_a6216ea03e578f212dd604.rank DESC, "categories"."id" ASC
=> ["Los Angeles",
 "Los Angeles Angels",
 "Los Angeles Lakers",
 "Los Angeles angels",
 "Los Angeles Dodgers",
 "Los Angeles Rams",
 "Los Angeles Clippers"]

Upvotes: 2

Kathan
Kathan

Reputation: 1458

So, I ended up overlooking my default scope - which was ordering by updated_at. After I removed that, it worked perfectly...

Upvotes: 0

sadaf2605
sadaf2605

Reputation: 7540

You can try sorting by pg_search_rank:

@beverages = Beverage.search("Coke").with_pg_search_rank
@beverages = @beverages.sort_by {|b| b.pg_search_rank}

Upvotes: 1

Related Questions