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)
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