Eloquent count occurrence with where clause-CodePudding

I'm trying to do a simple query using Eloquent. My test_registrants table looks like this I want to add new column with value of all user_id with payment_status = 1

This is my query using whereColumn

TestRegistrant::select(['test_registrants.*'])
            ->where('payment_status', 1)
            ->addSelect([
                'attempt' => TestRegistrant::select(DB::raw('count(*) as attempt'))
                    ->whereColumn('test_registrants.user_id', 'user_id')
                    ->where(function ($query) {
                        $query->where('payment_status', 1);
                    })
            ]);

but I get all user_id instead

What I'm trying to achieve is this one

So what do I do wrong here? thank you

CodePudding user response：

The reason your query is returning 3, is because it is simply counting all the records that have payment_status = 1. The whereColumn() is not working properly, because it does not reflect the right columns.

When you define an alias for the user_id column on the test_registrants table, it should work. For example, you could name it: outer_user_id. I have updated your example accordingly:

TestRegistrant::select(['test_registrants.payment_status', 'test_registrants.user_id as outer_user_id'])
  ->where('payment_status', 1)
  ->addSelect([
      'attempt' => TestRegistrant::selectRaw('count(*) as attempt')
          ->whereColumn('test_registrants.user_id', 'outer_user_id')
          ->where(function ($query) {
            $query->where('payment_status', 1);
          })
        ])
  ->get();

Alternatively, you could also look into grouping the results, so that you can count all the rows in a specific group.