Getting SQLSTATE[23000] error with Laravel global scope in many-to-many relationship

Question

Subject 1: I am using Laravel version 7 in my project, and in order to add a query to all my models in Laravel, I have added a global scope to all my models. In this way, all my models inherit from another model. The mentioned model is provided below.

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;

class Model extends \Illuminate\Database\Eloquent\Model
{
    /**
     * The "booting" method of the model.
     * @return void
     */
    protected static function boot()
    {
        parent::boot();
        if(!empty(Client::$Current_Token)) {
            static::addGlobalScope('client_token', function (Builder $builder) {
                $builder->where('client_token', Client::$Current_Token);
            });
        }
    }
}

Subject 2: There is a model named "user" and a model named "role", and there is a many-to-many relationship between these 2 tables. Now, imagine that I want to retrieve all the roles associated with a user in the "user" model using the belongsToMany method, based on their relationship defined in the intermediate table.

/**
* The roles that belong to the user.
*/
public function roles(): BelongsToMany
{
    return $this->belongsToMany(Role::class, 'role_user', 'user_id', 'role_id');
}

Problem: I encounter the following error: SQLSTATE\[23000\]: Integrity constraint violation: 1052 Column 'client_token' in the WHERE clause is ambiguous and I know it is related to the condition I added in the global scope.

Answer 1

I believe you got that error because a number of your tables are having that client_token column. So when you got a database query involving multiple tables, it just doesn't know which client_token column you are talking about.

Lets create a scope class so we can access the table name of the model:

<?php
 
namespace App\Scopes;
 
use Illuminate\Database\Eloquent\Scope;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;
 
class ClientTokenScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $builder
     * @param  \Illuminate\Database\Eloquent\Model  $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        $builder->where("{$model->getTable()}.client_token", Client::$Current_Token);
    }
}

Then applying the scope in the boot method:

namespace App\Models;

use App\Scopes\ClientTokenScope;

use Illuminate\Database\Eloquent\Builder;

class Model extends \Illuminate\Database\Eloquent\Model
{
    /**
     * The "booting" method of the model.
     * @return void
     */
    protected static function boot()
    {
        parent::boot();
        if(!empty(Client::$Current_Token)) {
            static::addGlobalScope(new ClientTokenScope);
        }
    }
}