Laravel如何按条件从两个关联表中查询指定字段

问题原因 #

1. 你的Select语句仅指定了tbl_members的字段，完全未包含tbl_members3的字段，因此无法获取后者的数据。
2. 每条payout记录只会匹配其中一张会员表（根据plan_id区分），需要通过条件判断来动态选取对应表的字段值。

解决方案一：使用CASE WHEN动态选取字段 #

通过CASE WHEN函数根据plan_id的值，选择对应会员表的字段，确保每条记录都能拿到正确的会员信息：

$payout = Payout::leftJoin('tbl_members', function($join) {
        $join->on('tbl_payout.mem_id', '=', 'tbl_members.id')
             ->where('tbl_payout.plan_id', '=', 1);
    })
    ->leftJoin('tbl_members3', function($join) {
        $join->on('tbl_payout.mem_id', '=', 'tbl_members3.id')
             ->where('tbl_payout.plan_id', '=', 3);
    })
    ->select(
        'tbl_payout.id as ID',
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.name ELSE tbl_members3.name END as Member'),
        'tbl_payout.paid as Amount',
        // 其他字段按同样逻辑处理
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.username ELSE tbl_members3.username END as username'),
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.city ELSE tbl_members3.city END as city'),
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.mobile ELSE tbl_members3.mobile END as mobile'),
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.bank ELSE tbl_members3.bank END as bank'),
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.bank_number ELSE tbl_members3.bank_number END as bank_number'),
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.bank_branch ELSE tbl_members3.bank_branch END as bank_branch'),
        DB::raw('CASE WHEN tbl_payout.plan_id = 1 THEN tbl_members.bank_ifsc ELSE tbl_members3.bank_ifsc END as bank_ifsc')
    )
    ->get();

解决方案二：使用UNION合并独立查询 #

如果两张会员表的字段结构完全一致，推荐用UNION分别查询两种套餐的记录再合并，代码更清晰易维护：

// 查询套餐1的 payout 关联 members 表
$query1 = Payout::join('tbl_members', 'tbl_payout.mem_id', '=', 'tbl_members.id')
    ->where('tbl_payout.plan_id', 1)
    ->select(
        'tbl_payout.id as ID',
        'tbl_members.name as Member',
        'tbl_payout.paid as Amount',
        'tbl_members.username',
        'tbl_members.city',
        'tbl_members.mobile',
        'tbl_members.bank',
        'tbl_members.bank_number',
        'tbl_members.bank_branch',
        'tbl_members.bank_ifsc'
    );

// 查询套餐3的 payout 关联 members3 表
$query2 = Payout::join('tbl_members3', 'tbl_payout.mem_id', '=', 'tbl_members3.id')
    ->where('tbl_payout.plan_id', 3)
    ->select(
        'tbl_payout.id as ID',
        'tbl_members3.name as Member',
        'tbl_payout.paid as Amount',
        'tbl_members3.username',
        'tbl_members3.city',
        'tbl_members3.mobile',
        'tbl_members3.bank',
        'tbl_members3.bank_number',
        'tbl_members3.bank_branch',
        'tbl_members3.bank_ifsc'
    );

// 合并两个查询结果
$payout = $query1->union($query2)->get();

内容的提问来源于stack exchange，提问作者Karthik SWOT