Laravel控制器中按指定条件合并两个查询集合的方法
2026-8-25
Laravel中合并两个查询集合的实现方案
我在Laravel控制器中有两个从数据库获取数据的查询，需要按照指定规则合并成一个集合。
第一个查询代码
$results = DB::table('transaction_sell_lines as tsl') ->select(DB::raw('b.name as results_business_location_name , t.location_id as results_location_id , tsl.product_id as results_product_id , p.name as results_product_name , tsl.variation_id as results_variation_id , sum(tsl.quantity) AS results_quantity , sum(tsl.quantity * tsl.unit_price_inc_tax) AS results_total')) ->leftjoin('transactions as t','t.id', '=', 'tsl.transaction_id') ->leftjoin('business_locations as b','b.id', '=', 't.location_id') ->leftjoin('products as p','p.id', '=', 'tsl.product_id') ->whereBetween('t.transaction_date',[$startDate.' 00:00:00', $endDate.' 23:59:59']) ->where('b.custom_field1', $location_type) ->where('t.type', '=' , 'sell') ->where('t.status', '=' , 'final') ->where('p.id', '!=' , 1) ->groupBy('tsl.variation_id') ->orderBy('tsl.variation_id', 'asc') ->get();
返回结果示例
#items: array:2 [▼ 0 => { ▼ +"results_business_location_name": "Video Game" +"results_location_id": 18 +"results_product_id": 513 +"results_product_name": "موتوسيكل" +"results_variation_id": 661 +"results_quantity": "211.0000" +"results_total": "4220.00000000" } 1 => { ▼ +"results_business_location_name": "Video Game" +"results_location_id": 19 +"results_product_id": 513 +"results_product_name": "موتوسيكل" +"results_variation_id": 661 +"results_quantity": "211.0000" +"results_total": "4220.00000000" }
第二个查询代码
$results2 = DB::table('transaction_sell_lines as tsl') ->select(DB::raw('b.name as results2_business_location_name , t.location_id as results2_location_id , tsl.product_id as results2_product_id , p.name as results2_product_name , tsl.variation_id as results2_variation_id , sum(tsl.quantity) AS results2_quantity , sum(tsl.quantity * tsl.unit_price_inc_tax) AS results2_total')) ->leftjoin('transactions as t','t.id', '=', 'tsl.transaction_id') ->leftjoin('business_locations as b','b.id', '=', 't.location_id') ->leftjoin('products as p','p.id', '=', 'tsl.product_id') ->whereRaw("t.transaction_date LIKE '$reportdate%'") ->where('b.custom_field1', $location_type) ->where('t.type', '=' , 'sell') ->where('t.status', '=' , 'final') ->where('p.id', '!=' , 1) ->groupBy('tsl.variation_id') ->orderBy('tsl.variation_id', 'asc') ->orderBy('b.id', 'asc') ->get();
返回结果示例
array:2 [▼ 0 => { ▼ +"results2_business_location_name": "Video Game" +"results2_location_id": 18 +"results2_product_id": 513 +"results2_product_name": "موتوسيكل" +"results2_variation_id": 661 +"results2_quantity": "1.0000" +"results2_total": "20.00000000" } 1 => { ▼ +"results2_business_location_name": "Video Game" +"results2_location_id": 20 +"results2_product_id": 513 +"results2_product_name": "موتوسيكل" +"results2_variation_id": 661 +"results2_quantity": "1.0000" +"results2_total": "20.00000000" }
合并规则
- 将
$results2中与$results具有相同location_id和variation_id的对象合并到$results的对应项中； - 若
$results中无匹配项，则单独添加$results2中的该对象，并以0填充$results相关字段； - 若
$results2中无匹配项，则以0填充$results2相关字段。
期望合并结果示例
#items: array:4 [▼ 0 => { ▼ +"results_business_location_name": "Video Game" +"results_location_id": 18 +"results_product_id": 513 +"results_product_name": "موتوسيكل" +"results_variation_id": 661 +"results_quantity": "211.0000" +"results_total": "4220.00000000" +"results2_business_location_name": "Video Game" +"results2_location_id": 18 +"results2_product_id": 513 +"results2_product_name": "موتوسيكل" +"results2_variation_id": 661 +"results2_quantity": "1.0000" +"results2_total": "20.00000000" } 1 => { ▼ +"results_business_location_name": "Video Game" +"results_location_id": 19 +"results_product_id": 513 +"results_product_name": "موتوسيكل" +"results_variation_id": 661 +"results_quantity": "211.0000" +"results_total": "4220.00000000" +"results2_business_location_name": "0" +"results2_location_id": 0 +"results2_product_id": 0 +"results2_product_name": "0" +"results2_variation_id": 0 +"results2_quantity": "0" +"results2_total": "0" } 2 => { ▼ +"results_business_location_name": "0" +"results_location_id": 0 +"results_product_id": 0 +"results_product_name": "0" +"results_variation_id": 0 +"results_quantity": "0" +"results_total": "0" +"results2_business_location_name": "Video Game" +"results2_location_id": 20 +"results2_product_id": 513 +"results2_product_name": "موتوسيكل" +"results2_variation_id": 661 +"results2_quantity": "1.0000" +"results2_total": "20.00000000" }
实现代码
利用Laravel集合的方法可以快速实现需求，具体代码如下：
// 为两个集合创建基于location_id和variation_id的唯一标识键 $resultsKeyed = $results->keyBy(function ($item) { return $item->results_location_id . '-' . $item->results_variation_id; }); $results2Keyed = $results2->keyBy(function ($item) { return $item->results2_location_id . '-' . $item->results2_variation_id; }); $merged = collect(); // 处理$results中的所有项，合并匹配的$results2数据 foreach ($resultsKeyed as $key => $result) { $mergedItem = $result->toArray(); if ($results2Keyed->has($key)) { $mergedItem = array_merge($mergedItem, $results2Keyed->get($key)->toArray()); } else { // 填充$results2相关字段为0 $mergedItem = array_merge($mergedItem, [ 'results2_business_location_name' => '0', 'results2_location_id' => 0, 'results2_product_id' => 0, 'results2_product_name' => '0', 'results2_variation_id' => 0, 'results2_quantity' => '0', 'results2_total' => '0' ]); } $merged->push((object)$mergedItem); } // 处理$results2中未在$results出现的项，填充$results相关字段为0 foreach ($results2Keyed as $key => $result2) { if (!$resultsKeyed->has($key)) { $mergedItem = [ 'results_business_location_name' => '0', 'results_location_id' => 0, 'results_product_id' => 0, 'results_product_name' => '0', 'results_variation_id' => 0, 'results_quantity' => '0', 'results_total' => '0' ]; $mergedItem = array_merge($mergedItem, $result2->toArray()); $merged->push((object)$mergedItem); } } // $merged即为最终合并后的集合
内容的提问来源于stack exchange，提问作者fionka
相关产品推荐
相关产品推荐
