How to group a complex pg_search query

This is a complex problem to tackle. Let's start with a bit of a background...

It all started with Prosopite telling me that running a count or a sum on a customer's invoices while looping through all customers is a bad practice. N+1 warning!

At this point, I do what any overachieving optimizer does: I set out to fix the problem!

My first attempt was something like this:

module Bills
  class Customer < ApplicationRecord
    include PgSearch::Model

    has_many :invoices, dependent: :destroy

    scope :with_invoice_totals, -> {
      left_joins(:invoices)
        .select("bills_customers.*")
        .select("COALESCE(SUM(bills_invoices.total_gross_cents), 0) AS total_invoiced_gross_cents")
        .select("COALESCE(SUM(bills_invoices.total_net_cents), 0) AS total_invoiced_net_cents")
        .select("COALESCE(SUM(bills_invoices.total_vat_cents), 0) AS total_invoiced_vat_cents")
        .group('"bills_customers"."id"')
    }

So basically, I select the invoice totals as part of the query, which kills the N+1 warning until... I try to combine this with searching for a customer. The horror!!

ActiveRecord::StatementInvalid - PG::GroupingError: ERROR:  column "pg_search_786fed5704f7001547079e.rank" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..." = 1))) GROUP BY "bills_customers"."id" ORDER BY pg_search_...

This was vaguely familiar; I remember running into this in the past and never having the time to look into it in detail.

So, let's take a look at our search scopes:

module Bills
  class Customer < ApplicationRecord
    include PgSearch::Model

    has_many :invoices, dependent: :destroy

    scope :with_invoice_totals, -> {
      left_joins(:invoices)
        .select("bills_customers.*")
        .select("COALESCE(SUM(bills_invoices.total_gross_cents), 0) AS total_invoiced_gross_cents")
        .select("COALESCE(SUM(bills_invoices.total_net_cents), 0) AS total_invoiced_net_cents")
        .select("COALESCE(SUM(bills_invoices.total_vat_cents), 0) AS total_invoiced_vat_cents")
        .group('"bills_customers"."id"')
    }

    pg_search_scope :customer_searchable,
      against: {
        company: "A",
        first_name: "B",
        last_name: "C",
        vat_number: "D",
      },
      using: {
        tsearch: {
          prefix: true,
          dictionary: "english",
          tsvector_column: "customer_searchable",
        },
      }

    pg_search_scope :address_searchable,
      against: {
        street_1: "A",
        postal_code: "B",
        city: "C",
        country: "D",
      },
      using: {
        tsearch: {
          prefix: true,
          dictionary: "english",
          tsvector_column: "address_searchable",
        },
      }

    pg_search_scope :search_customer_and_address,
      against: {
        company: "A",
        first_name: "B",
        last_name: "C",
        vat_number: "D",
        street_1: "B",
        postal_code: "C",
        city: "C",
        country_code: "D",
      },
      using: {
        tsearch: {
          prefix: true,
          dictionary: "english",
          tsvector_column: %w[customer_searchable address_searchable],
        },
      }

To make things even more complicated, and due to limitations in pg_search and tsvector columns, I need to combine two search scopes to search for everything.

I came across this issue: https://github.com/Casecommons/pg_search/issues/367 which led me to this issue: https://github.com/Casecommons/pg_search/issues/238 which finally led me to: https://github.com/Casecommons/pg_search/blob/v1.0.5/lib/pg_search/scope_options.rb#L34C11-L34C30.

So, adding with_pg_search_rank needs to be done in the model so we need another method to combine everything:

def self.search_and_aggregate(query)
  base_query = with_invoice_totals
  return base_query if query.blank?

  base_query
    .search_customer_and_address(query)
    .with_pg_search_rank
    .group("pg_search_rank")

Now we can use this from the controller like so:

module Bills
  class CustomersController < CrewScopedController
    def index
      query = params[:query]
      customers = Current.crew.invoices.where(user: Current.user) 
      customers = customers.search_and_aggregate(query)

      respond_to do |format|
        format.html do
          render Customers::Index.new(customers:, query:)
        end
        format.turbo_stream do
          component = Customers::Customers.new(customers:)

          render turbo_stream: turbo_stream.replace(
            component.id,
            component
          )
        end
      end
    end
  end
end

The cool thing about Ruby on Rails is that you can do as many group , select , and where you please. Rails appends every single one to the list. If you want to remove the existing ones, you must use regroup , reselect , or rewhere: https://guides.rubyonrails.org/active_record_querying.html#overriding-conditions

Related: more PostgreSQL in Rails: Using UUIDv7 with Ruby on Rails.