Eloquent Has By Join

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