Hello,
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
andLoginActivity
model User
has adisplay_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.