What's the best way to integrate pagy_search in to my index if I'm already using Pagy? In my controller I currently have:

def index

    @gold_drivers = Driver.gold_plan
    @silver_drivers = Driver.silver_plan
    @free_drivers = Driver.free_plan

    @pagy, @drivers = pagy_array(
      @gold_drivers   @silver_drivers   @free_drivers,
      page: params[:page],
      items: 16
    )

    if params[:query].present?
      @drivers = @gold_drivers   @silver_drivers   @free_drivers
      drivers_search = Driver.pagy_search(params[:query])
      @pagy, @drivers = pagy_meilisearch(drivers_search, items: 25, page: params[:page])
    end
end

I have a Driver model which delegate's to a Profile model. Unfortunately when I click search I get

Driver#first_name delegated to profile.first_name, but profile is nil: #<Driver id: 224... as I know this driver does not have a profile, but the query run for @gold_drivers = Driver.gold_plan / @silver_drivers = Driver.silver_plan etc should not select a driver without a profile.

The query behind Driver.gold_plan is:

find_by_sql("SELECT drivers.*, (profiles.no_races   profiles.no_poles   profiles.no_podiums   profiles.no_wins) AS scores FROM drivers INNER JOIN profiles ON profiles.driver_id = drivers.id INNER JOIN subscriptions ON subscriptions.driver_id = drivers.id WHERE subscriptions.status = 'active' AND profiles.first_name != null OR profiles.first_name != '' AND subscriptions.stripe_plan = '#{ENV["GOLD_ANNUAL"]}' OR subscriptions.stripe_plan = '#{ENV["GOLD_MONTH"]}' GROUP BY scores, drivers.id ORDER BY scores DESC")

In that query I'm using "AND profiles.first_name != null OR profiles.first_name != ''" which works in the pagy_page section - but not in the pagy_search section.

Any direction with this is appreciated!

CodePudding user response：

First of all: your code is extremely inefficient because:

1. It does 3 queries instead of 1
2. It pulls all the records into memory (potentially crashing your server) and joins them into an array using yet more memory to create another possibly huge object
3. It uses pagy_array, that should never be used that way

So let's start to change that.

1. Do not use but create a single scope that ORs the 3 scopes (gold, silver, plan) together (that should be AR relations too, not literal queries). That will get you a single AR Relation not loading anything into memory yet
2. Use the AR Relation directly with pagy instead of pagy_array: that will pull only the page of records that you are going to need, not the whole bunch of records that you don't need
3. Use the same AR Relation for pagy_meilisearch and your problem will probably disappear without further changes. If it won't, then look into your scope.