I have an Online Store project written in Laravel 5.8 and I can properly get the latest orders like this:

$orders = Order::query()->latest('orders.created_at');

And at the Blade:

@forelse($orders as $order)
    <tr>
    ...
    </tr>
@empty
@endforelse

But now I need to sort data based on the latest payments.

I have also define One To Many relationship between these two Models:

Payment.php:

public function order()
    {
        return $this->belongsTo(Order::class, 'pay_ord_id', 'pay_id');
    }

Order.php:

public function payments()
    {
        return $this->hasMany(Payment::class, 'pay_ord_id', 'ord_id');
    }

Basically every payment has a record named pay_ord_id which is the Order Id.

And I need to sort data with these conditions:

1- Get latest orders

2- Go to the payments table and search data with the retrieved order ids

3- Sort data based on latest payments data

So how can I properly get the latest payments based on the latest order ids in this situation?

UPDATE:

$orders = Order::query()->latest('orders.created_at')->with([
            'payments' => function($query){
                $query->orderBy('created_at', 'DESC');
            }
        ])->get();

I just tried this but still shows latest orders!

CodePudding user response：

forgive me that I don't know Laravelle but the software principles are the same everywhere. Wouldn't you be better getting the correct order in the query in the first place. I saw on this page that (like most other DB frameworks) Laravelle supports an 'order by' as part of the query.?

CodePudding user response：

Did you try to use with() function to get all order payments

$orders = Order::orderBy('created_at', 'DESC')->with([
    'payments' => function($query){
        $query->orderBy('created_at', 'DESC');
    }
])->get();

CodePudding user response：

You need to join the payments table first if you want to sort the orders by the latest payment:

$orders = Order::leftJoin('payments', function($join){
        $join->on('orders.ord_id', '=', 'payments.pay_ord_id')
            ->on('payments.pay_id', '=', \DB::raw("(SELECT pay_id from payments WHERE orders.ord_id = payments.pay_ord_id ORDER BY created_at DESC limit 1)"));
    })
    ->with([
        'payments' => function($query){
            $query->orderBy('created_at', 'DESC');
        }
    ])
    ->orderBy('payments.created_at', 'DESC')
    ->orderBy('orders.created_at', 'DESC')
    ->get();