Laravel Datatable三级关联数据搜索问题求助

问题场景 #

使用Yajra/Laravel-Datatables管理记录，已为数据表添加搜索功能，但三级关联记录的搜索操作失效。

涉及数据表 #

• Lead表
• Agent表
• User表

原代码实现 #

$data = LeadInquiry::select(
    'lead_inquiries.*',
    'agent_leads.agent_id',
    'sender_detail.first_name as sender_first_name',
    'sender_detail.last_name as sender_last_name',
    'sender_detail.email as sender_email',
    'receiver_detail.first_name as receiver_first_name',
    'receiver_detail.last_name as receiver_last_name',
    'receiver_detail.email as receiver_email'
)
    ->join('agent_leads', 'agent_leads.lead_id', 'lead_inquiries.map_id')
    ->join(
        'users as sender_detail',
        'lead_inquiries.sender_id',
        'sender_detail.id'
    )
    ->join(
        'users as receiver_detail',
        'lead_inquiries.receiver_id',
        'receiver_detail.id'
    )
    ->where('agent_leads.agent_id', Auth::user()->id)
    ->latest();

filter(function ($query) use ($search) {
    if ($search) {
        $query->where(function ($query) use ($search) {
            $query->Where('user', function ($query) use ($search) {
                $query->orwhere('first_name', 'like', '%' . $search . '%');
                $query->orwhere('last_name', 'like', '%' . $search . '%');
                $query->orwhere('email', 'like', '%' . $search . '%');
            });
        });
    }
});

问题分析 #

原代码的搜索逻辑存在两个核心问题：

1. 错误使用Where('user', function ($query) ...)写法，不存在名为user的字段，且未正确关联到查询中已别名化的两个User表（sender_detail、receiver_detail）
2. 嵌套的闭包逻辑错误，导致搜索条件无法匹配到关联表的字段

修复后的代码 #

$data = LeadInquiry::select(
    'lead_inquiries.*',
    'agent_leads.agent_id',
    'sender_detail.first_name as sender_first_name',
    'sender_detail.last_name as sender_last_name',
    'sender_detail.email as sender_email',
    'receiver_detail.first_name as receiver_first_name',
    'receiver_detail.last_name as receiver_last_name',
    'receiver_detail.email as receiver_email'
)
    ->join('agent_leads', 'agent_leads.lead_id', 'lead_inquiries.map_id')
    ->join(
        'users as sender_detail',
        'lead_inquiries.sender_id',
        'sender_detail.id'
    )
    ->join(
        'users as receiver_detail',
        'lead_inquiries.receiver_id',
        'receiver_detail.id'
    )
    ->where('agent_leads.agent_id', Auth::user()->id)
    ->latest();

// 修复后的搜索过滤逻辑
$data->filter(function ($query) use ($search) {
    if ($search) {
        $query->where(function ($query) use ($search) {
            // 匹配发送方用户的姓名或邮箱
            $query->orWhere('sender_detail.first_name', 'like', "%{$search}%")
                  ->orWhere('sender_detail.last_name', 'like', "%{$search}%")
                  ->orWhere('sender_detail.email', 'like', "%{$search}%")
                  // 匹配接收方用户的姓名或邮箱
                  ->orWhere('receiver_detail.first_name', 'like', "%{$search}%")
                  ->orWhere('receiver_detail.last_name', 'like', "%{$search}%")
                  ->orWhere('receiver_detail.email', 'like', "%{$search}%");
        });
    }
});

修复说明 #

1. 移除错误的Where('user', ...)嵌套，直接针对查询中已定义的两个User表别名（sender_detail、receiver_detail）添加OR搜索条件
2. 使用字符串插值"%{$search}%"简化代码写法
3. 将所有搜索条件整合到同一闭包内，确保只要匹配发送方或接收方的任意目标字段，记录就会被筛选出来
4. 若需要同时搜索LeadInquiry自身字段，可在闭包内继续添加->orWhere('lead_inquiries.字段名', 'like', "%{$search}%")扩展搜索范围

内容的提问来源于stack exchange，提问作者Sahil Rana