Laravel Wherehasin Laravel Package

Getting Started

Install — no config, no migrations, no publishable assets; it registers builder macros via package auto-discovery:

composer require dcat/laravel-wherehasin

Where to look first — the whole package is essentially one service provider plus a macro file in vendor/dcat/laravel-wherehasin/src. Read those ~50 lines before adopting it; you need to know exactly what SQL it emits, because that SQL is the entire value proposition. There is nothing else to learn.

Confirm it loaded (catches auto-discovery problems on older/newer skeletons immediately):

\Illuminate\Database\Eloquent\Builder::hasMacro('whereHasIn'); // true

First use case — a slow admin index page. Take an existing whereHas that filters a listing and swap it:

// before: exists (select * from `posts` where `users`.`id` = `posts`.`user_id` and `status` = 1)
User::whereHas('posts', fn ($q) => $q->where('status', 1))->paginate();

// after: `users`.`id` in (select `user_id` from `posts` where `status` = 1)
User::whereHasIn('posts', fn ($q) => $q->where('status', 1))->paginate();

Then run EXPLAIN (or EXPLAIN ANALYZE) on both on your real data before committing. The package is a query-shape swap, not a correctness fix — if the plan doesn't improve, don't keep the dependency.

Implementation Patterns

Treat it as a drop-in at the builder level, not a framework feature. Both whereHasIn and (in most versions) orWhereHasIn / whereDoesntHaveIn return the builder, so they compose exactly like their whereHas counterparts — nested closures, orWhere, whereIn, ordering all work unchanged.

Wrap it in a scope so the dependency is one file deep. With zero dependents and a last release in mid-2021, you want the escape hatch pre-built:

// app/Models/Concerns/HasWhereHasIn.php
public function scopeWhereHasInCompat($query, string $relation, ?Closure $cb = null)
{
    return \Illuminate\Database\Eloquent\Builder::hasMacro('whereHasIn')
        ? $query->whereHasIn($relation, $cb)
        : $query->whereHas($relation, $cb);
}

Now a Laravel upgrade that breaks the macro degrades to the stock whereHas instead of a fatal error, and you can delete the package later by changing one method.

Typical admin/filter workflow (this is what the package was written for — same author as Dcat Admin, so it slots into grid filters): build the filter value into a closure, keep the relation constraint logic in one place, and reuse it for both the filter and the withCount column:

$activePosts = fn ($q) => $q->where('status', 1)->where('published_at', '<=', now());

User::query()
    ->whereHasInCompat('posts', $activePosts)
    ->withCount(['posts' => $activePosts])   // same closure, no drift
    ->paginate(20);

Prefer it over hand-written joins. whereHasIn adds no columns to the outer SELECT, so there's no select users.* hygiene, no row multiplication, no ambiguity when the related table has a status/name column. For "filter by related record" screens it's a cleaner default than a join.

Reach for whereDoesntHaveIn only with a non-nullable key. See the NOT IN pitfall below; in practice the whereHas-with-NOT version is safer for the negative case.

Don't try to express cardinality with it. There is no has('posts', '>=', 3) equivalent. Use withCount + having for those thresholds.

Gotchas and Tips

MySQL 8.0.16+ largely erases the win. The subquery-to-semi-join transformation means EXISTS and IN often produce identical plans. Profile on your actual server version before adopting; the package pays off mainly on MySQL 5.7 or on subqueries complex enough to defeat the optimizer's rewrite.

It can be slower on paginated queries. IN (subquery) is typically materialized once; EXISTS can short-circuit row-by-row. If your outer set is small (e.g. a search narrows to 40 rows) with a LIMIT, the materialization cost is pure overhead. Filter-heavy admin screens with narrow results are the worst case.

NOT IN + NULL returns zero rows. If the subquery's projected key can ever be NULL (nullable custom key, outer join inside the closure), whereDoesntHaveIn silently matches nothing — the classic SQL three-valued-logic trap. Verify the key column is NOT NULL, or use whereHas(...) negated instead.

Never add select()/addSelect() in the closure. The macro relies on the relation's own key projection; overriding it produces a multi-column subquery and a hard SQL error. Same for groupBy/aggregates, which break the single-column contract.

Soft deletes are applied automatically because the subquery runs through the relation's query builder — including its global scopes. ->withTrashed() inside the closure works as expected. Any custom global scope on the related model also fires, which is usually what you want and occasionally a surprise.

Relation type coverage is uneven. hasOne, hasMany, and belongsTo are the well-trodden paths. belongsToMany, morphMany, and hasManyThrough need verification on your version — the macro has to reconstruct the key columns itself, and the pivot/morph cases are where it diverges from core whereHas. Test each relation type once, in a test, before rolling it out broadly.

Last release is 2021; treat it as unmaintained. Laravel 9/10/11 changed Eloquent builder internals (relationship matching, whereHas closure handling). The macro may still fire but is untested against those versions. Pin the constraint, add a test that asserts the generated SQL, and be ready to vendor the macro into AppServiceProvider — it's small enough to own.

Debug the SQL, not the PHP. ->toSql() plus ->getBindings() is the whole story here; the binding order interleaves outer and subquery bindings in positional order, which will bite you if you're merging bindings by hand in a custom repository. For plan-level debugging, DB::listen() and dump the raw query into EXPLAIN ANALYZE rather than trusting dd() output.

Extension point is the macro itself. If you need a variant (e.g. whereIn against a hasManyThrough), extend by registering your own macro in a service provider rather than patching vendor/. That keeps upgrades and removal trivial.