tp5 join关联查询，聚合查询

记录：tp5 join关联查询，聚合查询
1，关联并限制条件

$list2 = $role_model->alias('a')
	    ->join('shequshop_school_admin b','a.user_id = b.id AND b.status =1','left')
	    ->where($dis)
	    ->where('a.type','in',[2,3])
	    // ->where(['b.status'=>1])
	    ->field('a.*,b.agent_name as user_name')
	    ->group('a.id')
	    ->select()
	    ->toArray();

2，原生SQL关联聚合查询

$sql = "SELECT a.id as coach_id,a.coach_name,a.work_img,a.user_id,ifnull(b.order_service_price,0) as order_service_price ,ifnull(b.service_timelong,0) as service_timelong,ifnull(b.add_balance,0) as add_balance,ifnull(c.coach_integral,0) as coach_integral,ifnull(d.coach_star,5) as coach_star
     FROM `ims_massage_service_coach_list` `a`
     LEFT JOIN (SELECT sum(true_service_price) as order_service_price,sum(true_time_long) as service_timelong,sum(true_service_price*is_add)/sum(true_service_price) as add_balance,id,coach_id FROM `ims_massage_service_order_list` where pay_type = 7 AND create_time between $start_time AND $end_time GROUP BY coach_id) AS b ON a.id=b.coach_id 
     LEFT JOIN (SELECT sum(integral) as coach_integral,coach_id FROM `ims_massage_integral_list` where type = 0 AND status=1 AND create_time between $start_time AND $end_time GROUP BY coach_id) AS c ON a.id=c.coach_id
     LEFT JOIN (SELECT ROUND(avg(star),1) as coach_star,coach_id FROM `ims_massage_service_order_comment` where status > -1 AND create_time between $start_time AND $end_time GROUP BY coach_id) AS d ON a.id=d.coach_id
     WHERE $dis_sql GROUP BY a.id ORDER BY $top LIMIT $start OFFSET $end";

$data = Db::query($sql);

3，join 关联聚合查询，并用聚合字段排序

$data = $this->alias('a')
	   ->join( 'card_user_info b', 'a.to_uid = b.fans_id')
	   ->join( 'card_user d', 'b.fans_id = d.id && d.is_staff = 1','left')
	   ->join( 'card_job c', 'c.id = b.job_id ','left')
	   ->field('a.to_uid,count(a.to_uid) as number,a.uniacid,b.name,avatar,c.name as job_name,nickName,avatarUrl')
	   ->where($where)
	   ->limit(3)
	   ->group('a.to_uid')
	   ->order('number desc ,a.to_uid asc')
	   ->whereDay('a.create_time','yesterday')
	   ->select()->toArray();

$data = $user_model
      ->alias('a')
      ->join('massage_coach_broker_list b','a.id = b.user_id','left')
      ->where($dis)
      ->where(function ($query) use ($where){
          $query->whereOr($where);
      })
      ->field('b.*, ifnull(b.sh_time,0) as sh_time,a.nickName,a.avatarUrl,a.new_cash,a.cash as new_cash')
      ->group('a.id')
      ->order('sh_time desc,a.id desc')
      ->paginate($page)
      ->toArray();

 $coach_data = $coach_model->alias('a')
      ->join('massage_service_order_list b','a.id = b.coach_id')
      ->where($dis)
      ->whereTime('b.create_time','-30 days')
      ->field('a.coach_name,a.admin_id,sum(b.true_service_price) as order_price')
      ->group('a.id')
      ->order('order_price desc,a.id desc')
      ->limit(10)
      ->select()
      ->toArray();

$havingSql = "";
if($min_price > 0 && $max_price > 0 && $min_price <= $max_price){
    $havingSql = "MIN(b.goods_price) >= $min_price && MIN(b.goods_price) <= $max_price";
}
$model_temp = new WxlitestoregoodsModel();
$result = $model_temp->alias('a')
    ->join('litestore_goods_spec b','a.goods_id = b.goods_id','left')
    ->field(['a.*,(a.sales_initial + a.sales_actual) as goods_sales,MIN(b.goods_price) as goods_min_price,MAX(b.goods_price) as goods_max_price,MIN(b.line_price) as goods_min_line_price,MAX(b.line_price) as goods_max_line_price'])
    ->with(['spec'])
    ->where($filter)
    ->having($havingSql)
    ->group('a.goods_id')
    ->order($sort)
    ->paginate(15, false, [ 'page' => $page ]);

4，条件1关联b表，条件2关联c表，并取关联表的值

//订单付款后的操作记录
$dis = [

    ['a.type' ,'=', 1],

    ['b.id'    ,'=',  $input['order_id']],
];

$dis1 =[
    ['c.order_id' ,'=', $input['order_id']],

    ['a.type'   ,'=',  2]
];
$list = $log_model->alias('a')
    ->join('massage_service_order_list b','a.order_id = b.id AND a.type=1','left')
    ->join('massage_service_refund_order c','a.order_id = c.id AND a.type=2','left')
    ->where(function ($query) use ($dis,$dis1){
        $query->whereOr([$dis,$dis1]);
    })
    ->field('a.admin_control,a.user_id,ifnull(b.order_code,c.order_code) as order_code,a.create_time,a.pay_type,a.type,ifnull(b.pay_price,c.refund_price) as price')
    ->group('a.id')
    ->order('a.id desc')
    ->select()
    ->toArray();