Laravel controller merge collections in one return-CodePudding

I have 2 queries to return data from DB by Laravel controller first :

$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();

it will return result like

  #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"
}

] and second query is :

$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();

it will return result like

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"
}

]

How can I merge between $results and $results2 to return only one collection , the marge must be to add object from $results2 to results which has same location_id and variation_id , and if not find in $results , It will be added alone example of needed collection

#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"
}

]

Thanks in advance

CodePudding user response：

I think this might help you by using array merge, example below:

array_merge($data1, $data2)

CodePudding user response：

In Laravel you can merge two collection results by:

$first_collection  = collect(['Dog']);

$second_collection = collect(['Cat']);

$merged_collection = $first_collection->merge($second_collection);

dd($merged_collection->toArray());

// ['Dog', 'Cat']