Skip to content
This repository has been archived by the owner on Nov 21, 2023. It is now read-only.

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

License

Notifications You must be signed in to change notification settings

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

Repository files navigation

Eloquent Has By Non-dependent Subquery Build Status Coverage Status Scrutinizer Code Quality

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