Laravel Eloquent Scope As Select Laravel Package

Getting Started

Install the package with Composer; Laravel's package discovery registers the service provider, and there is no config file to publish:

composer require protonemedia/laravel-eloquent-scope-as-select

First, confirm the builder macro is available in your app:

use Illuminate\Database\Eloquent\Builder;

Builder::hasMacro('addSelectSubquery'); // true after package discovery

Look first at the package README and then at vendor/protonemedia/laravel-eloquent-scope-as-select/src if the exact argument order differs from your version. In current releases the primary entry point is addSelectSubquery($alias, $query), where $query is an Eloquent builder that becomes a correlated select subquery.

The first real use case is replacing a hand-written withCount plus duplicated where constraints:

use App\Models\Post;
use App\Models\User;

$users = User::query()
    ->addSelectSubquery(
        'published_posts_count',
        Post::query()
            ->published()
            ->whereColumn('posts.user_id', 'users.id')
            ->selectRaw('count(*)')
    )
    ->orderByDesc('published_posts_count')
    ->paginate();

// Each model now has $user->published_posts_count without an N+1 query.

This is the day-to-day value: the published() scope lives in one place, is reused inside the subquery, and the result is available as a normal selected column.

Implementation Patterns

Reuse an existing scope as the source of truth. Keep constraints on the related model and pass that builder into the subquery:

class Post extends Model
{
    public function scopePublished(Builder $query): void
    {
        $query->where('published', true)
              ->whereNotNull('published_at');
    }
}

$users = User::query()
    ->addSelectSubquery(
        'published_posts_count',
        Post::query()
            ->published()
            ->whereColumn('posts.user_id', 'users.id')
            ->selectRaw('count(*)')
    )
    ->get();

The same scope can still be used for filtering, so the definition does not drift between a where clause and a computed column.

Parameterised scopes work naturally. Pass arguments to the scope inside the subquery builder:

Post::query()
    ->publishedAfter($request->date('since'))
    ->whereColumn('posts.user_id', 'users.id')
    ->selectRaw('count(*)');

Chain multiple computed columns. This is common on index pages and API resources:

$users = User::query()
    ->addSelectSubquery(
        'published_posts_count',
        Post::query()->published()
            ->whereColumn('posts.user_id', 'users.id')
            ->selectRaw('count(*)')
    )
    ->addSelectSubquery(
        'latest_post_at',
        Post::query()
            ->whereColumn('posts.user_id', 'users.id')
            ->selectRaw('max(published_at)')
    )
    ->get();

Sort and filter on the alias in SQL, not PHP. Use orderBy normally, but use having for filtering because the alias is not available in the WHERE clause:

->having('published_posts_count', '>', 0)

If your database does not permit HAVING without grouping, wrap the outer query or move the condition into the subquery.

Use it in API resources instead of loading relations just to count them:

class UserResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id' => $this->id,
            'published_posts_count' => (int) $this->published_posts_count,
            'latest_post_at' => $this->latest_post_at,
        ];
    }
}

Prefer builder instances over raw SQL strings. Passing Post::query() preserves global scopes such as soft deletes. Passing DB::table('posts') bypasses them and is a common source of mismatched counts.

Test the generated SQL, not just the result. In feature tests, assert the column exists and has the expected type, and use toSql()/getBindings() when debugging:

$query = User::query()->addSelectSubquery('published_posts_count', $subquery);

dump($query->toSql(), $query->getBindings());

Gotchas and Tips

• Correlation is your responsibility. The subquery must include whereColumn('posts.user_id', 'users.id'); without it you get a global count repeated on every row. Table aliases must match both sides.
• Alias collisions are silent. Do not alias a subquery to an existing column name. Use a _count or _at suffix and cast in the model or resource.
• withCount is still better for simple relationship counts. It is optimised and less error-prone. Reach for this package when the count needs arbitrary scopes, multiple constraints, or an aggregate other than count(*).
• Correlated subqueries can be slow on large tables. Ensure the foreign key (posts.user_id) and filter columns are indexed. Compare execution plans against a leftJoin/groupBy approach for high-volume endpoints.
• Global scopes and soft deletes differ by builder source. Post::query() applies them; Post::withTrashed() intentionally does not; raw DB::table() applies neither.
• Do not reuse the same builder instance across two addSelectSubquery calls. Clone it or construct a fresh builder, otherwise bindings and constraints accumulate unexpectedly.
• Aggregate subqueries must return a scalar. selectRaw('count(*)'), max(...), sum(...) are safe. Returning multiple columns from the subquery will break the outer select.
• Watch groupBy interactions. If the outer query groups, adding an aggregate subquery can produce surprising results or SQL errors on strict databases. Keep the subquery correlated and ungrouped unless you deliberately need grouping.
• Bindings can be hard to trace. Use DB::listen() or Laravel Telescope to inspect the final SQL. The subquery's bindings are merged into the outer query, so a missing binding usually means the subquery builder was mutated after being added.
• Method signature is macro-based. If addSelectSubquery is missing, run composer dump-autoload and check package discovery. If the argument order differs in your version, confirm against the vendor source rather than guessing.
• Wrap repeated usage in a model method. A User::withPostStats() scope that chains the subqueries keeps controllers and resources clean and gives you one place to change aliases or casts.
• Type casting: MySQL may return aggregate columns as strings. Add casts such as 'published_posts_count' => 'integer' on the model or cast explicitly in the resource.
• Extension point: the registered builder macro is global, so you can compose it inside your own custom scope or a reusable query object without touching package internals.