Eloquent Subquery Magic Laravel Package

Getting Started

1. Confirm it installs on your stack before writing code. The last tagged release is from October 2019 (Laravel 6 era). Run composer require maksimru/eloquent-subquery-magic on a throwaway branch and read the resulting composer.json constraint on illuminate/database. If you're on Laravel 9+, expect to need --ignore-platform-reqs at best and a fork at worst.

2. Read the README, then src/. With 19 stars and a single maintainer, the source is short and is the real documentation. Look for (a) a service provider and whether it's auto-discovered, (b) any Builder macros or a trait applied to your models, and (c) the class that assembles the subquery SQL — that's where binding order is decided.

3. Start with the canonical use case: a scalar select subquery. Adding "latest related value" columns to an index query without N+1:

   // Conceptually: users list showing the total of their most recent order
   User::query()
       ->select('users.*')
       ->addSelect(['last_order_total' => Order::select('total')
           ->whereColumn('orders.user_id', 'users.id')
           ->latest()
           ->limit(1)
       ])
       ->paginate(25);
   

   If the package provides sugar for this (e.g. a selectSub-style helper or a withSubquery scope), use it — it exists to keep bindings and aliases correct.

4. Sanity-check with ->toSql() and ->getBindings() before running anything. You want to see the correlated subquery inline and the bindings in the order the placeholders appear.

Implementation Patterns

1. Scalar subqueries for list screens. This is the highest-value pattern: one query, one row per parent, aggregate or "latest child" column attached. Use it for admin tables showing counts, last activity, latest status. Reach for withCount/withSum first if the package isn't installed — they cover the common aggregates natively.

2. Existence filters instead of whereHas on hot paths. whereExists subqueries often outperform whereHas because they avoid the extra join the relation adds:

// Users who have at least one unpaid invoice
User::whereExists(function ($q) {
    $q->select(DB::raw(1))
      ->from('invoices')
      ->whereColumn('invoices.user_id', 'users.id')
      ->where('invoices.status', 'unpaid');
});

3. Sorting by a subquery. "Order customers by lifetime value" is a natural fit, but always weigh it against a maintained aggregate column. If the list is paginated and the sort column is indexed on the child table, it's usually acceptable; otherwise denormalize.

4. Wrap recurring subqueries in local scopes. Once a subquery appears in two places, extract it:

public function scopeWithLatestOrderTotal($query)
{
    return $query->addSelect(['last_order_total' => Order::select('total')
        ->whereColumn('orders.user_id', 'users.id')->latest()->limit(1)]);
}

This is your main extension point — the package gives you the plumbing, your scopes give you the domain vocabulary.

5. Layer with eager loading, don't replace it. Use subqueries for scalar data on the parent row (counts, latest value, flags) and with() for anything you'll render as a collection. Mixing both is fine and normal; just be deliberate about which query owns which data.

6. Hide the SQL in API resources. Expose last_order_total through a UserResource or transformer so controllers and API contracts stay stable if you later swap the subquery for a join or a cached column.

7. Test the query shape, not just the result. Because subqueries fail silently-ish (wrong alias, wrong binding), assert on toSql() in a unit test for any non-trivial scope, alongside a feature test against real data.

Gotchas and Tips

• Version drift is the headline risk. A 2019 package predates Laravel's query builder changes around selectSub, fromSub, identifier wrapping, and the Illuminate\Database\Query\Builder refactors. Verify every feature you depend on against your Laravel version; don't assume the README's examples still produce valid SQL.
• Binding order is the classic bug. When you mix selectRaw, whereRaw, and subqueries, placeholders and bindings can desynchronize and you get a Column not found or a silently wrong filter. Always dump toSql() + getBindings() together. On Laravel 10+, ->toRawSql() makes this a one-liner.
• Aliases are mandatory for select subqueries. A subquery in the select list without an alias will collide or be dropped depending on driver. Alias everything, and keep aliases unique per query.
• Correlated subqueries need indexes on the correlation column (orders.user_id above). Without them, a paginated list of 25 rows can trigger 25 full scans. Run EXPLAIN on the generated SQL, not the Eloquent query.
• groupBy + select subquery is fragile. MySQL's ONLY_FULL_GROUP_BY and Postgres will reject or silently pick arbitrary rows. Prefer a join or a withSum-style aggregate when grouping.
• Never interpolate user input into the subquery. The package builds SQL from builder objects — keep it that way. Any DB::raw("... $var") you add is on you, and it's the easiest place to introduce injection in an otherwise safe codebase.
• Debugging workflow: DB::listen(fn ($q) => logger($q->sql, $q->bindings)) around the failing request, then paste the raw SQL into your DB client. Eloquent's exception messages for subquery failures rarely name the subquery.
• Extension points to prefer over patching: query-builder macros (Builder::macro('withSubquery', ...)) and model scopes give you most of what a fork would, without maintaining someone else's 2019 code. If you do need a fix, fork and constrain to dev-main or a tagged fork rather than pinning to an unmaintained tag.
• Have an exit plan. withCount, withSum, withAvg, whereHas, whereRelation, and native selectSub/whereExists cover the overwhelming majority of what this package does. Before adopting, write down the two or three cases that genuinely need it — if the list is short, you may not need the dependency at all.