如果一个项目中存在这么一个sql条件在任何情况下或大多数情况都会被使用,同时很容易被开发者遗忘,那么就非常适用于今天要提到的这个功能,Eloquent\Model的全局作用域。
首先看一个示例,有个数据表,结构如下:
现用Laravel写一个查询语句:
$post = PostModel::where('cate_id', 2)->toSql();
打印$post结果是:"select * from `news_post` where `cate_id` = ?"
现在就是只想查属于ID为001的学校的post数据,其他许多表都有这个school_id字段,都需要在查询时使用。这就是我们需要解决的简化的操作,实现自动在sql条件中添加"and school_id='ID001'"
结合官方文档和搜索引擎查询结果,找到一个实现方案就是设置全局作用域:
首先创建基础Model并 添加scope:
namespace App\Models;use App\Models\Scopes\MyScopes;
use Illuminate\Database\Eloquent\Model;class BaseModel extends Model
{public $school_id = '';public $alias = '';public function __construct(array $attributes = []){$this->bootIfNotBooted();$this->initializeTraits();$this->syncOriginal();$token = app()->get('mytoken');$user = getDataByToken($token); //根据token获取存储在redis里的用户数据if(!empty($user) && isset($user['school_id'])){$attributes['school_id'] = $this->school_id = $user['school_id'];}$this->fill($attributes);}protected static function boot(){parent::boot();static::addGlobalScope(new MyScopes());}}
在构造方法中通过登录token信息获取用户的基础信息包括所属学校ID,赋值给model属性,同时在boot中addGlobalScope全局添加自定义作用域,MyScopes如下:
namespace App\Models\Scopes;use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;class MyScopes implements Scope{public function apply(Builder $builder, Model $model){$from = $builder->getQuery()->from;//表 + 别名(如果有的话)if(is_string($from)){ //不考虑非字符串情况,如子查询$fromArr = explode(' ', str_ireplace(' as ', ' ', $from));$alias = isset($fromArr[1]) ? $fromArr[1] : '';//添加全局条件if(!empty($model->school_id)){$fieldName = $alias ? $alias . '.' . 'school_id' : 'school_id';$builder->where($fieldName, $model->school_id);}}}
}
根据model属性是否有值决定是否添加查询条件,$builder->getQuery()获取到的是Query\Builder实例:
同时依据from来判断是否存在别名的情况,附加别名,适配为表设置别名或联查情况。
最后在后续创建Model时都需要继承BaseModel即可。
这样重新执行最开始的sql查询语句,打印出来的结果就是:
"select * from `news_post` where `cate_id` = ? and `school_id` = ?"
这样就达到了预期的效果,自动添加查询条件了,下面再试试增删改
更新:
DB::enableQueryLog();
PostModel::where('cate_id', 2)->update(['is_hot' => 1]);
$log = DB::getQueryLog();
var_dump($log);exit;
打印结果:
array(1){
[
0
]=>array(3){
[
"query"
]=>string(75)"update `news_post` set `is_hot` = ? where `cate_id` = ? and `school_id` = ?"[
"bindings"
]=>array(3){
[
0
]=>int(1)[
1
]=>int(2)[
2
]=>string(5)"ID001"
}[
"time"
]=>float(310.23)
}
}
删除:
DB::enableQueryLog();
PostModel::where(['title' => '春天', 'is_hot' => 0])->delete();
$log = DB::getQueryLog();
var_dump($log);exit;
打印结果:
array(1){
[
0
]=>array(3){
[
"query"
]=>string(80)"delete from `news_post` where (`title` = ? and `is_hot` = ?) and `school_id` = ?"[
"bindings"
]=>array(3){
[
0
]=>string(6)"春天"[
1
]=>int(0)[
2
]=>string(5)"ID001"
}[
"time"
]=>float(217.09)
}
}
增:
DB::enableQueryLog();
$model = new PostModel();
$model->setAttribute('title', '冬天');
$model->setAttribute('content', '冬天,寒风彻骨!');
$model->setAttribute('uid', 1);
$model->setAttribute('cate_id', 2);
$model->setAttribute('createtime', time());
/* $model->title='冬天';
$model->content='冬天,寒风彻骨!';
$model->uid=1;
$model->cate_id=2;
$model->createtime=time();*/
$model->save();
$log = DB::getQueryLog();
var_dump($log);exit;
打印结果:
array(1){
[
0
]=>array(3){
[
"query"
]=>string(115)"insert into `news_post` (`school_id`, `title`, `content`, `uid`, `cate_id`, `createtime`) values (?, ?, ?,
?, ?, ?)"[
"bindings"
]=>array(6){
[
0
]=>string(5)"ID001"[
1
]=>string(6)"冬天"[
2
]=>string(22)"冬天,寒风彻骨!"[
3
]=>int(1)[
4
]=>int(2)[
5
]=>int(1721413065)
}[
"time"
]=>float(48.68)
}
}
插入时也自动加入了school_id的数据
插入有多重形式,试试其他的:
create方法:
DB::enableQueryLog();
$data = ['title' => '秋天','content' => '秋天,是收获的季节,也是思念的季节,它以一种宁静而深沉的美,让人沉醉!','uid' => 2,'cate_id' => 2,'createtime' => time(),
];
PostModel::create($data);
$log = DB::getQueryLog();
var_dump($log);exit;
打印结果:
array(1){
[
0
]=>array(3){
[
"query"
]=>string(115)"insert into `news_post` (`title`, `content`, `uid`, `cate_id`, `createtime`, `school_id`) values (?, ?, ?,
?, ?, ?)"[
"bindings"
]=>array(6){
[
0
]=>string(6)"秋天"[
1
]=>string(105)"秋天,是收获的季节,也是思念的季节,它以一种宁静而深沉的美,让人沉醉!"[
2
]=>int(2)[
3
]=>int(2)[
4
]=>int(1721413472)[
5
]=>string(5)"ID001"
}[
"time"
]=>float(142.4)
}
}
由上可知 create方法同样适用。
insert方法:
DB::enableQueryLog();
$data = ['title' => '秋天','content' => '秋风送爽,落叶轻舞,绘就一幅金色的画卷。','uid' => 2,'cate_id' => 2,'createtime' => time(),
];
PostModel::insert($data);
$log = DB::getQueryLog();
var_dump($log);exit;
打印结果:
array(1){
[
0
]=>array(3){
[
"query"
]=>string(99)"insert into `news_post` (`title`, `content`, `uid`, `cate_id`, `createtime`) values (?, ?, ?, ?, ?)"[
"bindings"
]=>array(5){
[
0
]=>string(6)"秋天"[
1
]=>string(60)"秋风送爽,落叶轻舞,绘就一幅金色的画卷。"[
2
]=>int(2)[
3
]=>int(2)[
4
]=>int(1721413774)
}[
"time"
]=>float(145.6)
}
}
由上可见insert方法似乎不适用。其实也可以从源码中看出端倪,Illuminate\Database\Eloquent\Builder中有一个属性:
表明了这些方法都是从query builder返回结果的。且在Eloquent\Builder中确实没有找到insert方法的定义,这也就导致基于Eloquent\Builder的一些操作没有生效。
同时使用Illuminate\Support\Facades\DB的操作也是基于query builder的亦无法使用作用域或model属性添加额外条件。
在Illuminate\Database\Query\Builder中找到了一个属性如下介绍:
感觉似乎可以在query执行前进行干预,查询相关资料,在provider中尝试注册了下但没有生效,若有成功的大佬希望能交流一下。
接下来尝试下连表查询:
$log = PostModel::from('post as p')->leftjoin('user as u', 'p.uid', '=', 'u.id')->where(['p.cate_id' => '2'])->toSql();
打印结果:
"select * from `news_post` as `news_p` left join `news_user` as `news_u` on `news_p`.`uid` = `news_u`.`id` where (`news_p`.`cate_id` = ?) and `news_p`.`school_id` = ?"
子表查询:
DB::enableQueryLog();
$subQuery = PostModel::where('is_hot', 1)->select(['uid', 'title','cate_id']);
$post = PostModel::fromSub($subQuery, 'p')->where('cate_id', 2)->select(['title'])->get();
$log = DB::getQueryLog();
var_dump($log);exit;
打印结果:
array(1){
[
0
]=>array(3){
[
"query"
]=>string(142)"select `title` from (select `uid`, `title`, `cate_id` from `news_post` where `is_hot` = ? and `school_id` =
?) as `news_p` where `cate_id` = ?"[
"bindings"
]=>array(3){
[
0
]=>int(1)[
1
]=>string(5)"ID001"[
2
]=>int(2)
}[
"time"
]=>float(23.76)
}
}
子表条件查询:
DB::enableQueryLog();
$subQuery = PostModel::where('is_hot', 1)->select([DB::raw('DISTINCT(uid)')]);
UserModel::whereIn('uid', $subQuery)->get(['username']);
$log = DB::getQueryLog();
var_dump($log);exit;
打印结果:
array(1){
[
0
]=>array(3){
[
"query"
]=>string(148)"select `username` from `news_user` where `uid` in (select DISTINCT(uid) from `news_post` where `is_hot` = ?
and `school_id` = ?) and `school_id` = ?"[
"bindings"
]=>array(3){
[
0
]=>int(1)[
1
]=>string(5)"ID001"[
2
]=>string(5)"ID001"
}[
"time"
]=>float(31.77)
}
}
测试的差不多了,综上除了insert方法,基于Model的增删改查操作,全局作用域基本都能生效,
且insert方法可以被save或create方法代替。
而基于Facades\DB的数据库操作如何全局添加条件还未找到可实现的方案,目前想到的是把Facades\DB进行封装,调用封装好的方法或对象,另外可以从Query\Builder的$beforeQueryCallbacks找突破口。希望有好的解决方案的大佬能分享出来,感谢!