Laravel query format - Join To the Latest row

Question

I have two tables for example users and orders.

I have to join users with orders On users.id = orders.user_id - which is quite fine. User table has one to many relation in orders table.

I have some conditions also, like orders.pay_type = 'xyz', 'orders.date = yesterday' , 'user.status = active' ..etc.

My problem is that I need to join my user table to the latest row of orders table correspond to that user_id. Or need to fetch the latest details of that users orders table details along with user table data.

I already tried

->orderBy('orders.date')
->groupBy('orders.user_id')
->get();

but it has no result in o/p.

Answer 1

$data= users::leftJoin('orders', function($join) { $join->on('orders.user_id', '=', 'users.id') ->on('orders.id', '=', DB::raw("(SELECT max(id) from orders WHERE orders.user_id = users.id)")); }) ->select(*)

This resolve my issue.

Answer 2

If you don't want to change anything in model you can go with this method as well, which is simple query builder method.

DB::table('orders')->leftJoin('users', 'orders.user_id', 'users.id')
    ->groupBy('orders.user_id')
    ->orderBy('orders.date', 'DESC')
    ->get();

Assuming, You might have "orders" function in User.php model

You can also go for below method. (Similar to the answer already given but for array of users instead of one user.)

$users = User::all();
foreach ($users as $user) {
   $latest_order = $user->orders()->where('pay_type', 'xyz')->orderBy('date', 'DESC')->first(); // Option 1
   $latest_order = $user->orders()->where('pay_type', 'xyz')->latest('date')->first(); // Option 2 
}

Answer 3

You can use the following Relation in the User Model

    public function latest_order(){
       return $this->hasOne('Order')->orderBy('orders.date', 'desc')->limit(1);
    }

Update

Since you can not change the Model, you can use the following code to fetch the latest Order for each User.

    $user = User::first(); //get first User 

    $user->orders()->latest('date')->first();