mpyw/eloquent-has-by-join

This package is abandoned and no longer maintained. No replacement package was suggested.

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

v2.0.2 2022-07-14 19:34 UTC

This package is auto-updated.

Last update: 2023-11-21 17:26:23 UTC


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