Why is Filterrific Search in Production on Heroku add-on db not working?

Question

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.

Answer 1

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.