Eloquent Has By Join
Convert has()
and whereHas()
constraints to join()
ones for single-result relations.
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.
Requirements
- PHP:
^7.3 || ^8.0
- Laravel:
^6.0 || ^7.0 || ^8.0 || ^9.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 |