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