多表关联,两个查询串联,多出很多不需要的 sql 查询语句?
问题描述
这是一个获取用户个人信息的Api,用户头像存储到了 attachments
附件表中,使用 avatar
方法进行关联,logs
是用户操作日志表,使用 logs
方法进行关联。
我只想查询一次附件表将 用户头像查询出来,但是不知道为什么只要关联了 logs
多出了好多查询头像的sql语句(见下方返回结果中),应该如何处理呢?
控制器代码
public function profile(){
$result = Auth::user()
->with([ 'logs' => function( $query ) {
return $query->limit(15)->orderBy('id', 'DESC');
},'avatar'])->first();
return $this->success(
new UserResource( $result )
);
}
User模型代码
public function logs(){
return $this->hasMany('App\Models\Log');
}
public function avatar(){
return $this->hasOne('App\Models\Attachment', 'relate')->where('type', 'user_avatar');
}
public function getAvatarUrlAttribute($value){
if(!empty($this->avatar)){
return $this->avatar->url;
}else{
return 'http://'.$_SERVER['HTTP_HOST'].'/images/avatar.png';
}
}
UserResource 代码
public function toArray($request) {
$obj = $this;
return [
'id' => $this->id,
'avatar' => $this->when(
$this->relationLoaded('avatar'), function() use ($obj){
return $obj->avatar_url;
}),
'name' => (string) $this->name,
'logs' => LogResource::collection($this->whenLoaded('logs')),
];
}
LogResource代码
public function toArray($request) {
return [
'id' => (int) $this->id,
'user_id' => (int) $this->user_id,
'name' => $this->name,
'code' => (string) $this->code,
'message' => $this->message,
'os' => (string) $this->os,
'ip' => (string) $this->ip,
'broswer' => (string) $this->broswer,
'user' => new UserResource($this->whenLoaded('user')),
'created_at' => (string) $this->created_at,
];
}
返回结果
{
"code": "success",
"message": "成功",
"sqls": [
"select * from `users` limit 1",
"select * from `users` where `type` = 'B' and `brand_id` = 1",
"select * from `attachments` where `attachments`.`relate` = 1 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 41 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 37 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 39 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 40 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 42 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 43 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 44 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `attachments` where `attachments`.`relate` = 45 and `attachments`.`relate` is not null and `type` = 'user_avatar' and `brand_id` = 1 and `agent_id` = 0 limit 1",
"select * from `logs` where `logs`.`user_id` in (1) and `user_id` in (1, 41, 37, 39, 40, 42, 43, 44, 45) order by `id` desc limit 3",
"select * from `attachments` where `type` = 'user_avatar' and `attachments`.`relate` in (1) and `brand_id` = 1 and `agent_id` = 0",
"insert into `logs` (`type`, `name`, `code`, `user_id`, `os`, `ip`, `agent`, `broswer`, `updated_at`, `created_at`) values ('operate', 'Api\\MainController@profile', 'success', 1, 'Postman', '127.0.0.1', 'PostmanRuntime/7.1.5', 'Postman(7.1.5)', '2018-05-26 12:44:10', '2018-05-26 12:44:10')"
],
"result": {
"id": 1,
"avatar": "http://api.weineng.me/images/avatar.png",
"name": "admin",
"logs": [
{
"id": 235,
"user_id": 1,
"name": "Api\\MainController@profile",
"code": "RelationNotFound",
"message": "失败,",
"os": "Postman",
"ip": "127.0.0.1",
"broswer": "Postman(7.1.5)",
"created_at": "2018-05-26 12:26:15"
},
{
"id": 234,
"user_id": 1,
"name": "Api\\MainController@profile",
"code": "RelationNotFound",
"message": "失败,",
"os": "Postman",
"ip": "127.0.0.1",
"broswer": "Postman(7.1.5)",
"created_at": "2018-05-26 12:18:26"
},
{
"id": 233,
"user_id": 1,
"name": "Api\\MainController@profile",
"code": "FatalThrowableError",
"message": "失败,致命错误",
"os": "Postman",
"ip": "127.0.0.1",
"broswer": "Postman(7.1.5)",
"created_at": "2018-05-26 12:05:10"
}
]
}
}
推荐文章: