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;