CODE EXAMPLE FOR PHP

laravel left join count

  $rating = User::query()
            //            ->where('users.ban', '!=', 1)
            //            ->where('users.rights', '!=', 0)
            ->leftJoin('users as referal', 'users.id', '=', 'referal.ref_id')
            ->whereNotNull('referal.id')
            ->select([
                'users.id',
                'users.name',
                'users.rating',
                'users.status_name',
                'users.status_id',
                'users.telegram_id',
                DB::raw('count(referal.id) as total_referal')
            ])
            ->groupBy('users.id')
            ->orderByDesc('total_referal')
            ->limit(15)->get()->toArray();