如何让带belongsToMany关联的模型支持Datatables搜索功能

问题原因 #

你开启了Datatables的服务端处理模式（serverSide: true），默认搜索逻辑只会针对主表Sale的字段执行查询，而items是通过with预加载的关联数据，并不在主查询的字段范围内，且默认逻辑不会处理关联表的搜索条件，因此触发报错。

实现方案 #

可以实现该需求，推荐两种常用方案：

方案1：自定义Datatables搜索逻辑（无需修改表结构） #

修改控制器代码，手动指定items字段的搜索规则，通过whereHas查询关联表匹配关键词：

// 先引入依赖
use Illuminate\Database\Eloquent\Builder;

public function demo(Request $request)
{
    if ($request->ajax()) {
        $data = Sale::with(['branch', 'items', 'customer', 'user']);

        return Datatables::of($data)
                ->addIndexColumn()
                // 自定义items列的单独搜索逻辑
                ->filterColumn('items', function($query, $keyword) {
                    $query->whereHas('items', function (Builder $q) use ($keyword) {
                        // 可根据需求调整要搜索的关联字段，比如商品名、UPC、序列号
                        $q->where('items.name', 'like', "%{$keyword}%")
                          ->orWhere('items.upc', 'like', "%{$keyword}%")
                          ->orWhere('serial_number', 'like', "%{$keyword}%");
                    });
                })
                // 全局搜索兼容items字段匹配
                ->filter(function ($query) use ($request) {
                    if ($request->filled('search.value')) {
                        $keyword = $request->get('search')['value'];
                        // 匹配items关联字段
                        $query->whereHas('items', function (Builder $q) use ($keyword) {
                            $q->where('items.name', 'like', "%{$keyword}%")
                              ->orWhere('items.upc', 'like', "%{$keyword}%")
                              ->orWhere('serial_number', 'like', "%{$keyword}%");
                        });
                        // 保留原主表字段的搜索能力，可自行扩展
                        $query->orWhere('sale_number', 'like', "%{$keyword}%")
                              ->orWhere('created_at', 'like', "%{$keyword}%");
                    }
                })
                // 可选：将items数组转为字符串方便前端渲染，避免格式异常
                ->editColumn('items', function($sale) {
                    return $sale->items->map(function($item) {
                        return "{$item->name}（数量：{$item->quantity}）{$item->serial_number}";
                    })->implode('<br>');
                })
                ->rawColumns(['items'])
                ->make(true);
    }
}

同时调整前端Datatables配置，标记items列不可排序（关联字段默认不支持服务端排序，避免额外报错）：

$('#search_sale_list').DataTable({
    "processing": true,
    "serverSide": true,
    "ajax": "{{ route('rma.demo') }}",
    "columns": [
        {data: 'created_at', name: 'created_at'},
        {data: 'branch.address', name: 'branch.address'},
        {data: 'sale_number', name: 'sale_number'},
        {data: 'items', name: 'items', searchable: true, orderable: false}
    ]
});

方案2：冗余字段存储（性能更优，适合数据量大的场景） #

在Sale表新增items_summary文本类型字段，每次创建/更新销售单时，自动将关联商品的名称、序列号、数量等信息拼接为字符串存入该字段，后续搜索直接匹配主表的items_summary字段即可，无需额外处理关联查询，搜索性能更高。

注意事项 #

• 若你的MySQL开启了严格模式，需要确保items关联中groupBy的字段符合sql_mode的要求，避免关联查询本身触发SQL错误。
• 若不需要对items列做单独搜索，仅需要全局搜索支持匹配商品内容，也可以只保留filter方法中的逻辑即可。

内容的提问来源于stack exchange，提问作者Andy Wong