在Laravel的多对多关系中使用全局作用域出现SQLSTATE[23000]错误

huangapple
go评论106阅读模式
英文:

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

问题

主题 1：我在我的项目中使用 Laravel 版本 7，并为了在 Laravel 的所有模型中添加查询，我已经为所有模型添加了一个全局范围。这样，我的所有模型都继承自另一个模型。下面是提到的模型。

  1. namespace App\Models;
  3. use Illuminate\Database\Eloquent\Builder;
  5. class Model extends \Illuminate\Database\Eloquent\Model
  6. {
  7.     /**
  8.      * 模型的“启动”方法。
  9.      * @return void
  10.      */
  11.     protected static function boot()
  12.     {
  13.         parent::boot();
  14.         if (!empty(Client::$Current_Token)) {
  15.             static::addGlobalScope('client_token', function (Builder $builder) {
  16.                 $builder->where('client_token', Client::$Current_Token);
  17.             });
  18.         }
  19.     }
  20. }

主题 2：有一个名为 "user" 的模型和一个名为 "role" 的模型，这两个表之间有一个多对多的关系。现在，想象一下，我想在 "user" 模型中使用 belongsToMany 方法检索与用户关联的所有角色，基于它们在中间表中定义的关系。

  1. /**
  2. * 属于用户的角色。
  3. */
  4. public function roles(): BelongsToMany
  5. {
  6.     return $this->belongsToMany(Role::class, 'role_user', 'user_id', 'role_id');
  7. }

问题：我遇到了以下错误：SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'client_token' in the WHERE clause is ambiguous，我知道这与我在全局范围中添加的条件有关。

英文:

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.

  1. namespace App\Models;
  3. use Illuminate\Database\Eloquent\Builder;
  5. class Model extends \Illuminate\Database\Eloquent\Model
  6. {
  7.     /**
  8.      * The "booting" method of the model.
  9.      * @return void
  10.      */
  11.     protected static function boot()
  12.     {
  13.         parent::boot();
  14.         if(!empty(Client::$Current_Token)) {
  15.             static::addGlobalScope('client_token', function (Builder $builder) {
  16.                 $builder->where('client_token', Client::$Current_Token);
  17.             });
  18.         }
  19.     }
  20. }

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.

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

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.

答案1

得分: 0

我相信你之所以收到该错误是因为你的多个表中都有client_token列。所以当你执行涉及多个表的数据库查询时，它不知道你在谈论哪个client_token列。

让我们创建一个作用域类，以便我们可以访问模型的表名：

  1. <?php
  3. namespace App\Scopes;
  5. use Illuminate\Database\Eloquent\Scope;
  6. use Illuminate\Database\Eloquent\Model;
  7. use Illuminate\Database\Eloquent\Builder;
  9. class ClientTokenScope implements Scope
  10. {
  11.     /**
  12.      * 将作用域应用于给定的Eloquent查询构建器。
  13.      *
  14.      * @param  \Illuminate\Database\Eloquent\Builder  $builder
  15.      * @param  \Illuminate\Database\Eloquent\Model  $model
  16.      * @return void
  17.      */
  18.     public function apply(Builder $builder, Model $model)
  19.     {
  20.         $builder->where("{$model->getTable()}.client_token", Client::$Current_Token);
  21.     }
  22. }

然后在boot方法中应用该作用域：

  1. namespace App\Models;
  3. use App\Scopes\ClientTokenScope;
  4. use Illuminate\Database\Eloquent\Builder;
  6. class Model extends \Illuminate\Database\Eloquent\Model
  7. {
  8.     /**
  9.      * 模型的“booting”方法。
  10.      * @return void
  11.      */
  12.     protected static function boot()
  13.     {
  14.         parent::boot();
  15.         if (!empty(Client::$Current_Token)) {
  16.             static::addGlobalScope(new ClientTokenScope);
  17.         }
  18.     }
  19. }
英文:

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:

  1. &lt;?php
  2.  
  3. namespace App\Scopes;
  4.  
  5. use Illuminate\Database\Eloquent\Scope;
  6. use Illuminate\Database\Eloquent\Model;
  7. use Illuminate\Database\Eloquent\Builder;
  8.  
  9. class ClientTokenScope implements Scope
  10. {
  11.     /**
  12.      * Apply the scope to a given Eloquent query builder.
  13.      *
  14.      * @param  \Illuminate\Database\Eloquent\Builder  $builder
  15.      * @param  \Illuminate\Database\Eloquent\Model  $model
  16.      * @return void
  17.      */
  18.     public function apply(Builder $builder, Model $model)
  19.     {
  20.         $builder-&gt;where(&quot;{$model-&gt;getTable()}.client_token&quot;, Client::$Current_Token);
  21.     }
  22. }

Then applying the scope in the boot method:

  1. namespace App\Models;
  3. use App\Scopes\ClientTokenScope;
  5. use Illuminate\Database\Eloquent\Builder;
  7. class Model extends \Illuminate\Database\Eloquent\Model
  8. {
  9.     /**
  10.      * The &quot;booting&quot; method of the model.
  11.      * @return void
  12.      */
  13.     protected static function boot()
  14.     {
  15.         parent::boot();
  16.         if(!empty(Client::$Current_Token)) {
  17.             static::addGlobalScope(new ClientTokenScope);
  18.         }
  19.     }
  20. }

huangapple
  • 本文由 发表于 2023年6月1日 23:41:15
  • 转载请务必保留本文链接：https://go.coder-hub.com/76383599.html
  • laravel
  • laravel-7
  • many-to-many
  • mysql
  • php
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定