Convert has()
and whereHas()
constraints to non-dependent subqueries.
- PHP: ^7.1
- Laravel: ^5.8 || ^6.0 || ^7.0
composer require mpyw/eloquent-has-by-non-dependent-subquery
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
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
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')
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 | 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 |
❌ | ✅ |