使用Yajra\DataTables添加下拉筛选遇问题：状态筛选不刷新

在基于Yajra\DataTables\Services\DataTable类的BankTransferDataTable中添加状态（Status）、起始日期（From）、结束日期（To）三个筛选器后，选择状态下拉选项时表格数据未刷新/重载，控制台无任何输出。

问题代码片段 #

PHP DataTable类代码 #

<?php

namespace App\DataTables\Payments;

use App\Models\Order;
use App\Models\Payment;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Services\DataTable;

class BankTransferDataTable extends DataTable
{
    protected $exportColumns = [
        'id', 'order_id', 'order_number', 'invoice_number', 'bulk_order_key',
        'add_on', 'email', 'mobile_number', 'g_bank', 'bank_name', 'account_name',
        'account_number', 'payment_date', 'amount', 'deposit_slip', 'status',
        'transaction_id', 'created_at', 'updated_at', 'phone', 'order_status', 'order_total'
    ];

    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query)
            ->addColumn('Order #', function ($payment) {
                return $payment->bulk_order_key;
            })
            ->addColumn('Gifted Account', function ($payment) {
                return $payment->g_bank;
            })
            ->addColumn('Bank Name', function ($payment) {
                return $payment->bank_name;
            })
            ->addColumn('Account Name', function ($payment) {
                return $payment->account_name;
            })
            ->addColumn('Status', function ($payment) {
                if ($payment->transaction_id) {
                    return $payment->get_wallet_transaction_status();
                } else if($payment->bulk_order_key) {
                    $orderStatus = Order::select(['status'])
                        ->where('bulk_order_key', $payment->bulk_order_key)
                        ->first();

                    if ($orderStatus) {
                        return $orderStatus->status;
                    }
                }

                return $payment->order_status;
            });
    }

    /**
     * Get query source of dataTable.
     *
     * @param  \App\Payment  $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Payment $model)
    {
        $filters = request()->has('search.value') && filled(request('search.value')) ? json_decode(request('search.value'), true)['filters'] : [];

        return $model->newQuery()
            ->selectRaw('payments.*, orders.phone, orders.status as order_status, orders.total as order_total')
            ->leftJoin('orders', 'payments.order_id', '=', 'orders.id')
            ->whereRaw('payments.bulk_order_key <> ""');

            if (filled($filters['status'])) {
                $query->where('payments.status', $filters['status']);
            }

            $query->when((request()->has('from') && request()->has('to')), function ($query) {
                $query->whereDate('payments.created_at', '>=', request()->get('from'));
                $query->whereDate('payments.created_at', '<=', request()->get('to'));
            })
            ->when((request()->has('from')), function ($query) {
                $query->whereDate('payments.created_at', '>=', request()->get('from'));
                $query->whereDate('payments.created_at', '<=', date('Y-m-d', strtotime('today')));
            })
            ->when((request()->has('to')), function ($query) {
                $query->whereDate('payments.created_at', '>=', date('Y-m-d', strtotime('-5 years')));
                $query->whereDate('payments.created_at', '<=', request()->get('to'));
            })
            ->orderBy('payments.created_at', 'desc');
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
            ->setTableId('bankTransferPaymentsTable')
            ->columns($this->getColumns())
            ->minifiedAjax()
            ->dom('Brtip')
            ->orderBy(1)
            ->buttons(
                Button::make('export')->buttons(['csv']),
                Button::make('reset')
                    ->text('<span><i class="fa fa-undo"></i> Refresh</span>'),
            );
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::make('Order #'),
            Column::make('Gifted Account'),
            Column::make('Bank Name'),
            Column::make('Status'),
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'bank_transfer_payments_report_' . date('YmdHis');
    }
}

视图代码 #

{{ $dataTable->scripts() }}

<script>
    $(function () {
        const filter = () => {
            const filters = {
                filters: {
                    status: $('#filterStatus').val()
                }
            };

            window.LaravelDataTables["bankTransferPaymentsTable"].search(JSON.stringify(filters)).draw();
                
            $('#bankTransferPaymentsTable').DataTable().ajax.reload();
        };

        $(document).on('change', '#filterStatus', function() {
            filter();
        });
    });
</script>

筛选器截图 #

系统信息 #

• 操作系统：MacOS
• PHP版本：8.1
• Laravel版本：8.83.27
• Laravel-Datatables版本：9.11

问题分析与解决方案 #

核心问题点 #

1. query方法提前return：原代码中构建基础查询后直接return，导致后续的状态筛选、日期筛选逻辑完全未执行，这是数据不更新的根本原因。
2. JS重复触发重载：同时调用search().draw()和ajax.reload()会导致冲突，只需保留前者即可。
3. 日期筛选参数未传递：原JS仅传递了status参数，from和to未加入筛选对象，且后端query方法未从$filters中读取日期参数，导致日期筛选无效。

修改后的代码 #

1. 修正DataTable的query方法

public function query(Payment $model)
{
    $filters = request()->has('search.value') && filled(request('search.value')) ? json_decode(request('search.value'), true)['filters'] : [];

    // 先赋值$query变量，最后再return
    $query = $model->newQuery()
        ->selectRaw('payments.*, orders.phone, orders.status as order_status, orders.total as order_total')
        ->leftJoin('orders', 'payments.order_id', '=', 'orders.id')
        ->whereRaw('payments.bulk_order_key <> ""');

    // 状态筛选
    if (filled($filters['status'])) {
        $query->where('payments.status', $filters['status']);
    }

    // 日期筛选：从$filters中获取参数
    $query->when(filled($filters['from']) && filled($filters['to']), function ($query) use ($filters) {
        $query->whereDate('payments.created_at', '>=', $filters['from']);
        $query->whereDate('payments.created_at', '<=', $filters['to']);
    })
    ->when(filled($filters['from']) && empty($filters['to']), function ($query) use ($filters) {
        $query->whereDate('payments.created_at', '>=', $filters['from']);
        $query->whereDate('payments.created_at', '<=', date('Y-m-d', strtotime('today')));
    })
    ->when(empty($filters['from']) && filled($filters['to']), function ($query) use ($filters) {
        $query->whereDate('payments.created_at', '>=', date('Y-m-d', strtotime('-5 years')));
        $query->whereDate('payments.created_at', '<=', $filters['to']);
    })
    ->orderBy('payments.created_at', 'desc');

    // 最后返回构建好的查询
    return $query;
}

2. 修正视图中的JS代码

<script>
    $(function () {
        const filter = () => {
            const filters = {
                filters: {
                    status: $('#filterStatus').val(),
                    from: $('#filterFrom').val(),
                    to: $('#filterTo').val()
                }
            };

            // 仅调用search+draw即可，无需重复reload
            window.LaravelDataTables["bankTransferPaymentsTable"].search(JSON.stringify(filters)).draw();
        };

        // 给日期筛选器也绑定change事件
        $(document).on('change', '#filterStatus, #filterFrom, #filterTo', function() {
            filter();
        });
    });
</script>

内容的提问来源于stack exchange，提问作者Blues Clues