php 多表查询

$res= db('admin_login_record')
            ->alias('login')
            ->join('__ADMIN_USER__ user','user.id=login.create_user_id','LEFT')
            ->where(function ($query) use ($param) {
                if (!empty($param['startTime'])) $query->where('login.create_time', '>=', strtotime($param['startTime']));
                if (!empty($param['endTime'])) $query->where('login.create_time', '<=', strtotime($param['endTime']));
                if (!empty($param['userIds'])) $query->whereIn('login.create_user_id', $param['userIds']);
            })
            ->field('login.*,user.realname as username')
            ->page($param['page'],$param['limit'])
            ->order('login.id', 'desc')
            ->select();
			
			
$sql = CustomerModel::alias('a')
            ->field([
                "FROM_UNIXTIME(`a`.`create_time`, '{$time['time_format']}')" => 'type',
                'COUNT(*)' => 'customer_num',
                'SUM(
                    CASE WHEN ISNULL(`b`.`order_date`) THEN 0 ELSE (
                        UNIX_TIMESTAMP(`b`.`order_date`) - `a`.`create_time`
                    ) / 86400 END
                )' => 'cycle_sum'
            ])
            ->join(
                "(
                    SELECT 
                        `customer_id`, MIN(`order_date`) AS `order_date` 
                    FROM
                        `{$prefix}crm_contract` 
                    WHERE
                        `check_status` = 2 
                    GROUP BY
                        `customer_id`
                ) b",
                '`a`.`customer_id` = `b`.`customer_id`',
                'LEFT'
            )
            ->where([
                'a.deal_status' => ['IN', ['已到诊', '已到诊（流失）', '已到诊（流回）']],
                'a.create_time' => ['BETWEEN', $time['between']],
                'a.owner_user_id' => ['IN', !empty($userIds) ? $userIds : '9999999999']
            ])
            ->group('type')
            ->fetchsql()
            ->select();
        $res = queryCache($sql);
        $res = array_column($res, null, 'type');
					
					
					
$where = $this->getWhere($request);
		$join  = [
			['__CRM_CONTRACT__ contract', 'contract.contract_id = a.contract_id', 'LEFT'],
			['__ADMIN_USER__ user', 'user.id = contract.owner_user_id', 'LEFT'],
			['__CRM_PRODUCT__ product', 'product.product_id = a.product_id', 'LEFT'],
			['__CRM_PRODUCT_CATEGORY__ product_category', 'product_category.category_id = product.category_id', 'LEFT'],
		];

		$sql = db('crm_contract_product')
				->alias('a')
				->where($where)
				->join($join)
				->group('a.product_id')
				->field('a.product_id,sum(a.num) as num,product.name as product_name,product_category.name as category_id_info,product_category.category_id')
				->fetchSql()
				->select();
		$list = queryCache($sql);

		$data = [];
		foreach ($list AS $key => $value) {
		    if (empty($data[$value['category_id']])) {
                $data[$value['category_id']] = $value;
            } else {
                $data[$value['category_id']]['num'] += $value['num'];
            }
        }					```