Laravel: Get last transaction of each user

Question

I have a transactions table and a users table. Each transaction has a foreign key referring to an user. I need to get last transaction of each user for a particular day.

I have written this function but it's returning first transaction of each user.

public function usersLastTransaction(Request $request)
{
    return DB::table('transactions')
        ->join('users', 'users.id', '=', 'transactions.trigered_by')
        ->join('model_has_roles', 'model_has_roles.model_id', '=', 'users.id')
        ->join('roles', 'roles.id', '=', 'model_has_roles.role_id')
        ->whereDate('transactions.created_at', '2023-07-14')
        ->groupBy('transactions.trigered_by')
        ->select('transactions.*')
        ->get();
}

Answer 1

As discussed on comments, you are looking for Aggregate Functions and how to use them on Laravel. On Laravel documentation there is an example of how to use the aggregate function count(id) as a raw select clause, we can do just the same with the max(created_at) function:

public function usersLastTransaction(Request $request)
{
    return DB::table('transactions')
        ->join('users', 'users.id', '=', 'transactions.trigered_by')
        ->join('model_has_roles', 'model_has_roles.model_id', '=', 'users.id')
        ->join('roles', 'roles.id', '=', 'model_has_roles.role_id')
        ->whereDate('transactions.created_at', '2023-07-14')
        ->groupBy('transactions.trigered_by')
        ->selectRaw('transactions.*, MAX(transactions.created_at) AS latest')
        ->get();
}

This query on its own solves your issue, returning the timestamp of the latest created transaction for the given day. Nonetheless, as mentioned by matiaslaurati your usage of Laravel is far from idiomatic. You should look into Laravel's ORM: Eloquent to achieve its full power. Once best practices are applied, your code should look like this:

$transactions = Transaction::query()
    ->has('triggeredBy.roles')
    ->whereDate('created_at', '2023-07-14')
    ->get();

$latestByUser = $transactions
    ->groupBy('triggered_by')
    ->map->max('created_at');

Dump of $latestByUser:

Illuminate\Support\Collection^ {#3452
  #items: array:2 [
    // triggered_by => latest transaction timestamp
    13 => "2023-07-15 11:54:48"
    18 => "2023-07-15 02:01:34"
  ]
}

Not only readability is improved, as you are also working with a very powerful and elegant ORM. To reach this:

1. I created the Transaction model and its BelongsTo relation to User, called triggered_by (I'm assuming you cannot call it user_id, but it would be preferable);
2. I created Role model;
3. I created User model and its BelongsToMany relation through model_has_roles (pivot) to Role (standard relation name: roles)