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

Question

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

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'));
    }
}

Answer 1

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

Judging from your table records, the participants table seems to be the pivot table between users and messages. You are referencing the pivot table, leading to the database misbehaving.

The correct way to do this is in your users models:

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

In your messages models:

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;

Answer 2

Answered via the Larachat official Slack:

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

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_participant pivot table.

Answer 3

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 p1 and p2 in the correct places, for example

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

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