I use the filterrific gem (currently 5.2.3) a lot for my Rails apps and love it. I have run into a weird problem. I have a secondary Postgres database for my dev team to write to as an additional add-on in Heroku. My filterrific setup works great for all my scope filters in production but the search returns errors. Everything works perfectly in Development with my SQL seed data.
Model.rb
filterrific(
default_filter_params: { },
available_filters: [
:sorted_by,
:with_search_please,
:with_manufacturer,
:with_boot_type,
:with_firm,
:with_monitor_count,
:with_ethernet_count,
],
)
scope :with_search_please, ->(search_string) {
return nil if search_string.blank?
terms = search_string.to_s.downcase.split(/\s+/)
terms = terms.map { |e|
#('%' + e + '%').gsub(/%+/, '%')
('%' + e.gsub('*', '%') + '%').gsub(/%+/, '%')
}
num_or_conds = 6
self.where(
terms.map { |term|
"(
LOWER(Terminals.Model) LIKE ?
OR LOWER(Terminals.TermcapModel) LIKE ?
OR LOWER(Manufacturers.Name) LIKE ?
OR LOWER(TerminalType.Type) LIKE ?
OR LOWER(Note.Description) LIKE ?
OR LOWER(FirmwarePackage.Version) LIKE ?
)"
}.join(' AND '),
*terms.map { |e| [e] * num_or_conds }.flatten
)
.joins(:Manufacturers).references(:ManufacturerIds)
.joins(:TerminalType).references(:TypeIds)
.includes(:Notes).references(:TerminalIds)
.joins(:FirmwarePackages).references(:TerminalFirmwarePackages)
}
Log Error
Completed 500 Internal Server Error in 15ms (ActiveRecord: 3.2ms | Allocations: 6057)
2023-09-07T12:56:11.955033+00:00 app[web.1]: F, [2023-09-07T12:56:11.954949 #2] FATAL -- : [9e6cf5c0-457c-47aa-9099-a5923e41b1ce]
2023-09-07T12:56:11.955035+00:00 app[web.1]: [9e6cf5c0-457c-47aa-9099-a5923e41b1ce] ActionView::Template::Error (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "terminals"
2023-09-07T12:56:11.955035+00:00 app[web.1]: LINE 2: LOWER(Terminals.Model) LIKE '%a%'
2023-09-07T12:56:11.955036+00:00 app[web.1]: ^
I have tried several ways of rewriting the search query, stripping out conditions and even just a straight SQL query. This is particularly difficult as everything works flawlessly in Development and all the filtering scopes work in Production. I know it must have to do with the secondary add-on db as I have never run in to this before.
Your issue is that the "terminals" table is not part of the query.
Also please note Postgres double quoted identifiers are case sensitive e.g. "Terminals" v "terminals".
If you post the entire query as well as some context as to where this code block is defined we may be able to assist with that.
All that being said I would also like to take the opportunity to help you clean up that query a bit as it seems very awkward, IMO.
From the post it appears that your intent is to find: "A Record where every word in the search string is contained in one of the following columns Terminals.Model, Terminals.TermcapModel, Manufacturers.Name, TerminalType.Type, Note.Description, FirmwarePackage.Version".
We can achieve this in a simpler fashion (and possibly solve your issue in the process) as follows:
scope :with_search_please, ->(search_string) {
return none if search_string.blank? # A scope should not return nil
search_columns = [
Terminal.arel_table[:Model],
Terminal.arel_table[:TermcapModel],
Manufacturer.arel_table[:Name],
TerminalType.arel_table[:Type],
Note.arel_table[:Description],
FirmwarePackage.arel_table[:Version]
]
terms = search_string.to_s
.gsub("*","%")
.split
.map {|term| "%#{term}%"}
where(search_columns.map {|c| c.matches_any(terms)}.reduce(:and))
.joins(:Manufacturers)
.joins(:TerminalType)
.left_joins(:Notes)
.joins(:FirmwarePackages)
}
As an example, with a search_string of "A Sun*ny Day" this will result in a WHERE CLAUSE similar to
(Terminals.Model ILIKE '%A%' OR Terminals.Model ILIKE '%Sun%ny%' OR Terminals.Model ILIKE '%Day%') AND
(Terminals.TermcapModel ILIKE '%A%' OR Terminals.TermcapModel ILIKE '%Sun%ny%' OR Terminals.TermcapModel ILIKE '%Day%') AND
(Manufacturers.Name ILIKE '%A%' OR Manufacturers.Name ILIKE '%Sun%ny%' OR Manufacturers.Name ILIKE '%Day%') AND
(TerminalType.Type ILIKE '%A%' OR TerminalType.Type ILIKE '%Sun%ny%' OR TerminalType.Type ILIKE '%Day%') AND
(Note.Description ILIKE '%A%' OR Note.Description ILIKE '%Sun%ny%' OR Note.Description ILIKE '%Day%') AND
(FirmwarePackage.Version ILIKE '%A%' OR FirmwarePackage.Version ILIKE '%Sun%ny%' OR FirmwarePackage.Version ILIKE '%Day%')
We could take this a bit further with Full Text Search as well.