backpack listing operation for very big table using Deferred Joins

FastPaginationOperation.php

<?php
/**
 * Created by PhpStorm.
 * User: Hiệp Nguyễn
 * Date: 27/07/2022
 * Time: 15:43
 *
 * we don't care about exactly total records, just pagination as page by page
 * see https://github.com/hammerstonedev/fast-paginate
 */


namespace App\Http\Controllers\Admin\Operations;


use Backpack\CRUD\app\Http\Controllers\Operations\ListOperation;

trait FastPaginationOperation
{
    use ListOperation;

    public function search()
    {
        $this->crud->hasAccessOrFail('list');
        $offset = (int)request()->input('start');
        $limit  = (int)request()->input('length');

        $filters = $this->crud->filters();
        //ignore filters not active
        $active_filter = $filters->reject(function ($filter) {
            return !$filter->isActive();
        });

        $this->crud->applyUnappliedFilters();
        $totalRows = $this->getTotalRowsInPageRange($this->crud->model::query(),$offset, $limit);

        if ($active_filter->count() > 0) {
            $filteredRows = $this->getTotalRowsInPageRange($this->crud->query->clone()->toBase(),$offset, $limit);
        } else {
            $filteredRows = $totalRows;
        }
        $startIndex = $offset ?: 0;
        // if a search term was present
        if (request()->input('search') && request()->input('search')['value']) {
            // filter the results accordingly
            $this->crud->applySearchTerm(request()->input('search')['value']);
            // recalculate the number of filtered rows
            $filteredRows = $this->crud->where("id", ">", 0)->count();
        }
        // start the results according to the datatables pagination
        if ($offset) {
            $this->crud->skip($offset);
        }
        // limit the number of results according to the datatables pagination
        if ($limit) {
            $this->crud->take($limit);
        }
        // overwrite any order set in the setup() method with the datatables order
        if (request()->input('order')) {
            // clear any past orderBy rules
            $this->crud->query->getQuery()->orders = null;
            foreach ((array)request()->input('order') as $order) {
                $column_number    = (int)$order['column'];
                $column_direction = (strtolower((string)$order['dir']) === 'asc' ? 'ASC' : 'DESC');
                $column           = $this->crud->findColumnById($column_number);
                if ($column['tableColumn'] && !isset($column['orderLogic'])) {
                    // apply the current orderBy rules
                    $this->crud->orderByWithPrefix($column['name'], $column_direction);
                }

                // check for custom order logic in the column definition
                if (isset($column['orderLogic'])) {
                    $this->crud->customOrderBy($column, $column_direction);
                }
            }
        }

        // show newest items first, by default (if no order has been set for the primary column)
        // if there was no order set, this will be the only one
        // if there was an order set, this will be the last one (after all others were applied)
        // Note to self: `toBase()` returns also the orders contained in global scopes, while `getQuery()` don't.
        $orderBy = $this->crud->query->toBase()->orders;
        $table   = $this->crud->model->getTable();
        $key     = $this->crud->model->getKeyName();

        $hasOrderByPrimaryKey = collect($orderBy)->some(function ($item) use ($key, $table) {
            return (isset($item['column']) && $item['column'] === $key)
                || (isset($item['sql']) && str_contains($item['sql'], "$table.$key"));
        });

        if (!$hasOrderByPrimaryKey) {
            $this->crud->orderByWithPrefix($this->crud->model->getKeyName(), 'DESC');
        }

        $deferred_joins = $this->crud->query->clone()->select("{$key} AS deferred_{$key}");

        $entries = $this->crud->model::query()
            ->select('*')
            ->from($table)
            ->joinSub($deferred_joins, 'deferred_joins', function ($join) use ($key, $table) {
                $join->on("{$table}.{$key}", "=", "deferred_joins.deferred_{$key}");
            })
            ->get();
        return $this->crud->getEntriesAsJsonForDatatables($entries, $totalRows, $filteredRows, $startIndex);
    }

    final public function getTotalRowsInPageRange($builder,int $offset, int $limit): int
    {
        $existing_more_pages =  $builder->selectRaw('1')
            ->where("id", ">", "0")
            ->limit($limit)
            ->offset($offset + 1)
            ->get();
        if ($existing_more_pages->count() > 0) {
            return $offset + $limit + 1;
        }
        return $limit + $offset;
    }

}