Laravel多对多关联报错SQLSTATE[42000]：1066表别名不唯一

问题场景 #

现有三张数据表：booking、category、pivot_booking，定义模型关联并执行查询后出现SQL语法错误：

Booking模型关联代码 #

public function category()
{
    return $this->belongsToMany(\Modules\Backend\Entities\Category::class, "pivot_booking","booking_id","category_id")->withPivot('count');
}

Category模型关联代码 #

public function bookings()
{
    return $this->belongsToMany(\Modules\Frontend\Entities\Booking::class, "pivot_booking","booking_id","category_id")->withPivot('count');
}

控制器查询代码 #

$booking = \Modules\Frontend\Entities\Booking::with('category')->where('id',$decrypted)->first();

执行后报错：

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'pivot_booking' (SQL: select pivot_booking.*, pivot_booking.booking_id as pivot_booking_id, pivot_booking.category_id as pivot_category_id, pivot_booking.count as pivot_count from pivot_booking inner join pivot_booking on pivot_booking.id = pivot_booking.category_id where pivot_booking.booking_id = 1255)

错误原因 #

Category模型中belongsToMany方法的外键参数顺序完全写反：

• belongsToMany的第三个参数是当前模型在中间表的外键，第四个参数是关联模型在中间表的外键
• 原代码把关联模型Booking的外键booking_id放在了第三个参数位置，当前模型Category的外键category_id放在第四个参数，导致Laravel生成的SQL错误地将中间表与自身做关联，触发表别名重复的语法错误。

修复方案 #

修改Category模型的bookings关联方法，调换外键参数顺序：

public function bookings()
{
    return $this->belongsToMany(\Modules\Frontend\Entities\Booking::class, "pivot_booking","category_id","booking_id")->withPivot('count');
}

修改后重新执行控制器查询代码，即可正常获取关联数据。

内容的提问来源于stack exchange，提问作者Jerry Jones