Laravel join with limit

Question

I have the following working for laravel paginate

$query = DB::table('clients')
            ->leftJoin('radusergroup', 'clients.username', '=', 'radusergroup.username')
            ->leftJoin('recharge', 'clients.username', '=', 'recharge.soldto');

I want to join some values from two tables radusergroup and recharge. radusergroup always return one row as it has only one row stored whereas recharge table return multiple rows. I want only one row returned from recharge table which is latest entry.

Right now its return all the possible rows from recharge table and showing it on paginated view.

Answer 1

This is Laravel Official documentation

DB::table('clinets')
     ->leftJoin('radusergroup', 'clients.username', '=', 'radusergroup.username')
    ->leftJoin('recharge', function ($leftJoin) {
        $leftJoin->on('clients.username', '=', 'recharge.soldto')
             ->where('recharge.create_date', '=', DB::raw("(select max(`create_date`) from recharge)"));
    })
    ->get();

this is the case if you have create_date column in your table, if you haven't got it I strongly recommend to create that column.