Yajra Datatable关联表DB::raw列无法搜索的最优解决方法

问题原因 #

Yajra Datatable默认会把前端传入的搜索字段，自动拼接主表前缀生成WHERE查询条件。对于DB::raw()生成的自定义别名列、跨表拼接列，自动生成的条件会出现column 主表名.自定义列名 not found的报错。
之前尝试配置列名为users.user_name无效，是因为user_name是查询结果集的别名，并非users表实际存在的字段，数据库层面无法直接匹配该字段。

解决方法 #

不需要修改原有查询结构，不需要将结果集转为数组，直接调用包自带的filterColumn方法，手动指定自定义列的搜索逻辑即可，全程无额外性能损耗。

具体修改步骤 #

• 前端列配置保持原有user_name的写法即可，注意给不存在于数据库的操作列关闭搜索、排序权限，避免自动生成非法查询条件：

columns: [
    {data: 'name', name: 'name'},
    {data: 'phone', name: 'phone'},
    {data: 'status', name: 'status'},
    {data: 'user_name', name: 'user_name'},
    {data: 'action', name: 'action', searchable: false, orderable: false},
],

• 服务端Datatables返回逻辑中，为user_name列添加自定义搜索规则，替换后的完整代码如下：

$AAEListData = Entrepreneur::leftjoin('users', 'users.id', 'entrepreneurs.user_id')
    ->where('entrepreneurs.user_id', Auth::user()->id)
    ->groupBy('entrepreneurs.id')
    ->orderBy('entrepreneurs.created_at','desc')
    ->select([
        'entrepreneurs.*',
        'users.id as user',
        DB::raw('CONCAT(IFNULL(users.user_first_name,"")," ",IFNULL(users.user_middle_name,"")," ",IFNULL(users.user_last_name,"")) as user_name')
    ]);

return Datatables::of($AAEListData)
    // 覆盖默认搜索规则，手动指定user_name列的匹配逻辑
    ->filterColumn('user_name', function($query, $keyword) {
        $matchSql = "CONCAT(IFNULL(users.user_first_name,''),' ',IFNULL(users.user_middle_name,''),' ',IFNULL(users.user_last_name,'')) LIKE ?";
        $query->whereRaw($matchSql, ["%{$keyword}%"]);
    })
    ->addColumn('action', function ($AAEListData) {
        $view_btn = '<a href="' . url('entrepreneur/advance-against-expense-info/edit/' . Encryption::encodeId($AAEListData->id)) .
            '" class="btn btn-xs btn-primary open" target="_blank" ><i class="fa fa-folder-open"></i> Open</a>';
        return $view_btn;
    })
    ->rawColumns(['action'])
    ->make(true);

方案说明 #

• 所有通过DB::raw()生成的别名列、计算字段、跨表聚合字段，都可以通过filterColumn方法自定义搜索逻辑，不需要调整原有查询结构
• 所有通过addColumn动态追加的、不存在于数据库查询结果中的列（比如操作按钮列、序号列），都要在前端配置searchable: false，避免触发默认查询条件生成
• 该方案直接在数据库查询构建器层面执行搜索，和原生查询性能一致，不存在数组遍历带来的额外性能损耗

内容的提问来源于stack exchange，提问作者Mash tan