Ad

Laravel Self Referencing Scope

- 1 answer

Is it possible to apply self referencing criteria within a scope?

I need to write a scope which will allow users to retrieve the supplier related to their account.

class Account extends Model
{
    public function supplier(): BelongsTo
    {
        return $this->belongsTo(self::class, 'supplier_id');
    }

    public function customers(): HasMany
    {
        return $this->hasMany(self::class, 'supplier_id');
    }
}

I have tried the following:

class SupplierAccessScope implements Scope
{
    public function apply(Builder $builder, Model $model)
    {
        /*
         * Join accounts onto itself - does not seem to work
         */
        $builder->join('accounts as a2', 'a2.id', '=', 'accounts.supplier_id');

        // Reference using own column - produces error
        $builder->where('accounts.id', 'accounts.supplier_id');
    }
}
Ad

Answer

It looks like I was going about this the wrong way. Once I realised that I needed to return extra results rather than adding extra columns, I reduced the SQL query required to:

SELECT accounts.id
FROM accounts
WHERE accounts.id IN ([account IDs])
OR accounts.id IN (SELECT accounts.supplier_id FROM accounts WHERE accounts.id IN([account ID]))

And so my global scope ended up looking like this:

 public function apply(Builder $builder, Model $model)
 {
    if (!auth()->guard('admin')->check() && auth()->user()) {
        $accountIds = AccountAccess::where('user_id', auth()->user()->id)->get()->pluck('account_id');

        $builder->where(function ($builder) use ($accountIds) {
            $builder->whereIn('accounts.id', $accountIds);
            $builder->orWhereIn('accounts.supplier_id', $accountIds);
            $builder->orWhereRaw('accounts.id IN (SELECT supplier_id FROM accounts WHERE id IN (?))', $accountIds);
        });
    }

    return $builder;
}
Ad
source: stackoverflow.com
Ad