Здравствуйте.
Возникла такая проблема.
При запросе:
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.