CODE EXAMPLE FOR PHP

laravel db raw count where

 $users= User::query()
                ->where('users.ban', '!=', 1)
                ->where('users.rights', '=', 1)
                ->leftJoin('users as referal', 'users.id', '=', 'referal.ref_id')
                ->whereNotNull('referal.id')
                ->select([
                    'users.id',
                    'users.name',
                    'users.telegram_id',
                    DB::raw('count(referal.id) as total_referal'),
                ])
                ->groupBy('users.id')
    ->orderByDesc('total_referal')->paginate(100);