mpyw/eloquent-has-by-join

Convert has() and whereHas() constraints to join() ones for single-result relations.

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

This package is auto-updated.

Last update: 2021-07-31 06:31:13 UTC


README

Convert has() and whereHas() constraints to join() ones for single-result relations.

Requirements

  • PHP: ^7.3 || ^8.0
  • Laravel: ^6.0 || ^7.0 || ^8.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