How to fix "laravel pagination NOT updating on search"

Question

I'm making a table of orders that have a specific current step in this case the step is 'NESTEN' and some other where statements to get the orders back that I need.

Because I don't want a list with more than 400 orders on 1 page I want to use laravels pagination functionality that also give a performance boost on the list of more than 100.000 records. The laravel pagination works as is should but the problem comes when I want to use the filter I made.

I made a dropdown list that needs to filter based on material of the orders that are in the list. In the table I see the filtered orders but the pagination has the same page count and order count as before. So to the point, the problem is that the pagination is not updating after filtering the collection from the query.

What I already tried was some googling and found several solutions that didn't solve my problem or even caused more problems...

Also added a $orders->filter function to remove orders that don't meet up the filter requirements but no result...

To keep it understandable for now I added the code in the Route file.

My route is as followed

Route::get('orders/nesten', function(Request $request) {
    $orders = ShopOrder::where([
        ['ItemCode', 'LIKE', 'CM%'],
        ['Exact', '=', null],
        ['Nesting', '=', null],
        ['IsOnHold', '!=', 1],
        ['ShopOrderRoutingStepPlanCount', '!=', 0]
    ])->paginate(50);

    $filteredCollection = $orders->filter(function ($order) use($request) {
        if($request->exists('material')) {
            return $order->getCurrentStep() == 'Nesten' 
                   && $order->getMaterial() == $request->get('material');
        }
        return $order->getCurrentStep() == 'Nesten';
    });

    $orders->setCollection($filteredCollection);
    return view('dashboard/actions/Nesten')->with('shopOrders', $orders);
});

In the ShopOrder Model I declared the functions ->getMaterial() and ->getCurrentStep() as

    public function routingStepPlans() {
        return $this->hasMany('App\Models\Exact\ShopOrder\RoutingStepPlan', 'ShopOrder', 'ID');
    }

    public function materialPlans() {
        return $this->hasMany('App\Models\Exact\ShopOrder\MaterialPlan', 'ShopOrder', 'ID');
    }

    public function getCurrentStep() {
        $current = $this->routingStepPlans()->where('LineNumber', ($this->timeTransactions()->count() + 1))->first();

        if(isset($current->Description)) {
            return $current->Description;
        }

        return 'Afgerond';
    }

    public function getMaterial() {
        $material = $this->materialPlans()->where('ItemCode', 'LIKE', 'TAP%')->first();

        if(isset($material->Description)) {
            return $material->Description;
        }
        return '-';
    }

and as last the view

        <table class="table table-striped table-bordered no-width">
            <thead>
                <tr>
                    <th>Order nummer</th>
                    <th>SKU</th>
                    <th>Omschrijving</th>
                    <th>Aantal</th>
                    <th>Datum</th>
                    <th>Deadline</th>
                    <th>Materiaal</th>
                    <th>DXF?</th>
                </tr>
            </thead>
            <tbody>
                @foreach($shopOrders as $shopOrder)
                    <tr>
                        <td>{{ $shopOrder->ShopOrderNumber }}</td>
                        <td>{{ $shopOrder->ItemCode }}</td>
                        <td>{{ str_replace('Car Mats', '',$shopOrder->Description) }}</td>
                        <td>{{ $shopOrder->PlannedQuantity }}</td>
                        <td>{{ $shopOrder->PlannedStartDate }} </td>
                        <td>{{ $shopOrder->PlannedDate }}</td>
                        <td>{{ $shopOrder->getMaterial() }}</td>
                        <td>{{ $shopOrder->hasDxf() }}</td>
                    </tr>
                @endforeach
            </tbody>
        </table>
        {{ $shopOrders->total() }}
        {{ $shopOrders->appends(['material' => Request::get('material')])->render() }} 

I expect 1 page from pagination with orders/nesten?material=Saxony%20Zwart&page=1 as url since that material has 9 orders.

But currently it still has 151 pages, the same as just going to orders/nesten.

Answer 1

From what i see you're fetching all records from database and then filter them with Collection filter. Pagination is build from database query results, so you should update your query to do the filtering.

---

Update

1. Create abstract filter class

namespace App\Http\Filters;

use Illuminate\Http\Request;
use Illuminate\Database\Eloquent\Builder;

class AbstractFilter
{
    protected $builder;

    protected $request;

    public function __construct(Request $request)
    {
        $this->request = $request;
    }

    public function apply(Builder $builder)
    {
        $this->builder = $builder;

        foreach($this->filters() as $name => $value) {
            if(method_exists($this, $name)) {
                call_user_func_array([$this, $name], array_filter([$value]));
            }
        }

        return $this->builder;
    }

    public function filters()
    {
        return $this->request->all();
    }
}

2. Extend that class for your request (NOTE - method names here are the request parameters for filters):

namespace App\Http\Filters;

class OrdersFilter extends AbstractFilter
{
    public function material($value = null)
    {
        if (!is_null($value)) {
            // return modified query here (return $this->builder->where....) 

        }
    }
}

3. Add scope to your model (in ShopOrder model):

public function scopeFilter($query, OrderFilters $filter) {
    return $filter->apply($query);
}

4. Update your query like this:

$orders = ShopOrder::whereLike('ItemCode', 'CM%')
    ->whereNull('Exact')
    ->whereNull('Nesting')
    ->where('IsOnHold', '!=', 1)
    ->where('ShopOrderRoutingStepPlanCount', '!=', 0)
    ->filter(new OrdersFilter($request))
    ->paginate(50);

And you can skip all collection filtering. Also you can add more filter params to OrdersFilter class.

NOTE: i wrote everything without testing, so there might be some minor errors. Also I haven't wrote materials filter without having a database structure, but your Model methods could be better.

UPDATE

for filter materials method i would use something like this (not 100% if it works, haven't tested):

public function material($value = null) {
    if (!is_null($material)) {
        return $this->builder->having('', function($query) use ($value) {
            return $query->where('ItemCode', 'LIKE', 'TAP%')
                ->where('description', '=', $value);
        })
        ->having('routingStepPlans', function($query) {
            return $query->where('LineNumber', ShopOrder::timeTransactions()->count() + 1)
               ->where('description', '=', 'Nesten');
        });
    }
}

Answer 2

so that your pagination continues but keep your search

{{ $shopOrders->appends(Request::except('page'))->links() }}