How to automatically filter foreign table's deleted_at = null while using join in laravel eloquent?

Question

As it is right now ->join() is a query builder so it wouldn't using Model that has SoftDelete, there is too many codes that used join in my project so I don't want to manually replace it all with eager loading ->with(), anyone know how to do it while keeping the join?

For example I want to get a data like this without adding ->where('tb_b.deleted_at', null) to all my codes:

$data = TabelA::join('tb_b', 'tb_a.id_b', '=', 'tb_b.id_b')->get()

Answer 1

There are basically two ways to solve this

1. By using a whereNull constraint

$data = TabelA::join('tb_b', 'tb_a.id_b', '=', 'tb_b.id_b')
    ->whereNull('tb_b.deleted_at')
    ->get();

2. Creating a global scope in your model (here, I will assume TbB Model). Add the following function in your model class to create a global scope to automatically filter your model get method.

public static function boot()
{
    parent::boot();

    static::addGlobalScope('notDeleted', function (Builder $builder) {
        $builder->where('deleted_at', null);
    });
}

Then, you will do this when you need your data without the deleted data

$data = TabelA::withoutGlobalScope('notDeleted')
    ->join('tb_b', 'tb_a.id_b', '=', 'tb_b.id_b')
    ->get();

Read more on Laravel scope here: https://learn2torials.com/a/laravel8-global-model-scope

Answer 2

Three alternatives

1. Adding a WHERE clause after the join

TabelA::query()
    ->join('tb_b', 'tb_a.id_b', '=', 'tb_b.id_b')
    ->whereNull('tb_b.deleted_at')
    ->get()

2. Adding an AND clause to the join

TabelA::query()
    ->join(function ($join) {
        $join->on('tb_b', 'tb_a.id_b', '=', 'tb_b.id_b')
            ->whereNull('tb_b.deleted_at');
    })
    ->get()

3. Joining on a subquery.

TabelA::query()
    ->joinSub(
        function ($sub) {
            $sub->from('tb_b')
                ->whereNull('tb_b.deleted_at');
        }),
        'tb_b_alias',
        'tb_b_alias.id_b',
        '=',
        'tb_a.id_b'
    )
    ->get()