MySQL SQLSTATE[42000]：语法错误或访问冲突：1066 不是唯一的关系表/别名

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias on relationship

提问by ChrisBratherton

So, I'm receiving the following error from Laravel framework; but I couldn't find why this framework is producing this error:

所以，我从 Laravel 框架收到以下错误；但我找不到为什么这个框架会产生这个错误：

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'participants' (SQL: select `participants`.*, `participants`.`message_id` as `pivot_message_id`, `participants`.`user_id` as `pivot_user_id`, `participants`.`created_at` as `pivot_created_at`, `participants`.`updated_at` as `pivot_updated_at` from `participants` inner join `participants` on `participants`.`id` = `participants`.`user_id` where `participants`.`deleted_at` is null and `participants`.`message_id` in (2))

My message/participants relationship looks like this:

我的消息/参与者关系如下所示：

public function participants()
{
    return $this->belongsToMany('Namespace\Modules\Email\Models\Participant', 'participants', 'message_id', 'user_id')->withTimestamps();
}

and I'm trying to call it like this:

我试图这样称呼它：

public function getAllMessages()
{
   return Message::with('user')->with('participants')->get();
}

Why am I getting this error? What's going on?

为什么我收到这个错误？这是怎么回事？

Edit: Included full models

编辑：包括完整模型

Message

信息

class Message extends Eloquent
{
    use PublishedTrait;
    use SoftDeletingTrait;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'messages';

    /**
     * The attributes that can be set with Mass Assignment.
     *
     * @var array
     */
    protected $fillable = ['subject', 'user_id', 'body', 'status'];

    /**
     * The attributes that should be mutated to dates.
     *
     * @var array
     */
    protected $dates = ['created_at', 'updated_at', 'deleted_at'];

    /**
     * Validation rules.
     *
     * @var array
     */
    protected $rules = [
        'subject' => 'required|max:255',
        'body' => 'required',
    ];

    /**
     * User relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user()
    {
        return $this->belongsTo(Config::get('email.user_model'));
    }

    public function assets()
    {
        return $this->belongsToMany('Namespace\Modules\Assets\Models\Asset', 'message_assets');
    }

    /**
     * Participants relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function participants()
    {
        return $this->belongsToMany('Namespace\Modules\Email\Models\Participant', 'participants', 'message_id', 'user_id')->withTimestamps();
    }

    /**
     * Recipients of this message
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function recipients()
    {
        return $this->participants()->where('user_id', '!=', $this->user_id);
    }

    /**
     * Returns the latest message from a thread
     *
     * @return Namespace\Modules\Email\Models\Message
     */
    public function getLatestMessageAttribute()
    {
        return $this->messages()->latest()->first();
    }

    /**
     * Returns threads that the user is associated with
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeForUser($query, $userId)
    {
        return $query->join('participants', 'messages.id', '=', 'participants.message_id')
            ->where('participants.user_id', $userId)
            ->where('participants.deleted_at', null)
            ->select('messages.*');
    }

    /**
     * Returns threads that the user is associated with
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeForUserWithDeleted($query, $userId)
    {
        return $query->join('participants', 'messages.id', '=', 'participants.message_id')
            ->where('participants.user_id', $userId)
            ->select('messages.*');
    }

    /**
     * Returns messages that the user has sent
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeByUser($query, $userId)
    {
        return $query->where('user_id', $userId);
    }

    /**
     * Returns threads with new messages that the user is associated with
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeForUserWithNewMessages($query, $userId)
    {
        return $query->join('participants', 'messages.id', '=', 'participants.message_id')
            ->where('participants.user_id', $userId)
            ->whereNull('participants.deleted_at')
            ->where(function ($query) {
                $query->where('messages.updated_at', '>', $this->getConnection()->raw($this->getConnection()->getTablePrefix() . 'participants.last_read'))
                    ->orWhereNull('participants.last_read');
            })
            ->select('messages.*');
    }

}

Participant

参与者

class Participant extends Eloquent
{
    use SoftDeletingTrait;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'participants';

    /**
     * The attributes that can be set with Mass Assignment.
     *
     * @var array
     */
    protected $fillable = ['message_id', 'user_id', 'last_read'];

    /**
     * The attributes that should be mutated to dates.
     *
     * @var array
     */
    protected $dates = ['created_at', 'updated_at', 'deleted_at', 'last_read'];

    /**
     * Thread relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function message()
    {
        return $this->hasMany('Namespace\Modules\Email\Models\Message');
    }

    /**
     * User relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user()
    {
        return $this->belongsTo(Config::get('email.user_model'));
    }
}

采纳答案by Jamesking56

Answered via the Larachat official Slack:

通过 Larachat 官方 Slack 回答：

The relationship is missing a pivot table for this to work. The second argument in the participantsmethod is the pivot table to use:

关系缺少一个数据透视表来工作。该participants方法中的第二个参数是要使用的数据透视表：

public function participants()
{
    return $this->belongsToMany('Namespace\Modules\Email\Models\Participant', 'PIVOT', 'message_id', 'user_id')->withTimestamps();
}

Therefore, you can't use participants as the pivot because it is one of the tables in the relationship, you need a message_participantpivot table.

因此，您不能使用参与者作为数据透视表，因为它是关系中的表之一，您需要一个message_participant数据透视表。

回答by Jim Garrison

Your error is

你的错误是

...from `participants` inner join `participants` ...

You need to provide aliases for each reference, as in

您需要为每个引用提供别名，如

...from `participants` p1 inner join `participants` p2 ...

and then use p1and p2in the correct places, for example

然后在正确的地方使用p1和p2，例如

...on p1.`id` = p2.`user_id` ...

(I'm guessing on which is p1and which is p2; you have to make that determination)

（我在猜测哪个是p1，哪个是p2；你必须做出决定）

回答by AntonyMN

Answering this question for anyone who encountered this error much later.

为以后遇到此错误的任何人回答此问题。

Judging from your table records, the participants tableseems to be the pivot tablebetween usersand messages. You are referencing the pivot table, leading to the database misbehaving.

从您的表记录来看，participants table似乎是pivot table介于users和之间messages。您正在引用数据透视表，导致数据库行为异常。

The correct way to do this is in your usersmodels:

正确的方法是在您的users模型中：

public function messages(){
   return $this->belongsToMany(Message::class, 'participants', 'user_id', 'message_id')->withPivot('last_read');
}

In your messagesmodels:

在您的messages模型中：

public function users(){
   return $this->belongsToMany(User::class, 'participants', 'message_id', 'user_id')->withPivot('last_read');
}

That way, when you calling messages from users, use this:

这样，当您从用户调用消息时，请使用以下命令：

$messages = $user->messages()->get();

And when you checking the users for the message,

当你检查用户的消息时，

$user = $message->users()->get()

And last read

最后阅读

$last_read = $message->pivot->last_read;