Error 1066 not unique table alias book

Здравствуйте.Возникла такая проблема.При запросе:

Здравствуйте.
Возникла такая проблема.
При запросе:

mysql> SELECT lr1_1.number, lr1_1.fio, lr1_2.code, lr1_2.name, lr1_4.auditory, lr1_3.code, lr1_3.name
    -> FROM lr1_1 INNER JOIN lessons ON lr1_1.number = lessons.teacher
    -> INNER JOIN lessons ON lr1_2.code = lessons.lesson
    -> INNER JOIN lessons ON lr1_4.auditory = lessons.auditory
    -> INNER JOIN lessons ON lr1_3.code = lessons.cafedry;
 

Выдается ошибка:

ERROR 1066 (42000): Not unique table/alias: ‘lessons’

Структура таблиц:

mysql> DESCRIBE lr1_1;
+———+————-+——+——+———+——-+
| Field   | Type        | Null | Key | Default | Extra |
+———+————-+——+——+———+——-+
| number  | int(11)     | NO   | PRI | 0       |       |
| fio     | varchar(60) | YES  |     | NULL    |       |
| address | varchar(60) | YES  |     | NULL    |       |
| work    | varchar(60) | YES  |     | NULL    |       |
+———+————-+——+——+———+——-+
4 rows in set (0.00 sec)

mysql> DESCRIBE lr1_2;
+————+————-+——+——+———+——-+
| Field      | Type        | Null | Key | Default | Extra |
+————+————-+——+——+———+——-+
| code       | int(11)     | NO   | PRI | 0       |       |
| name       | varchar(60) | YES  |     | NULL    |       |
| hours      | int(11)     | YES  |     | NULL    |       |
| controle   | varchar(60) | YES  |     | NULL    |       |
| categories | varchar(60) | YES  |     | NULL    |       |
+————+————-+——+——+———+——-+
5 rows in set (0.00 sec)

mysql> DESCRIBE lr1_3;
+———-+————-+——+——+———+——-+
| Field    | Type        | Null | Key | Default | Extra |
+———-+————-+——+——+———+——-+
| code     | int(11)     | NO   | PRI | 0       |       |
| name     | varchar(60) | YES  |     | NULL    |       |
| idZavCaf | int(11)     | YES  |     | NULL    |       |
+———-+————-+——+——+———+——-+
3 rows in set (0.00 sec)

mysql> DESCRIBE lr1_4;
+———-+———+——+——+———+——-+
| Field    | Type    | Null | Key | Default | Extra |
+———-+———+——+——+———+——-+
| auditory | char(5) | NO   | PRI |         |       |
+———-+———+——+——+———+——-+
1 row in set (0.00 sec)

mysql> DESCRIBE lessons;
+———-+———+——+——+———+——-+
| Field    | Type    | Null | Key | Default | Extra |
+———-+———+——+——+———+——-+
| teacher  | int(11) | NO   | PRI | 0       |       |
| lesson   | int(11) | NO   | PRI | 0       |       |
| auditory | char(5) | NO   | PRI |         |       |
| cafedry  | int(11) | NO   | PRI | 0       |       |
+———-+———+——+——+———+——-+
4 rows in set (0.00 sec)

Задание лабораторной: Создать связанные таблицы и получить их отношение.

В чем может быть проблема?
Заранее спасибо.

Hi,

I had been trying to use belongsToMany relationship for following tables

faculties table
id
name

proficiencies table
id
name

faculty_proficiencies table
faculty_id
proficiency_id

Following is my model files

<?php

namespace AppModelsMT;

use AppModelsMTFacultyProficiency;
use IlluminateDatabaseEloquentModel;

class Faculty extends Model
{
    protected $table = "faculties";

    protected $fillable = ['name'];

    public function proficiency() {
        return $this->belongsToMany(FacultyProficiency::class, 'faculty_proficiencies', 'faculty_id', 'proficiency_id');
    }
}


<?php

namespace AppModelsMT;

use IlluminateDatabaseEloquentModel;

class FacultyProficiency extends Model
{
    protected $table = "faculty_proficiencies";

    protected $fillable = ['faculty_id', 'proficiency_id'];

    public $timestamps = false;

    protected function faculty() {
        return $this->belongsTo(AppModelsMTFaculty::class, 'faculty_id');
    }

    protected function proficiency() {
        return $this->belongsTo(AppModelsMTProficiency::class, 'proficiency_id');
    }
}

<?php

namespace AppModelsMT;

use IlluminateDatabaseEloquentModel;

class Proficiency extends Model
{
    protected $table = "proficiencies";

    protected $fillable = ['name'];

    public $timestamps = false;

    public function faculty() {
        return $this->belongsToMany(FacultyProficiency::class, 'faculty_proficiencies', 'proficiency_id', 'faculty_id');
    }
}

Now, when I try getting faculty with proficiency like the following,

I get following error

Syntax error or access violation: 1066 Not unique table/alias: 'faculty_proficiencies' 
(SQL: 
select 
    `faculty_proficiencies`.*, 
    `faculty_proficiencies`.`faculty_id` as `pivot_faculty_id`, 
    `faculty_proficiencies`.`proficiency_id` as `pivot_proficiency_id` 
from 
    `faculty_proficiencies` 
inner join 
    `faculty_proficiencies` 
on 
    `faculty_proficiencies`.`id` = `faculty_proficiencies`.`proficiency_id` 
where 
    `faculty_proficiencies`.`faculty_id` = 5) 

So, I am receiving the following error from 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 relatioship looks like this:

public function participants()
    {
        return $this->belongsToMany('NamespaceModulesEmailModelsParticipant', 'participants', 'message_id', 'user_id')->withTimestamps();
    }

and I am trying to call it like this:

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

Why am I getting this error? What is 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 IlluminateDatabaseEloquentRelationsBelongsTo
     */
    public function user()
    {
        return $this->belongsTo(Config::get('email.user_model'));
    }

    public function assets()
    {
        return $this->belongsToMany('NamespaceModulesAssetsModelsAsset', 'message_assets');
    }

    /**
     * Participants relationship
     *
     * @return IlluminateDatabaseEloquentRelationsHasMany
     */
    public function participants()
    {
        return $this->belongsToMany('NamespaceModulesEmailModelsParticipant', 'participants', 'message_id', 'user_id')->withTimestamps();
    }

    /**
     * Recipients of this message
     *
     * @return IlluminateDatabaseEloquentRelationsHasMany
     */
    public function recipients()
    {
        return $this->participants()->where('user_id', '!=', $this->user_id);
    }

    /**
     * Returns the latest message from a thread
     *
     * @return NamespaceModulesEmailModelsMessage
     */
    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 IlluminateDatabaseEloquentRelationsBelongsTo
     */
    public function message()
    {
        return $this->hasMany('NamespaceModulesEmailModelsMessage');
    }

    /**
     * User relationship
     *
     * @return IlluminateDatabaseEloquentRelationsBelongsTo
     */
    public function user()
    {
        return $this->belongsTo(Config::get('email.user_model'));
    }
}


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('NamespaceModulesEmailModelsParticipant', '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.

Понравилась статья? Поделить с друзьями:
  • Error 1065 query was empty
  • Error 1064 you have an error in your sql syntax check the manual that corresponds
  • Error 1064 mysql при создании таблицы
  • Error 1064 mysql you have an error in your sql syntax
  • Error 1064 mysql foreign key