Where cannot between when thinkphp5.0 has multiple join

the situation is similar to this link . The brother of this post has no answer. Now he has the same problem. Paste the code

below.
$map = ["products.insurance_status"=>1];//
    if( input("get.times") && input("get.times") < 7 ){
        $time = return_times(input("get.times"));
        //bug
        $map["order.pay_time"] = ["between time", [strtotime($time["start"]),strtotime($time["end"])]];
    }
    $list = db("xmb_insurance_products")
            ->alias("products")
            ->field("products.*,company.company_name,count(order.id) num,sum(order.order_amount) price")
            ->join("xmb_insurance_company company", "products.insurance_company_id=company.id", "left")
            ->join("xmb_insurance_order order", "order.insurance_id=products.id and order.order_status=1","left")//,and status=1,
            ->group("products.id")
            ->order("products.id desc")
            ->where($map)
            ->paginate(7,false,[ "type"=> "page\Page","var_page"=>"page","query" => $this->request->get()]);
            

result:

clipboard.png

ask for help: how should the following statements be handled

$map["order.pay_time"] = ["between time", [strtotime($time["start"]),strtotime($time["end"])]];

problem description

the environmental background of the problems and what methods you have tried

related codes

/ / Please paste the code text below (do not replace the code with pictures)

what result do you expect? What is the error message actually seen?

Php
Nov.06,2021

I hope it can be of some help to you.
the following code is OK. But if I use union, I will make the same mistake as you.

        $list = db('neworder')->alias('a')
            ->join('__MEMBERS__ m','a.mid = m.id','left')
            ->join('__MEMBERS__ s','s.id = m.id','left')
            ->whereTime('m.create_time', 'between', ['1970-10-1', '2018-10-1'])
            ->select();
        return json_encode($list);
        
  buildsqlmysqlok

has been solved, thank you boss, two solutions: 1: no alias, 2 between

 $map = ['products.insurance_status'=>1];//
    if( input('get.times') && input('get.times') < 7 ){
        $time = return_times(input('get.times'));
        $map['order.add_time'] = ['between', [strtotime($time['start']),strtotime($time['end'])]];
    }

    $list = db('xmb_insurance_products')
            ->alias('products')
            ->field('products.*,company.company_name,count(order.id) num,sum(order.order_amount) price')
            ->join('xmb_insurance_company company', 'products.insurance_company_id=company.id', 'left')
            ->join('xmb_insurance_order order', 'order.insurance_id=products.id and order.order_status=1','left')//,and status=1,
            ->group('products.id')
            ->order('products.id desc')
            ->where($map)
            ->paginate(7,false,[ 'type'=> 'page\Page','var_page'=>'page','query' => $this->request->get()]);
    $this->assign('list',$list);
    return $this->fetch();
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-7b7cc1-166e7.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-7b7cc1-166e7.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?