[FIXED] Laravel 5.5 BelongsToMany with pivot conditions

Issue

I have three models, Clinic, Department and ClinicDepartment and has belongsToMany relation called departments from Clinic to Department using ClinicDepartment's table as pivot table, but when i using this relation, scopes from ClinicDepartment not aplying in query.

I decided to make Pivot model calling ClinicDepartmentPivotand apply those scopes to Pivot, but no luck.

Clinic model:

class Clinic extends Model
{
    use SoftDeletes, ActiveOnly, HasParent;

    public function departments()
    {
        return $this->belongsToMany(Department::class, 'clinic_departments', 'clinic_id', 'department_id')->using(ClinicDepartmentPivot::class);
    }
}

Department Model:

class Department extends Model
{
    use SoftDeletes, ActiveOnly;

    public function clinics()
    {
        return $this->belongsToMany(Clinic::class, 'clinic_departments', 'department_id', 'clinic_id')->using(ClinicDepartmentPivot::class);
    }
}

ClinicDepartmentPivot Model:

class ClinicDepartmentPivot extends Pivot
{
    use ActiveOnly, SoftDeletes;
}

ActiveOnlyScope:

class ActiveOnlyScope implements Scope
{
    public function apply(Builder $builder, Model $model)
    {
        $builder->where($model->getTable() . '.is_active', true);
    }
}

So basicaly I want to apply global scopes to Pivot model, so when I trying to get Clinics of Department, it should check - is ClinicDepartment has is_active = 1 and not deleted.

UPD 1

My scope traits looks like this:

trait ActiveOnly
{
    public static function bootActiveOnly()
    {
        if (!Auth::guard('admin')->check() && strpos(request()->getRequestUri(), 'admin') === false) {
            static::addGlobalScope(new ActiveOnlyScope);
        }
    }
}

Can be used by any model.

Solution

Okay, with some workaround I finally found a working solution that does not look scrappy.

public function clinics()
    {
        return $this->belongsToMany(Clinic::class, 'clinic_departments', 'department_id', 'clinic_id')
            ->where(function (Builder $query) {
                $query->where('clinic_departments.is_active', 1)
                    ->whereNull('clinic_departments.deleted_at');
            });
    }

Actual query looks like this:

select `clinics`.*, `clinic_departments`.`department_id` as `pivot_department_id`, `clinic_departments`.`clinic_id` as `pivot_clinic_id` from `clinics` inner join `clinic_departments` on `clinics`.`id` = `clinic_departments`.`clinic_id` where (`clinic_departments`.`is_active` = 1 and `clinic_departments`.`deleted_at` is null)

Thanks all for the ideas.

Answered By - Oleg Shakhov