I want to refactor the withCount part from the following code to reuse it and make the controller shorter.

class GroupController extends Controller
{
    public function show(int $group = 1)
    {
        return Group::where('id', '>', 0)  // to simulate starting the query with some builder
            ->withCount([
                "requests",
                'requests AS requests_count_pending' => function ($query) {
                    $query->where('state', 'pending');
                },
                'requests AS requests_count_accepted' => function ($query) {
                    $query->where('state', 'accepted');
                },
                'requests AS requests_count_refused' => function ($query) {
                    $query->where('state', 'refused');
                }
            ])
            ->find($group);
    }
}

I want to build the query with the following syntax:

$group = Group::where('id', '>', 0)
    ::withCountRequests()
    ->find($group);

What I tried (Patch)

public static function withRequestsCount()
{
    return self::withCount([
        "requests",
        'requests AS requests_count_pending' => function ($query) {
            $query->where('state', 'pending');
        },
        'requests AS requests_count_accepted' => function ($query) {
            $query->where('state', 'accepted');
        },
        'requests AS requests_count_refused' => function ($query) {
            $query->where('state', 'refused');
        }
    ]);
}

public function show(int $group = 1)
{
    return Group::withRequestsCount()
        ->where('id', '>', 0)
        ->find($group);
}

The solution works only when I call the static method first. But I want to chain it fluently like any other Builder

Reproducing

php .\artisan make:model -mcrf Group
php .\artisan make:model -mcrf Request

Migrations

public function up(): void
{
    Schema::create('groups', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
    });
}

public function up(): void
{
    Schema::create('requests', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
        $table->set('state', ['pending', 'accepted', 'refused']);
        $table->foreignId('group_id');
    });
}

Models

class Group extends Model
{
    use HasFactory;

    public function requests()
    {
        return $this->hasMany(Request::class);
    }
}

Factory and seeder

class RequestFactory extends Factory
{
    public function definition(): array
    {
        return [
            'group_id' => 1,
            'state' => $this->faker->randomElement(['pending', 'accepted', 'refused']),
        ];
    }
}

class DatabaseSeeder extends Seeder
{
    public function run()
    {
        DB::table('groups')->insertOrIgnore(['id' => 1]);

        Request::factory(50)->create();
    }
}

Route

Route::get('the_group', [GroupController::class, 'show']);

CodePudding user response：

Use a query scope. It's similar to what you tried with the withRequestCount function.

public static function scopeWithRequestsCount($query)
{
    return $query->withCount([
        "requests",
        'requests AS requests_count_pending' => function ($query) {
            $query->where('state', 'pending');
        },
        'requests AS requests_count_accepted' => function ($query) {
            $query->where('state', 'accepted');
        },
        'requests AS requests_count_refused' => function ($query) {
            $query->where('state', 'refused');
        }
    ]);
}

https://laravel.com/docs/eloquent#local-scopes