Laravel Yajra Datatable修改日期格式后搜索过滤失效如何解决？

问题根源 #

你当前的搜索失效是因为前端列配置的name属性指定为created_at.timestamp，Yajra Datatables默认会按照该字段做匹配，用户输入的是d/m/Y格式的日期字符串，和timestamp格式完全不匹配，因此无搜索结果。

修复步骤 #

1. 后端Controller修改 #

在editColumn逻辑后添加自定义的搜索过滤规则和排序规则，保留原数组返回逻辑不变：

<?php
// 顶部先引入依赖
use Illuminate\Support\Str;
use Carbon\Carbon;

public function index(Request $request)
{
    $raw = Http::withHeaders(['Authorization' => 'Bearer ' . Cookie::get('access_token')])->get(env('API_URL') . '/v1/kelola-role/role');
    $data = $raw->json('data');
    $status = $raw->json('status');
    if ($request->ajax()) {
        if ($status == 'success') {
            return DataTables::of($data)
                ->addIndexColumn()
                ->editColumn('created_at', function ($e) {
                    return [
                        'display' => Carbon::parse($e['created_at'])->format('d/m/Y'),
                        'timestamp' => Carbon::parse($e['created_at'])
                    ];
                })
                ->editColumn('updated_at', function ($e) {
                    return [
                        'display' => Carbon::parse($e['updated_at'])->format('d/m/Y'),
                        'timestamp' => Carbon::parse($e['updated_at'])
                    ];
                })
                // 新增自定义搜索逻辑：匹配用户可见的display格式
                ->filterColumn('created_at', function($query, $keyword) {
                    $query->filter(function($item) use ($keyword) {
                        return Str::contains(Carbon::parse($item['created_at'])->format('d/m/Y'), $keyword);
                    });
                })
                ->filterColumn('updated_at', function($query, $keyword) {
                    $query->filter(function($item) use ($keyword) {
                        return Str::contains(Carbon::parse($item['updated_at'])->format('d/m/Y'), $keyword);
                    });
                })
                // 新增自定义排序逻辑：用timestamp字段排序
                ->orderColumn('created_at', function ($query, $order) {
                    $query->sortBy('created_at.timestamp', SORT_REGULAR, $order === 'desc');
                })
                ->orderColumn('updated_at', function ($query, $order) {
                    $query->sortBy('updated_at.timestamp', SORT_REGULAR, $order === 'desc');
                })
                ->make(true);
        }
        return abort(401);
    }
    return view('pages.pengaturan.kelola-role');
}

2. 前端JS配置修改 #

调整列的name属性，删除根配置下无效的render参数（该参数放全局不会生效，你已经用了addIndexColumn不需要手动生成序号）：

$.ajaxSetup({
    headers: {
        'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
    }
});

document.addEventListener("DOMContentLoaded", function() {
    $("#dataRole").DataTable({
        processing: true,
        serverSide: true,
        ajax: {
            type: "GET",
            url: "",
            dataSrc: function(json) {
                barDone();
                return json.data;
            }
        },
        columns: [{
                data: 'DT_RowIndex',
                name: 'DT_RowIndex',
                orderable: false,
                searchable: false
            },
            {
                data: 'nama_role',
                name: 'nama_role'
            },
            {
                // 修改name为created_at和后端对应
                name: 'created_at',
                data: {
                    _: 'created_at.display',
                    sort: 'created_at.timestamp'
                }
            },
            {
                // 修改name为updated_at和后端对应
                name: 'updated_at',
                data: {
                    _: 'updated_at.display',
                    sort: 'updated_at.timestamp'
                }
            },
        ],
        responsive: true,
        fixedHeader: true,
        select: {
            style: "multi"
        },
        language: {
            url: '{{ env('APP_URL') }}/id.json',
            processing: "<div id='loadercontainer'><div class='d-flex justify-content-center text-secondary' id='loader'><div class='spinner-border' role='status'><span class='sr-only'>Loading...</span></div></div></div>"
        },
        dom: '<"row"<"col-12 col-sm-6 py-0"l><"col-12 col-sm-6 py-0 pt-2 pt-sm-0"fr><"col-12"t><"col-12 d-flex justify-content-between"ip>>',
    });
});

验证效果 #

修改完成后，搜索d/m/Y格式的日期就可以匹配到对应记录，排序功能依然会使用timestamp值保证排序准确性。

内容的提问来源于stack exchange，提问作者Annug Dev