Laravel fetch sum of collection with leftJoin between two dates

Question

I want to list the revenue of all businesses. All businesses are meant to be listed whether they have revenue or not. The revenue can also be sorted by date (within a date range). Meaning that I can check how much each of the business has made between certain dates.

Here is my implementation below:

return Business::
leftJoin('invoice as i', 'i.business_id', '=', 'businesses.id')
->leftJoin('departments as d', 'businesses.department_id', '=', 'd.id')
   ->where('i.created_at', '>', $from)
   ->where('i.created_at', '<', $to)
->select('businesses.id','businesses.name', 'd.name as department', 'd.id as deparment_id', DB::raw('coalesce(sum(i.total_amount_paid),0) as received'),
DB::raw('coalesce(sum(i.invoice_amount),0) as expected'))
->groupBy('businesses.id', 'businesses.name')
->get();

This works but it doesn't return all the businesses. I need all business returned irrespective of whether there are invoices during the set date duration or not.

It only returns all businesses when the snippet below is not within the query builder and that way I cannot sort by date:

->where('i.created_at', '>', $from)
->where('i.created_at', '<', $to)

what business are not showing?

Alberto
– Alberto

2020-09-16 14:20:05 +00:00
Commented Sep 16, 2020 at 14:20 — Alberto
– Alberto, Commented Sep 16, 2020 at 14:20
I just modified the question please.

mykoman
– mykoman

2020-09-16 14:30:35 +00:00
Commented Sep 16, 2020 at 14:30 — mykoman
– mykoman, Commented Sep 16, 2020 at 14:30

Ziad Adeela · Accepted Answer · 2020-09-16 19:15:24Z

1

You should add the date filtration into the join condition as following:

return Business::
->leftJoin('invoice as i', function($join)
  {
     $join->on('i.business_id', '=', 'businesses.id'));
     $join->on('i.created_at', '>', $from);
     $join->on('i.created_at', '<', $to);
  })
->leftJoin('departments as d', 'businesses.department_id', '=', 'd.id')
->select('businesses.id','businesses.name', 'd.name as department', 'd.id as deparment_id', DB::raw('coalesce(sum(i.total_amount_paid),0) as received'),
DB::raw('coalesce(sum(i.invoice_amount),0) as expected'))
->groupBy('businesses.id', 'businesses.name')
->get();

answered Sep 16, 2020 at 19:15

Ziad Adeela

4864 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel fetch sum of collection with leftJoin between two dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related