Laravel MySQL按条件分组记录并关联取值的查询构建求助

在holla_voucher_code表中有两条关联记录：

• 记录A：包含group_id、group_id_verify、export_id、id_voucher_verify字段，其中id_voucher_verify值等于记录B的ID，group_id_verify为0
• 记录B：包含相同字段，id_voucher_verify值等于记录A的ID，group_id_verify不为0

需求：
按export_id、group_id、group_id_verify三个字段分组，同时当id_voucher_verify字段值不为0时，通过关联关系获取对应记录的group_id值。

$group_voucher_out_stock = CodeModel::getDBTable();
$group_voucher_out_stock = $group_voucher_out_stock->select(CodeModel::getDBConn()->raw('COUNT(id) as num'),
    'group_id', 'group_id_verify', 'export_id')
    ->groupBy(['export_id', 'group_id', CodeModel::getDBConn()->raw('IF(`voucher_id_verify` != 0, (SELECT `group_id` FROM `holla_voucher_code` AS A WHERE `A`.`id` = `voucher_id_verify`), `group_id_verify`)')])
    ->get();

修正点说明 #

1. 字段名修正：原代码中误用voucher_id_verify，实际应为id_voucher_verify
2. 用LEFT JOIN替代子查询，提升查询效率，避免重复执行子查询
3. 明确分组逻辑，确保关联记录的group_id正确获取

代码实现 #

$group_voucher_out_stock = CodeModel::getDBTable()
    ->leftJoin('holla_voucher_code as related', 'holla_voucher_code.id_voucher_verify', '=', 'related.id')
    ->select(
        CodeModel::getDBConn()->raw('COUNT(holla_voucher_code.id) as num'),
        'holla_voucher_code.group_id',
        'holla_voucher_code.group_id_verify',
        'holla_voucher_code.export_id',
        // 定义关联记录的group_id，不存在则用自身group_id_verify
        CodeModel::getDBConn()->raw('IF(holla_voucher_code.id_voucher_verify != 0, related.group_id, holla_voucher_code.group_id_verify) as related_group_id')
    )
    ->groupBy([
        'holla_voucher_code.export_id',
        'holla_voucher_code.group_id',
        'holla_voucher_code.group_id_verify',
        'related_group_id' // 用别名分组，更清晰
    ])
    ->get();

逻辑解释 #

• 通过LEFT JOIN关联自身表，匹配id_voucher_verify对应的记录
• 用IF函数判断：当id_voucher_verify不为0时，取关联记录的group_id，否则取自身的group_id_verify
• 分组时包含related_group_id，确保分组逻辑符合需求

内容的提问来源于stack exchange，提问作者Kay Nguyễn