}
}
$arrayRes = [];
foreach ($result as $key => $value) {
if (!array_key_exists("faculties", $value)) {
$value['faculties'] = [];
}
$arrayRes[] = $value;
}
$query = $arrayRes;
return $query;
});
Route::group(['prefix' => 'm1'], function () {
Route::group(['middleware' => ['cors']], function () {
Route::get('map-data/{id}', function ($provinceId) {
$query = \App\Models\Thailand\Amphur::query();
$query->select(['amphur.amphur_id', 'amphur.amphur_name', DB::raw(" count(projects.id) as numProAmp "), "sub.faculty_id", "sub.faculty_name", "sub.numProFacAmp"]);
$query->leftJoin('projects', function ($join) {
$join->on('projects.amphur_id', '=', 'amphur.amphur_id');
$join->on('projects.status_id', '=', DB::raw("4"));
});
$query->leftJoin('faculties', 'projects.faculty_id', '=', 'faculties.id');
$query->leftJoin(DB::raw("\n (\n select\n count(projects.id) as numProFacAmp\n , projects.amphur_id\n , projects.faculty_id\n ,faculties.name_th as faculty_name\n from projects\n left join faculties on projects.faculty_id = faculties.id\n where projects.province_id = {$provinceId}\n group by projects.faculty_id , projects.amphur_id\n ) as sub\n\n "), function ($join) {
$join->on('sub.amphur_id', '=', 'amphur.amphur_id');
});
$query->where('amphur.province_id', '=', "{$provinceId}");
$query->groupBy('amphur.amphur_id');
$query->groupBy('sub.faculty_id');
$query->orderBy('numProAmp', 'desc');
$query = $query->get()->toArray();
$result = [];
