Let's say we have two tables:
| id | reason_id | amount |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 10 |
| 3 | 1 | 30 |
| id | title | is_discount |
|---|---|---|
| 1 | Payment for something | 0 |
| 2 | Discount for something | 1 |
I'd like to query payments table and sum amount column based on its relationship payment_reasons.is_discount value:
| total_received | total_discounted |
|---|---|
| 130 | 10 |
How to achieve this?
If you want to get the data in one db-request, I don't think there is a clean laravel-way to solve this. I've been trying out some raw mysql to get it into a working query and it would look like this:
select sum(if(pr.is_discount = 0, p.amount, 0)) as total_received, sum(if(pr.is_discount = 1, p.amount, 0)) as total_discounted from payments p join payment_reasons pr on pr.id = p.reason_id The if statement will render if discount needs to be used per row and the sum wil simply sum all the end-results together.
Doing this in laravel with a model does not really make sence, since there are no fields in the result which your model could use anyway. In this case, using eloquent would be more (unmaintainable) code than a simple Query Builder:
$data = DB::select(' select sum(if(pr.is_discount = 0, p.amount, 0)) as total_received, sum(if(pr.is_discount = 1, p.amount, 0)) as total_discounted from payments p join payment_reasons pr on pr.id = p.reason_id ') If you don't mind doing multiple queries to fetch the data, models make more sense:
$totalReceived = Payment ::whereHas('paymentReason', fn($q) => $q->where('is_discount', 0)) ->sum('amount'); $totalDiscounted = Payment ::whereHas('paymentReason', fn($q) => $q->where('is_discount', 1)) ->sum('amount'); Please note that this example will perform 4 queries, 2 on payments, and 2 on payment_reasons
