Ad

Laravel Query Format - Join To The Latest Row

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.

Ad

Answer

$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.

Ad
source: stackoverflow.com
Ad