Eloquent Has By Non-dependent Subquery

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

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.

Requirements

PHP: ^7.3 || ^8.0
Laravel: ^6.0 || ^7.0 || ^8.0 || ^9.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`	❌	✅

Cheap and reliable Node.js hosting starts at $3/month, and $1/month static HTML hosting

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

Programming Languages

Labels

Projects that are alternatives of or similar to eloquent-has-by-non-dependent-subquery