Eloquent Has By Non-dependent Subquery

Convert has() and whereHas() constraints to non-dependent subqueries.

Important

NOTICE: Postgres' optimizer is very smart and covers JOIN optimization for dependent (correlated) subqueries. Therefore, this library is mainly targeted at MySQL which has a poor optimizer.

Caution

UPDATE: MySQL's optimizer has also been updated in version 8.0.16 to include optimizations similar to PostgreSQL. This library is no longer maintained.

Requirements

• PHP: ^7.3 || ^8.0
• Laravel: ^6.0 || ^7.0 || ^8.0 || ^9.0 || ^10.0

Installing

composer require mpyw/eloquent-has-by-non-dependent-subquery

Suggestion

You can install wimski/laravel-ide-helper-hook-eloquent-has-by-non-dependent-subquery to work with Laravel IDE Helper.

Motivation

Suppose you have the following relationship:

class Post extends Model
{
    use SoftDeletes;

    public function comments(): HasMany
    {
        return $this->hasMany(Comment::class);
    }
}

class Comment extends Model
{
    use SoftDeletes;
}

If you use has() constraints, your actual query would have dependent subqueries.

$posts = Post::has('comments')->get();

select * from `posts` where exists (
  select * from `comments`
  where `posts`.`id` = `comments`.`post_id`
    and `comments`.`deleted_at` is null
) and `posts`.`deleted_at` is null

These subqueries may cause performance degradations. This package provides Illuminate\Database\Eloquent\Builder::hasByNonDependentSubquery() macro to solve this problem: you can easily transform dependent subqueries into non-dependent ones.

$posts = Post::hasByNonDependentSubquery('comments')->get();

select * from `posts`
where `posts`.`id` in (
  select `comments`.`post_id` from `comments`
  where `comments`.`deleted_at` is null
)
and `posts`.`deleted_at` is null

API

Signature

Illuminate\Database\Eloquent\Builder::hasByNonDependentSubquery(string|string[] $relationMethod, ?callable ...$constraints): $this

Illuminate\Database\Eloquent\Builder::orHasByNonDependentSubquery(string|string[] $relationMethod, ?callable ...$constraints): $this

Illuminate\Database\Eloquent\Builder::doesntHaveByNonDependentSubquery(string|string[] $relationMethod, ?callable ...$constraints): $this

Illuminate\Database\Eloquent\Builder::orDoesntHaveByNonDependentSubquery(string|string[] $relationMethod, ?callable ...$constraints): $this

Arguments

$relationMethod

A relation method name that returns a Relation instance except MorphTo.

Builder::hasByNonDependentSubquery('comments')

You can pass nested relations as an array or a string with dot-chain syntax.

Builder::hasByNonDependentSubquery(['comments', 'author'])

Builder::hasByNonDependentSubquery('comments.author')

$constraints

Additional callable constraints for relations that take Illuminate\Database\Eloquent\Relation as the first argument.

Builder::hasByNonDependentSubquery('comments', fn (HasMany $query) => $query->withTrashed())

If you are using a union type as of PHP 8.0, the order of types does not matter.

// This will work
Builder::hasByNonDependentSubquery('comments', fn (HasMany|Comment $query) => $query->withTrashed())
// and so will this
Builder::hasByNonDependentSubquery('comments', fn (Comment|HasMany $query) => $query->withTrashed())

The first closure corresponds to comments and the second one corresponds to author.

Builder::hasByNonDependentSubquery(
    'comments.author',
    fn (HasMany $query) => $query->withTrashed(),
    fn (BelongsTo $query) => $query->whereKey(123)
)

Feature Comparison

Feature	mpyw/eloquent-has-by-join	mpyw/eloquent-has-by-non-dependent-subquery
Minimum Laravel version	5.6	5.8
Argument of optional constraints	Illuminate\Database\Eloquent\Builder	Illuminate\Database\Eloquent\Relations\* (Builder can be also accepted by specifying argument type)
Compoships support	✅	❌
No subqueries	✅	❌ (Performance depends on database optimizers)
No table collisions	❌ (Sometimes you need to give aliases)	✅
No column collisions	❌ (Sometimes you need to use qualified column names)	✅
OR conditions	❌	✅
Negative conditions	❌	✅
Counting conditions	❌	❌
HasOne	✅	✅
HasMany	❌	✅
BelongsTo	✅	✅
BelongsToMany	❌	✅
MorphOne	✅	✅
MorphMany	❌	✅
MorphTo	❌	❌
MorphMany	❌	✅
MorphToMany	❌	✅
HasOneThrough	❌	✅
HasManyThrough	❌	✅