pg_search: ordering of "multisearchable" results

Question

I'm using Rails 4.2.4 with pg_search 1.0.5.

class Advert < ActiveRecord::Base

  include PgSearch
  multisearchable :against => [:title, :body]

end

I would like to order the pg_search results by the :created_at date of my Advert records. Logically, it seems to me like the following could work (:asc):

> @pgs = PgSearch.multisearch('55948189').order(created_at: :asc)

PgSearch::Document Load (136.1ms)  SELECT "pg_search_documents".* FROM "pg_search_documents" INNER JOIN (SELECT "pg_search_documents"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("pg_search_documents"."content"::text, ''))), (to_tsquery('simple', ''' ' || '55948189' || ' ''')), 0)) AS rank FROM "pg_search_documents" WHERE (((to_tsvector('simple', coalesce("pg_search_documents"."content"::text, ''))) @@ (to_tsquery('simple', ''' ' || '55948189' || ' '''))))) AS pg_search_ce9b9dd18c5c0023f2116f ON "pg_search_documents"."id" = pg_search_ce9b9dd18c5c0023f2116f.pg_search_id  ORDER BY pg_search_ce9b9dd18c5c0023f2116f.rank DESC, "pg_search_documents"."id" ASC, "pg_search_documents"."created_at" ASC
=> #<ActiveRecord::Relation [

#<PgSearch::Document id: 3148, content: "Aleksandra | Tallinn | 55948189 Simpatichnaja i se...", searchable_id: 3148, searchable_type: "Advert", created_at: "2015-10-01 11:44:06", updated_at: "2015-10-01 11:44:30">, 
#<PgSearch::Document id: 3275, content: "Aleksandra | Tallinn | 55948189 Simpatichnaja i se...", searchable_id: 3275, searchable_type: "Advert", created_at: "2015-10-02 11:05:49", updated_at: "2015-10-02 11:28:48">]> 

But then the opposite order (:desc) produces the same results:

> @pgs = PgSearch.multisearch('55948189').order(created_at: :desc)

PgSearch::Document Load (108.4ms)  SELECT "pg_search_documents".* FROM "pg_search_documents" INNER JOIN (SELECT "pg_search_documents"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("pg_search_documents"."content"::text, ''))), (to_tsquery('simple', ''' ' || '55948189' || ' ''')), 0)) AS rank FROM "pg_search_documents" WHERE (((to_tsvector('simple', coalesce("pg_search_documents"."content"::text, ''))) @@ (to_tsquery('simple', ''' ' || '55948189' || ' '''))))) AS pg_search_ce9b9dd18c5c0023f2116f ON "pg_search_documents"."id" = pg_search_ce9b9dd18c5c0023f2116f.pg_search_id  ORDER BY pg_search_ce9b9dd18c5c0023f2116f.rank DESC, "pg_search_documents"."id" ASC, "pg_search_documents"."created_at" DESC
=> #<ActiveRecord::Relation [

#<PgSearch::Document id: 3148, content: "Aleksandra | Tallinn | 55948189 Simpatichnaja i se...", searchable_id: 3148, searchable_type: "Advert", created_at: "2015-10-01 11:44:06", updated_at: "2015-10-01 11:44:30">, 
#<PgSearch::Document id: 3275, content: "Aleksandra | Tallinn | 55948189 Simpatichnaja i se...", searchable_id: 3275, searchable_type: "Advert", created_at: "2015-10-02 11:05:49", updated_at: "2015-10-02 11:28:48">]>

Could anybody explain how I can order my search results by the :created_at date?

UPDATE 1: Is it possible that the order of the results is fixed by pg_search and there isn't any way to order by 'created_at`?

I am reading the documentation more carefully now and found this statement: "By default, pg_search ranks results based on the :tsearch similarity between the searchable text and the query. To use a different ranking algorithm, you can pass a :ranked_by option to pg_search_scope." (see https://github.com/Casecommons/pg_search)

Epizoic

If you're only expecting collections of a single type, it makes sense to use a search scope. Why are you using multisearch then? – Moly

@D-side: When I started this project, I thought I would need to search multiple models. But since I am only searching a single model, a search scope is a good solution. – Epizoic

Answer 1

There is a simple answer but it does not work with "multisearchable".

First, I needed to change from "multisearchable" (which works with multiple models) to "pg_search_scope" (searches in a single model only).

Second, I needed to use :order_within_rank. Since the results are all 100% matches of the search term, they have the same ranking according to pg_search. :order_within_rank gives a secondary rank as a tiebreaker.

class Advert < ActiveRecord::Base

  include PgSearch
  pg_search_scope :search_by_full_advert, 
                  :against => [:title, :body],
                  :order_within_rank => "adverts.created_at DESC"

end

Epizoic

Answer 2

I might be 5 years late to the party, but just stumbled upon same problem and figured out how you can do it.

Short simple answer - convert your query, i.e. PgSearch.multisearch('55948189') to an array.

My example: @results = PgSearch.multisearch(params[:main_search][:query]) While you can't group or sort @results, you can do it with @results.to_a.

In my case I have two models - Products and Manufacturers (Manufacturers produce products) and I wanted to display in my search result at first all Product results and only then all manufacturers results.

First, define an array to sort by:

sorting_order = ["Manufacturer", "Product"]

then

@results.to_a.sort_by { |x| sorting_order.index x.searchable_type }

(Note: serachable_type is the default column of the PgSearch::Document generated by the gem migration)

Tantalus