Hello, im trying to get a collection of my table customers who have unpaid amounts in a table called payments. This is my query
$customers = Customer::where('name', 'like', '%' . $name . '%')
->whereHas('payments', function ($query) {
$query->where('payment', 'like', '%unpaid%')
->orWhere('payment', 'like', '%partial%');
})
->paginate(10);
return new PendingOrderCollection($customers);
The above code retrieves customers, who have payments , the "payment" column indicates the status (paid,unpaid,partial). What i need is getting all customers, sorted by the total unpaid amount. I tried sorting using collection but it sorts the 10 records fetched and not the entire collection without pagination.
Payments table has columns paid and unpaid which are decimals.
Can some one help me out on how do i get customers sorted by the sum of unpaid amount?
Try this
$customers = Customer::where('name', 'like', '%' . $name . '%')
->whereHas('payments', function ($query) {
$query->where('payment', 'like', '%unpaid%')
->orWhere('payment', 'like', '%partial%')
})
->orderBy('payments.amount','DESC')
->paginate(10);
return new PendingOrderCollection($customers);
Hi @RamjithAp , thanks for the quick response, but this wont be working. The customers table has a one to many relationship with the payments table. The tables are like this
customers table
----------------------------------------
|id | name | email | phone | timestamps |
----------------------------------------
payments table
----------------------------------------------------------------------------------------------
|id | customer_id | payment(paid,unpaid,partial) | paid(double) | unpaid(double) | timestamps |
----------------------------------------------------------------------------------------------
so what im trying to get is customers with payments and the customers are ordered by the sum of column(unpaid) in descending order.
Try this
$customers = Customer::where('name', 'like', '%' . $name . '%')
->whereHas('payments', function ($query) {
$query->select(DB::raw('count(*) as payment_count'))
->where('payment', 'like', '%unpaid%')
->orWhere('payment', 'like', '%partial%');
})
->orderBy('payment_count','DESC') //not sure payments.payment_count or payment_count try both
->paginate(10);
return new PendingOrderCollection($customers);
@RamjithAp just edited my previous reply so you can understand the table structure
Ok i finally managed to get the result by doing this in my controller
$customers = Customer::where('name', 'like', '%' . $name . '%')
->whereHas('payments', function ($query) {
$query->where('payment', 'like', '%unpaid%')
->orWhere('payment', 'like', '%partial%');
})
->select('customers.id')
->join('payments', 'customers.id', '=', 'payments.customer_id')
->addSelect(DB::raw('SUM(payments.unpaid) as unpaid'))
->groupBy('customers.id')
->orderBy('unpaid', 'desc')
->paginate(10);
return new PendingOrderCollection($customers);
and doing this in my Resources
return [
'id' => $this->id,
'name' => ucwords($this->payments->first()->customer->name),
'email' => $this->payments->first()->customer->email,
'payments' => Payment::collection($this->payments),
'unpaidTotal' => Payment::collection($this->payments)->sum('unpaid'),
];
Why i am using the relationship from payments to customers in my resource is because the controller just returns the id and unpaid total ordered by unpaid total, so to get the name and email I used the payment-customer relationship.
This seems not to be an optimised solution, i would be grateful i f someone could help me further optimize this.
Please or to participate in this conversation.