4

So I Have two collections, sales and costs.

Now I need them to combine into one collection for my foreach condition (I'm not sure if I can use two collections in one foreach)

RAW Queries:

//Raw MySQL Queries for Sales
$total_sales = DB::raw('SUM(receipts.total) as totalSales');
$year_receipt_created = DB::raw('YEAR(receipts.created_at) as year');

//Raw MyQSL Queries for Cost of Goods Sold
$total_cost = DB::raw('(SUM(qty * cost)) as totalCost');
$year_sold = DB::raw('YEAR(created_at) as year');

Here's my query for these two collections:

$sales = DB::table('receipts')
            ->where('status', 'served')
            ->where('mode', 'restaurant')
            ->select($total_sales, $year_receipt_created)
            ->groupBy('year')
            ->get();

$costs = DB::table('orders')
            ->where('status', 'served')
            ->select($total_cost, $year_sold)
            ->groupBy('year')
            ->get();

Things I've tried testing: Converting the collections into array and tried merging them but I seem to have problems.

I reverted it because I don't know if it's the best way or not. Please let me know what's the best way.

UPDATE: Here's the output for those two queries, hope it helps:

Sales

{
    totalSales: "960.00",
    year: 2017
}

Costs

{
    totalCost: "792.00",
    year: 2017
}

What I tried: (It says it cannot find totalCost)

//Combining TWO collections into ONE Array
$gross_profit = array();
foreach (array_merge($sales, $costs) as $data) 
{
    $keys = array('total_sales', 'total_cost', '$year');
    $values = array($data->totalSales, $data->totalCost, $data->year);
    $gross_profit[$data] = array_combine($keys, $values);
}

**SOLVED: ** I used collection merge (didn't knew there was such a thing)

The syntax I used is, $result = $sales->merge($costs).

Here's the result:

{
    totalSales: "960.00",
    year: 2017
},
{
    totalCost: "792.00",
    year: 2017
}

Answered by: Sagar Gautam

masud_moni
  • 1,121
  • 16
  • 33
Jan Ariel San Jose
  • 690
  • 3
  • 11
  • 31

1 Answers1

7

Use collection merge() function like 

$result = $sales->merge($costs);

You can see docs https://laravel.com/docs/5.4/collections#method-merge

Sagar Gautam
  • 9,049
  • 6
  • 53
  • 84
  • Thank you! Just to make sure, calling data inside the blade would be like: `$result->totalCost`, `$result->totalSales`, `$result->year`? – Jan Ariel San Jose Aug 09 '17 at 02:30
  • Yeah, but with `foreach` it might give error because in first one there is no `totalCost` and in second one there is no `totalSales` – Sagar Gautam Aug 09 '17 at 02:32
  • Ohhh boy, I think I'll have problems. :o I'll try to ask again after 90 mins, we managed to combine them anyways. :D – Jan Ariel San Jose Aug 09 '17 at 02:36
  • Are you working in E Commerce project all questions of yours are like order sales cost .. etc :D :D – Sagar Gautam Aug 09 '17 at 02:40
  • I'm developing a Generic POS and Inventory System applicable for both Restaurants and Retailer. It has bunch of settings so it'll be flexible. These past previous questions is needed for my _Sales Reports_. It surely hurts the head. :D – Jan Ariel San Jose Aug 09 '17 at 02:45
  • @JanArielSanJose Wow, Some accounting application will always give damn problems at the back end with debit and credit. I've worked in similar application before xD – Sagar Gautam Aug 09 '17 at 02:48