How to get transaction count with laravel eloqueent-CodePudding

I have two tables users and transactions. users table:

 id     name
------------- 
 1       joe
 2       jane
 3       phil

and transactions table :

id   user_id   is_successful  
------------------------------
1      1            0
2      1            1
3      1            1
4      2            0
5      3            1
6      3            0

I want to get all successfull transactions count by each user. something like this:

joe =>2,
jane=>0,
phil=>1

I tried something like the following but it gets only successful transactions:

Transaction::wherIsSuccessful(1)
->with('user')
->select('user_id', DB::raw('count(*) as transaction_counts'))
->groupBy('user_id');

CodePudding user response：

You can use withCount method (more details) to get user's successful transaction count.

User::withCount(['transactions' => function (Builder $query) {
    $query->where('is_successful', true);
}])->get();