laravel中union all 和whereHas 同时使用关联表联合查询，出现了奇怪的BUG（已解决）
2021 年 3 月 16 日 10:05:00已经解决了，但不是从解决bug的角度解决的，而是找到了绕过bug的方式
我目前想实现的需求是union all 几张people_face_日期的分表，查询2021-02-13 00:00:00 ~ 2021-02-15 23:00:00这段时间的人脸识别记录，然后关联查询people表某身份证号的人的人脸识别记录
people_face表和people表虽然是分库的，但这个不影响后面
people_face中的people_id字段对应people表的id字段
class PeopleFace extends Model
{
...
public function people()
{
return $this->belongsTo(People::class);
}
...
}
获得错误结果的方案A（错得比较离谱，原因不明）
我第一个方案，代码如下
//开始日期
$start = Carbon::parse('2021-02-13 00:00:00');
//结束日期
$end = Carbon::parse('2021-02-15 23:00:00');
//查询集合
$queries = collect();
for($i=$start->copy();$i->format('Y-m-d') <= $end->format('Y-m-d');$i->addDay())
{
if (Schema::connection('mysql'.$community_identifier)->hasTable("people_face_{$i->format('Ymd')}"))
{
$queries->push(
\DB::connection('mysql'.$community_identifier)->table("people_face_{$i->format('Ymd')}")
->whereBetween('people_face_attendance_time',[$start,$end])
);
}
}
//出列一张表作为unnion的开始
$unionQuery = $queries->shift();
//循环剩下的表添加unnion
$queries->each(function($item,$key) use ($unionQuery) {
$unionQuery->unionAll($item);
});
if($unionQuery){
$people_face = new PeopleFace;
$people_face->setConnection('mysql'.$community_identifier);
//设置临时表名称，添加临时表
$lists = with($people_face)->setTable('union_people_face')
//添加临时表
->from(\DB::raw("({$unionQuery->toSql()}) as union_people_face"))
//合并查询条件
->mergeBindings($unionQuery);
//关联约束
$lists = $lists->whereHas('people',function($q){
//在这里关联查询身份证号
$q->where('people_idcard','123');
});
$data = $lists->toRawSql();
dd(data);//打印结果toRawSql()详见本网站的WIKILaravel 数据库：输出 SQL 语句
select * from ((select * from `people_face_20210213` where `people_face_attendance_time` between '2021-02-13 00:00:00' and '2021-02-15 23:00:00') union all (select * from `people_face_20210214` where `people_face_attendance_time` between 123 and '2021-02-13 00:00:00') union all (select * from `people_face_20210215` where `people_face_attendance_time` between '2021-02-15 23:00:00' and '2021-02-13 00:00:00')) as union_people_face where exists (select * from `cloud-home`.`people` where `union_people_face`.`people_id` = `cloud-home`.`people`.`id` and `people_idcard` = '2021-02-15 23:00:00')可以看到，身份证号码竟然和某一个分表查询的时间条件，调了个位置。
错误结果的方案B（比A稍微强点）
我在这里一筹莫展，开始怀疑是不是之前这里
//设置临时表名称，添加临时表
$lists = with($people_face)->setTable('union_people_face')
//添加临时表
->from(\DB::raw("({$unionQuery->toSql()}) as union_people_face"))
//合并查询条件
->mergeBindings($unionQuery);的$unionQuery->toSql()留了太多的?让后面的数据混淆，那么我把?填上是不是就好了呢？
于是我将这里面的代码改成$unionQuery->toRawSql()
//设置临时表名称，添加临时表
$lists = with($people_face)->setTable('union_people_face')
//添加临时表
//改成了toRawSql()
->from(\DB::raw("({$unionQuery->toRawSql()}) as union_people_face"))
//合并查询条件
->mergeBindings($unionQuery);结果获得
select * from ((select * from `people_face_20210213` where `people_face_attendance_time` between '2021-02-13 00:00:00' and '2021-02-15 23:00:00') union all (select * from `people_face_20210214` where `people_face_attendance_time` between '2021-02-13 00:00:00' and '2021-02-15 23:00:00') union all (select * from `people_face_20210215` where `people_face_attendance_time` between '2021-02-13 00:00:00' and '2021-02-15 23:00:00')) as union_people_face where exists (select * from `cloud-home`.`people` where `union_people_face`.`people_id` = `cloud-home`.`people`.`id` and `people_idcard` = '2021-02-13 00:00:00')确实，前面的问题提前解决了，但是最后的people_idcard ='2021-02-13 00:00:00'依然是错误的状态。
报错的方案C
最后的方案是放弃联合查询，先查people表里people_idcard=123的数据，得到id,再where语句组到people_face表的查询中去。
//开始日期
$start = Carbon::parse('2021-02-13 00:00:00');
//结束日期
$end = Carbon::parse('2021-02-15 23:00:00');
//查询集合
$queries = collect();
for($i=$start->copy();$i->format('Y-m-d') <= $end->format('Y-m-d');$i->addDay())
{
if (Schema::connection('mysql'.$community_identifier)->hasTable("people_face_{$i->format('Ymd')}"))
{
$people_face_query = \DB::connection('mysql'.$community_identifier)->table("people_face_{$i->format('Ymd')}")
->select('people_id','people_face_attendance_time','people_face_url','people_face_temperature_number')
->whereBetween('people_face_attendance_time',[$start,$end]);
if($idcard){
$people_face_query->where('people_id',$people_id);
}
$queries->push(
$people_face_query
);
}
}
//出列一张表作为unnion的开始
$unionQuery = $queries->shift();
//循环剩下的表添加unnion
$queries->each(function($item,$key) use ($unionQuery) {
$unionQuery->unionAll($item);
});
if($unionQuery){
$people_face = new PeopleFace;
$people_face->setConnection('mysql'.$community_identifier);
//设置临时表名称，添加临时表
$lists = with($people_face)->setTable('union_people_face')
//添加临时表
->from(\DB::raw("({$unionQuery->toSql()}) as union_people_face"))
//合并查询条件
->mergeBindings($unionQuery);
$data = $lists->paginate();
dd(data);//打印结果但报错
QueryException In Connection.php line 678 :
SQLSTATE[HY093]: Invalid parameter number (SQL: select count(*) as aggregate from ((select `people_id`, `people_face_attendance_time`, `people_face_url`, `people_face_temperature_number` from `people_face_20210213` where `people_face_attendance_time` between '2021-02-13 00:00:00' and '2021-02-15 23:00:00' and `people_id` = 1) union all (select `people_id`, `people_face_attendance_time`, `people_face_url`, `people_face_temperature_number` from `people_face_20210214` where `people_face_attendance_time` between '2021-02-13 00:00:00' and '2021-02-15 23:00:00' and `people_id` = 1) union all (select `people_id`, `people_face_attendance_time`, `people_face_url`, `people_face_temperature_number` from `people_face_20210215` where `people_face_attendance_time` between '2021-02-13 00:00:00' and '2021-02-15 23:00:00' and `people_id` = 1)) as union_people_face where `people_id` = 2021-02-13 00:00:00)目前还在研究该怎么做。
恳求各位老师指导！
2021年3月16日08:13:01更新
昨晚又有进展，分析应该是PDO绑定数值的问题，参考了PHP——了解 Laravel 预处理的数据绑定,却依然没有搞定。
看来得花点时间分析一下laravel的底层源码了，搞明白是怎么封装PDO的。
2021年3月16日10:05:00
现在终于写出我想要的代码了，研究了一番laravel底层的PDO绑定逻辑，没研究出个鸟来，但是最后我将分表里面的查询拎了出来，在union后的总表里进行查询，达到了相同的效果，还不会让PDO绑定值位置混淆。
这是最后的代码：
$datetime = request()->get('datetime');
$idcard = request()->get('idcard');
$lists = null;
if(!$datetime) return $lists;
// dd($datetime);
//开始日期
$start = Carbon::parse($datetime['start']);
//结束日期
$end = Carbon::parse($datetime['end']);
// dd($end);
//查询集合
$queries = collect();
for($i=$start->copy();$i->format('Y-m-d') <= $end->format('Y-m-d');$i->addDay())
{
if (Schema::connection('mysql'.$community_identifier)->hasTable("people_face_{$i->format('Ymd')}"))
{
$people_face_query = \DB::connection('mysql'.$community_identifier)->table("people_face_{$i->format('Ymd')}")
->select('people_id','people_face_attendance_time','people_face_url','people_face_temperature_number');
$queries->push(
$people_face_query
);
}
}
//出列一张表作为unnion的开始
$unionQuery = $queries->shift();
//循环剩下的表添加unnion
$queries->each(function($item,$key) use ($unionQuery) {
$unionQuery->unionAll($item);
});
if($unionQuery){
$people_face = new PeopleFace;
$people_face->setConnection('mysql'.$community_identifier);
//设置临时表名称，添加临时表
$lists = with($people_face)->setTable('union_people_face')
//添加临时表
->from(\DB::raw("({$unionQuery->toSql()}) as union_people_face"));
//合并查询条件
$lists->mergeBindings($unionQuery);
$lists->whereBetween('people_face_attendance_time',[$start,$end]);
//关联约束,不在people表的不用查
if($idcard){
$lists ->whereHasIn('people',function($q) use ($idcard){
$q->where('people_idcard',$idcard);
});
}
$lists = $lists->whereHasIn('people.peopleHouse');
$lists = $lists
->with(['people','people.peopleHouse','people.peopleHouse.house'])
->orderBy('people_face_attendance_time','desc')
->paginate();
}else{
$lists = '';
}
return $lists;
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：