Add a custom filter after paginate (or any other solution) ?

Hi everyone ! I'm using Laravel 11 with Inertia-Vue. I have invoices with payments attached.

invoices
|_ id
|_ client_id
|_ date
|_ amount

payments
|_ id
|_ invoice_id
|_ is_settled
|_ amount

To check if an invoice is paid I have to add all settled payments and compare to the invoice amount. I made the accessor 'balance' for that. I can't store this status in the database because it can vary over time (payment refused, negative payment added as refund, etc.)

Now I have to show a paginated list of these invoices with filtering including the status. My problem is that I can't add the status in the SQL query because the column doesn't exist in the table and if I process a filtering afterward on the collection I destroy the pagination system.

Here is the model code :

/**
  * Model Invoice
  */

class Invoice extends Model
{
    use HasFactory;

    protected $appends = ['balance'];

    // custom accessor
    protected function balance(): Attribute
    {
        return Attribute::make(get: fn() => $this->amount - $this->payments->filter(fn($p) => $p->is_settled)->sum(fn($p) => $p->amount));
    }

    // filtering function
    public function scopeFilter($query, array $filters)
    {
        $query->when(
            $filters['client_id'] ?? false,
            fn($query, $search) => $query->where('client_id', $search)
        )->when(
            $filters['date_range'] ?? false,
            fn($query, $search) => $query->where('date', '>=', $search[0])->where('date', '<=', $search[1])
        );
    }
}

And here is the controller :

class InvoiceController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
       
        $invoices = Invoice::with(['client'])->filter($request->all())->orderBy('date', 'desc')->paginate(20);

        // THIS CODE BELLOW DISABLE/BREAK THE PAGINATION
        if ($request->has('status') && $request->status == 'paid') {
            $invoices = $invoices ->filter(fn($invoice) => $invoice->balance == 0);
        }

        return Inertia::render('Invoice/Index', [
            'invoices' => $invoices,
            'clients' => Client::getList(),
        ]);
    }
}

Do you have any suggestion to meet this need?

Pierre_AIR

1 year ago

Best Answer

Level 1

A solution was to avoid using the paginate function of eloquent but rather to retrieve the SQL result in a Collection, to apply a custom filter and then to use a LengthAwarePaginator with finally a call to toArray to obtain the same result as before:

class InvoiceController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
       // Call get to have a Collection
        $invoices = Invoice::with(['client'])->filter($request->all())->orderBy('date', 'desc')->get();

        // Apply the custom filter
        if ($request->has('status') && $request->status == 'paid') {
            $invoices = $invoices ->filter(fn($invoice) => $invoice->balance == 0);
        }

		// Manually apply the paginator
		$paginatedInvoices = new LengthAwarePaginator($invoices->forPage(request('page'), 20), $invoices->count(), 20, request('page'), ['path' => URL::current()]);

		// Call toArray() function to get (nearly) the same result as with eloquent
        return Inertia::render('Invoice/Index', [
            'invoices' => $paginatedInvoices->toArray(),
            'clients' => Client::getList(),
        ]);
    }
}

Beware of this difference: with eloquent the list in data is an Array while it is an Object with the custom paginator (this is not treated the same way in Vue in the end):

{
	"invoices": {
		"current_page": 1,
		// With paginate of Eloquent : data is an Array
		"data": [
			{ ... },
			{ ... },
		],
		// With LengthAwarePaginator : data is an Object
		"data": {
			{ ... },
			{ ... },
		},
		"first_page_url": "http://localhost:8000/invoices/index?page=1",
		"from": 1,
		"last_page": 3,
		"last_page_url": "http://localhost:8000/invoices/index?page=3",
		"links": [ ... ],
		"next_page_url": "http://localhost:8000/invoices/index?page=2",
		"path": "http://localhost:8000/invoices/index",
		"per_page": 20,
		"prev_page_url": null,
		"to": 20,
		"total": 56
	}
}

sylar

1 year ago

Level 32

I would suggest

Invoice::query()->where('amount', '=', function ($q) {
          $q->selectRaw('sum(amount)')->from('payments')->where('is_settled', 1)->whereColumn('invoice_id', 'invoices.id');
   })

or in raw sql select * from "invoices" where "amount" = (select sum(amount) from "payments" where "is_settled" = 1 and "invoice_id" = "invoices"."id")

So instead of subtracting all payments from the amount you can check if the sum of them is == to the amount :)

P.S. You can simplify the status check with just $request->get('status') == 'paid'

Pierre_AIR

1 year ago

Level 1

Thanks a lot @sylar ! That's a good suggestion indeed, the only con I see is that it is out of Eloquent mechanism. +1 for the ending tip ;-)

Please or to participate in this conversation.