Laravel嵌套子查询

$hq_code = $params['hq_code'];
        $orgz_ids = $params['orgz_ids'];
        $product_ids = $params['product_ids'];
        $start_date = $params['start_date'];
        $end_date = $params['end_date'] ?? date('Y-m-d');//区间最多保持1年

        $sub = DB::table('stock_change_record')->selectRaw('max(created_at) as created_at')
            ->where('hq_code',$hq_code)
            ->whereIn('orgz_id',$orgz_ids)
            ->whereIn('product_id',$product_ids)
            ->whereBetween('created_at',[$start_date,$end_date])
            ->groupBy(DB::raw("date_format(created_at,'%Y-%m-%d')"));

        $list = DB::table('stock_change_record')
            ->select('orgz_id','product_id','stock_change_record.created_at','changed_inventory',
                DB::raw('date_format(stock_change_record.created_at,\'%Y-%m-%d\') as created_at_new'),
                DB::raw('CONCAT_WS(\'_\',orgz_id,product_id,date_format(stock_change_record.created_at,\'%Y-%m-%d\')) as op'))
            ->leftJoin(DB::raw("({$sub->toSql()}) as b"),'stock_change_record.created_at','=','b.created_at')
            ->addBinding($sub->getBindings())
            ->where('hq_code',$hq_code)
            ->whereIn('orgz_id',$orgz_ids)
            ->whereIn('product_id',$product_ids)
            ->whereBetween('stock_change_record.created_at',[$start_date,$end_date])
            ->orderBy('stock_change_record.id', 'desc')
            ->get();
        if(!empty($list)){
            $list = $list->toArray();
        }

ubuntu xlswrite安装

  • 下载源码 https://pecl.php.net/package/xlswriter
  • 在下载的目录解压tgz包
  • 切换到解压的目录下
  • 找到PHP安装目录,找到bin西面的phpize,比如我的目录是/usr/local/php/bin/phpize, 在3的目录下 执行该命令
  • 执行 ./configure –with-php-config=/usr/local/php/bin/php-config
  • 编译及安装模块 执行命令make && make install 生成.so动态库
  • 执行6以后会显示生成动态库的路径
  • 找到php.ini的文件 添加动态库路径 extension=/path/to/xlswrite.so
  • 重启php-fpm服务
  • 这里需要注意有两点一个拾php.ini的位置 我的位置/etc/php/7.1/cli/php.ini,不要找/etc/php/7.1/fpm/php.ini; 另外一个就是要重启php-fpm的服务