mpyw / eloquent-has-by-non-dependent-subquery
Convert has() and whereHas() constraints to non-dependent subqueries.
Installs: 375 193
Dependents: 1
Suggesters: 1
Security: 0
Stars: 84
Watchers: 4
Forks: 5
Open Issues: 0
Requires
- php: ^7.3 || ^8.0
- illuminate/database: ^6.0 || ^7.0 || ^8.0 || ^9.0 || ^10.0
- illuminate/support: ^6.0 || ^7.0 || ^8.0 || ^9.0 || ^10.0
Requires (Dev)
- awobaz/compoships: ^2.0.4
- nilportugues/sql-query-formatter: ^1.2.2
- orchestra/testbench: *
- orchestra/testbench-core: ^4.9 || ^5.9 || >=6.6
- phpunit/phpunit: >=9.5
Suggests
- mpyw/eloquent-has-by-join: Provides simple JOIN queries that do not depend optimizers
README
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 |
❌ | ✅ |