Laravel实现双条件匹配三表联查 无匹配自动生成新行
三表关联实现方案(Laravel 环境)
核心逻辑说明
该需求的本质是双字段匹配的全外连接查询:
- 以已完成的 Purchase Orders 与 Stock 联查结果为左数据集
- 以 ItemOrders 表为右数据集
- 匹配规则为同时满足两个等值条件:联查结果的 SKU 与 ItemOrders 的 SKU 相等、联查结果的 location 与 ItemOrders 的 Location 相等
- 结果需保留两边所有未匹配的行,匹配成功的行合并对应字段值
之前尝试的方案没有得到正确结果,通常是以下几个原因:
- 直接用 INNER JOIN 只会保留两边完全匹配的行,会漏掉单侧存在的记录
- 单独使用 LEFT 或 RIGHT JOIN 只能保留左/右单表的全量记录,无法兼顾另一侧的独有数据
- 自行拼接 UNION 时没有对齐两个结果集的字段顺序、没有严格使用双字段关联、误用 UNION ALL 导致匹配行重复出现
由于 MySQL 本身不支持原生的 FULL OUTER JOIN 语法,通用实现方式是:左连接结果集通过 UNION 去重合并右连接结果集,得到全外连接的效果。
具体实现代码
- 先封装已完成的两表联查为独立子查询,避免重复编写关联逻辑
// 基础两表联查子查询 $poStockBase = DB::table('Purchase Orders as po') ->join('Stock as s', 's.PurchaseOrderID', '=', 'po.ID') ->select( 's.SKU', 's.OnOrder', 'po.location' );
- 构建左连接查询:保留联查结果的全量记录,匹配对应 ItemOrders 数据
$leftPart = DB::query() ->fromSub($poStockBase, 'base') ->leftJoin('ItemOrders as io', function ($join) { // 严格双条件匹配 $join->on('base.SKU', '=', 'io.SKU') ->on('base.location', '=', 'io.Location'); }) ->select( 'base.SKU', 'base.OnOrder', 'io.Quantity', // Location 字段优先取非空值,避免出现 NULL DB::raw('COALESCE(base.location, io.Location) as Location') );
- 构建右连接查询:保留 ItemOrders 的全量记录,匹配对应联查结果数据
$rightPart = DB::query() ->fromSub($poStockBase, 'base') ->rightJoin('ItemOrders as io', function ($join) { // 和左连接保持完全一致的匹配条件 $join->on('base.SKU', '=', 'io.SKU') ->on('base.location', '=', 'io.Location'); }) ->select( // SKU 字段优先取非空值 DB::raw('COALESCE(base.SKU, io.SKU) as SKU'), 'base.OnOrder', 'io.Quantity', DB::raw('COALESCE(base.location, io.Location) as Location') );
- 用 UNION 合并两个结果集(UNION 会自动去重,避免匹配上的行重复出现)
$finalResult = $leftPart->union($rightPart)->get();
输出结果验证
上述代码运行后,会完全匹配预期的输出:
| SKU | OnOrder | Quantity | Location |
|---|---|---|---|
| ABC | 30 | 88 | USA |
| DEF | 40 | NULL | Canada |
| DEF | NULL | 99 | Mexico |
注意事项
- 如果表名不符合 Laravel 蛇形命名规范(如带空格的 Purchase Orders),需确保数据库连接配置支持反引号包裹表名,避免语法错误
- 关联条件必须同时绑定 SKU 和 Location 两个字段,仅单字段匹配会出现数据错位
- 不要使用
UNION ALL替代UNION,否则匹配上的行(如 ABC/USA 那条)会在结果中重复出现
内容的提问来源于stack exchange,提问作者David-Dj Bryant
相关产品推荐
相关产品推荐

