How to configure pg_search to sort on a column when returning a full text search result on a merged model?


I’m using GitHub - Casecommons/pg_search: pg_search builds ActiveRecord named scopes that take advantage of PostgreSQL’s full text search and I have a whole bunch of things working but I can’t quite figure out how to handle a case where I’m using .merge() to full text search another model’s attribute while also sorting at the same time.

I want to avoid associated_against at all costs because it mentions indexes won’t be used and it’s only meant to be a quick way to do cross model searching, quick as in getting it to work, not performance.

Here’s the set up:

  • There’s a User and LoginActivity model
  • User has a display_name which is full text searchable

The User model has pg search configured like this:

  pg_search_scope :pg_search,
    using: {
      tsearch: {
        dictionary: "english", tsvector_column: "searchable",
    order_within_rank: "users.created_at DESC"

Then I have a search_and_sort(query, sort, direction, allow_sort: true) method on that model.

The important part of this method is:

q = pg_search(query)
q = q.with_pg_search_rank.reorder("pg_search_rank DESC") if allow_sort

When performing FTS on my User model everything is perfect. I can sort on different columns with or without FTS results. It works as expected.

But now I have a LoginActivity model which doesn’t have any pg_search related fields.

Imagine having an admin dashboard where you want to list all login activities but it allows you to FTS the user’s display name.

Like the User model, the LoginActivity has its own search_and_sort(query, sort, direction, allow_sort: true) method.

The important part of that method is this snippet where I perform a FTS:

allow_sort = false
relation = joins(:user).merge(User.search_and_sort(query, sort, direction, allow_sort:))

The way I have it set up now ignores sorting while using FTS because if I try to sort when performing a FTS then I get this error: PG::UndefinedColumn: ERROR: column "pg_search_rank" does not exist, if I filter results without using full text search, even when using .merge() then everything works, such as looking a user up by their email address directly. This indicates there’s an issue related to how I’m using pg_search or pg_search itself.

Within the User model’s method, there’s q.with_pg_search_rank.reorder("pg_search_rank DESC"), that pg_search_rank column is created by the pg_search gem. That’s located here: pg_search/lib/pg_search/scope_options.rb at 2e702053e6b99b02f6985bb5cb24e1e1ad6f0eed · Casecommons/pg_search · GitHub

I don’t fully understand the problem but I sort of know why it’s not working. The LoginActivity model doesn’t have that column generated for it but I don’t know why that’s not being applied to the User model.

Long story short, I’ve exhausted my options on where to go next to get this to work. Does anyone have any leads?

I’m not married to using .merge() here, but it was the only way I got things to work for what I’m trying to do. I get back a list of LoginActivity models while being able to filter the results based on another model.