mhenrixon | How to group a complex pg

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:

lang-ruby
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:

lang-ruby
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:

lang-ruby
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

How to group a complex pg_search query