利用leftJoinsub创建子查询让查询语句groupBy和orderBy一起使用

//首先统计出该施工项目下的人员工种和每个工种的总人数
        $workTypes = Worker::from('workers AS w')
            ->leftJoin('projects AS p','p.projectCode', '=', 'w.pCode')
            ->leftJoin('worker_types AS wt', 'w.workType', '=', 'wt.id')
            ->where('w.pCode', $project)
            ->groupBy('w.workType')
            ->select('wt.id', DB::raw('IFNULL(wt.name, "其他") AS workType'),                 DB::raw('COUNT(w.id) AS total'));

//根据工种的id右关联工种表然后根据上面查出的字段进行排序
        $list = WorkerType::from('worker_types AS wt')
            ->rightJoinSub($workTypes, 'wts', 'wts.id', '=', 'wt.id')
            ->orderBy('wts.total', 'DESC')
            ->get(['wt.id', DB::raw('IFNULL(wt.name, "其他") AS workType'),                     'wts.total']);Copy