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

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

v2.0.0 2021-01-25 09:06 UTC

This package is auto-updated.

Last update: 2021-07-31 06:35:21 UTC


README

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

Requirements

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

Installing

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

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())

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