mpyw / eloquent-has-by-join
Convert has() and whereHas() constraints to join() ones for single-result relations.
Installs: 104 845
Dependents: 0
Suggesters: 2
Security: 0
Stars: 25
Watchers: 4
Forks: 1
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-non-dependent-subquery: Provides more versatile non-dependent subqueries
README
Convert has()
and whereHas()
constraints to join()
ones for single-result relations.
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-join
Motivation
Suppose you have the following relationship:
class Post extends Model { use SoftDeletes; }
class Comment extends Model { use SoftDeletes; public function post(): BelongsTo { return $this->belongsTo(Post::class); } }
If you use has()
constraints, your actual query would have dependent subqueries.
$comments = Comment::has('post')->get();
select * from `comments` where exists ( select * from `posts` where `comments`.`post_id` = `posts`.`id` and `posts`.`deleted_at` is null ) and `comments`.`deleted_at` is null
These subqueries may cause performance degradations.
This package provides Illuminate\Database\Eloquent\Builder::hasByJoin()
macro to solve this problem:
you can easily transform dependent subqueries into simple JOIN queries.
$comments = Comment::hasByJoin('post')->get();
select `comments`.* from `comments` inner join `posts` on `comments`.`post_id` = `posts`.`id` and `posts`.`deleted_at` is null where `comments`.`deleted_at` is null
API
Signature
Illuminate\Database\Eloquent\Builder::hasByJoin(string|string[] $relationMethod, ?callable ...$constraints): $this
Arguments
$relationMethod
A relation method name that returns a BelongsTo
, HasOne
or MorphOne
instance.
Builder::hasByJoin('post')
You can pass nested relations as an array or a string with dot-chain syntax.
Builder::hasByJoin(['post', 'author'])
Builder::hasByJoin('post.author')
You can provide table aliases with "as"
syntax.
Builder::hasByJoin(['post as messages', 'author as message_authors'])
$constraints
Additional callable
constraints for relations that take Illuminate\Database\Eloquent\Builder
as the first argument.
Builder::hasByJoin('post', fn (Builder $query) => $query->withTrashed())
The first closure corresponds to post
and the second one corresponds to author
.
Builder::hasByJoin( 'post.author', fn (Builder $query) => $query->withTrashed(), fn (Builder $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 |
❌ | ✅ |