mysql 根据某一条件排序

//例如 mysql 根据聊天条数排序
SELECT
	`a`.`id` AS `family_id`,
	`a`.`name`,
	`a`.`avatar`,
	`a`.`intro`,
	count(c.id) 
FROM
	`ln_family` AS `a`
	LEFT JOIN `ln_family_chat` AS `c` ON `a`.`id` = `c`.`family_id` and c.is_violation = 1
WHERE
	`status` = 1 
GROUP BY
	`a`.`id`

//is_violation 1 未违规 因为直接在where中加ln_family_chat的条件则会查不到数据 所以在表连接时加入条件


//laravel 写法
//获取最近一周的时间
$endTime = time();
$startTime = strtotime('-7 days',$endTime);
$this->famliyListStartTime = date('Y-m-d H:i:s',$startTime);
$this->famliyListEndTime = date('Y-m-d H:i:s',$endTime);

$where[] = ['a.status','=',1];
$list = DB::table('ln_family as a')
    ->leftJoin('ln_user_data as b', 'a.user_id', '=', 'b.user_id')
    ->leftJoin('ln_family_chat as c', function ($join) {
        $join->on('a.id', '=', 'c.family_id')
            ->where('c.is_violation', '=', 1)
            ->whereBetween('c.send_time', [ $this->famliyListStartTime, $this->famliyListEndTime]);
    })
    ->where($where)
    ->select([
        'a.id as family_id',
        'a.name as family_name',
        'a.avatar as family_avatar',
        'a.intro as family_intro',
        'a.user_id',
        'a.add_time',
        'b.nickname',
        'b.head_picture',
        DB::raw('count(c.id) heat')
    ])
    ->groupby('a.id')
    ->orderBy('heat','desc')
    ->orderBy('a.add_time','desc')
    ->paginate($pageSize, ['*'], '', $page);