从不同的表中查询并将其存储在json对象中

Question

我想获得每个学校的学生总数和性别，每个学校对应一个特定的ward_id，这将由用户选择，还想获得每个ward_id通过api请求传递的学校总数

api url是http://127.0.0.1:8000/api/getSchools/{ward_id}代码在

下面

public function getSchoolsinWard($id){
//this will be the id of ward
$array_count = [];
$array_school = [];
$schools = School::where('ward_id', $id)->get();

foreach($schools as $school){
$array_school[] = $school;
$data = count($school->grades);
foreach($school->grades as $grade){
$total = count($grade->streams);
foreach($grade->streams as $stream){
$total_students = count($stream->students);
foreach($stream->students as $student){
$array_count[] = $student;
$boys = Student::where('id', $student->id)->where('gender', 'male')->get();
$total_boys = $boys->count();
$girls = Student::where('id', $student->id)->where('gender', 'female')->get();
$total_girls = $girls->count();
}
}
}
}

$schoolsTotal = School::where('ward_id',$id)->count();
return response(['message' => 'schools in wards', 
'schools'=>$array_school,
'total students'=> $array_count, 
'total boys'=>$total_boys,
'total girls' => $total_girls,
'totals schools' => $schoolsTotal]);

}

上面的代码给出了这个邮差响应

{
"message":"schools in wards",
"schools":[
{
"id":1,
"name":"Mivinjeni",
"educationLevel":"Secondary",
"ward_id":1,
"created_at":"2022-06-04T14:41:34.000000Z",
"updated_at":"2022-06-04T14:41:34.000000Z",
"grades":[
{
"id":1,
"name":"Form 5",
"school_id":1,
"created_at":"2022-06-04T14:47:34.000000Z",
"updated_at":"2022-06-04T14:47:34.000000Z",
"streams":[
{
"id":1,
"name":"A",
"grade_id":1,
"created_at":"2022-06-04T14:54:38.000000Z",
"updated_at":"2022-06-04T14:54:38.000000Z",
"students":[
{
"id":1,
"student_name":"Matumbu Mawe",
"gender":"male",
"stream_id":1,
"created_at":"2022-06-04T14:58:03.000000Z",
"updated_at":"2022-06-04T14:58:03.000000Z"
},
{
"id":2,
"student_name":"Lukaku Maguire",
"gender":"male",
"stream_id":1,
"created_at":"2022-06-04T14:58:46.000000Z",
"updated_at":"2022-06-04T14:58:46.000000Z"
}
]
},
{
"id":2,
"name":"B",
"grade_id":1,
"created_at":"2022-06-04T14:54:46.000000Z",
"updated_at":"2022-06-04T14:54:46.000000Z",
"students":[

]
}
]
},
{
"id":2,
"name":"Form 6",
"school_id":1,
"created_at":"2022-06-04T14:47:59.000000Z",
"updated_at":"2022-06-04T14:47:59.000000Z",
"streams":[
{
"id":3,
"name":"A",
"grade_id":2,
"created_at":"2022-06-04T14:54:52.000000Z",
"updated_at":"2022-06-04T14:54:52.000000Z",
"students":[

]
},
{
"id":4,
"name":"B",
"grade_id":2,
"created_at":"2022-06-04T14:54:56.000000Z",
"updated_at":"2022-06-04T14:54:56.000000Z",
"students":[

]
}
]
},
{
"id":3,
"name":"Form 1",
"school_id":1,
"created_at":"2022-06-04T14:49:41.000000Z",
"updated_at":"2022-06-04T14:49:41.000000Z",
"streams":[
{
"id":5,
"name":"A",
"grade_id":3,
"created_at":"2022-06-04T14:55:05.000000Z",
"updated_at":"2022-06-04T14:55:05.000000Z",
"students":[

]
},
{
"id":6,
"name":"B",
"grade_id":3,
"created_at":"2022-06-04T14:55:09.000000Z",
"updated_at":"2022-06-04T14:55:09.000000Z",
"students":[

]
}
]
},
{
"id":4,
"name":"Form 2",
"school_id":1,
"created_at":"2022-06-04T14:49:49.000000Z",
"updated_at":"2022-06-04T14:49:49.000000Z",
"streams":[

]
},
{
"id":5,
"name":"Form 3",
"school_id":1,
"created_at":"2022-06-04T14:49:53.000000Z",
"updated_at":"2022-06-04T14:49:53.000000Z",
"streams":[

]
},
{
"id":6,
"name":"Form 4",
"school_id":1,
"created_at":"2022-06-04T14:50:01.000000Z",
"updated_at":"2022-06-04T14:50:01.000000Z",
"streams":[

]
}
]
},
{
"id":2,
"name":"Miburani",
"educationLevel":"Secondary",
"ward_id":1,
"created_at":"2022-06-04T14:41:50.000000Z",
"updated_at":"2022-06-04T14:41:50.000000Z",
"grades":[
{
"id":7,
"name":"Form 4",
"school_id":2,
"created_at":"2022-06-04T14:50:10.000000Z",
"updated_at":"2022-06-04T14:50:10.000000Z",
"streams":[

]
},
{
"id":8,
"name":"Form 1",
"school_id":2,
"created_at":"2022-06-04T14:50:14.000000Z",
"updated_at":"2022-06-04T14:50:14.000000Z",
"streams":[

]
},
{
"id":9,
"name":"Form 2",
"school_id":2,
"created_at":"2022-06-04T14:50:17.000000Z",
"updated_at":"2022-06-04T14:50:17.000000Z",
"streams":[

]
},
{
"id":10,
"name":"Form 3",
"school_id":2,
"created_at":"2022-06-04T14:50:19.000000Z",
"updated_at":"2022-06-04T14:50:19.000000Z",
"streams":[

]
}
]
}
],
"total students":[
{
"id":1,
"student_name":"Matumbu Mawe",
"gender":"male",
"stream_id":1,
"created_at":"2022-06-04T14:58:03.000000Z",
"updated_at":"2022-06-04T14:58:03.000000Z"
},
{
"id":2,
"student_name":"Lukaku Maguire",
"gender":"male",
"stream_id":1,
"created_at":"2022-06-04T14:58:46.000000Z",
"updated_at":"2022-06-04T14:58:46.000000Z"
},
{
"id":3,
"student_name":"Mary Mzuri",
"gender":"female",
"stream_id":1,
"created_at":"2022-06-04T15:09:15.000000Z",
"updated_at":"2022-06-04T15:09:15.000000Z"
},
{
"id":4,
"student_name":"Anna Crush",
"gender":"female",
"stream_id":1,
"created_at":"2022-06-04T15:09:24.000000Z",
"updated_at":"2022-06-04T15:09:24.000000Z"
}
],
"total boys":0,
"total girls":1,
"totals schools":2
}

但是我想要作为邮递员的请求是下面的

{
"message":"schools in wards",
"schools":[
{
"id":1,
"name":"Mivinjeni",
"educationLevel":"Secondary",
"ward_id":1,
"total_students":21,
"total_boys":11,
"total_girls":10,
"created_at":"2022-06-04T14:41:34.000000Z",
"updated_at":"2022-06-04T14:41:34.000000Z"
},
{
"id":1,
"name":"Mivinjeni",
"educationLevel":"Secondary",
"ward_id":1,
"total_students":21,
"total_boys":11,
"total_girls":10,
"created_at":"2022-06-04T14:41:34.000000Z",
"updated_at":"2022-06-04T14:41:34.000000Z"
}
],
"total_students_in_wards":42,
"total_schools":2
}

模型之间的关系是Ward hasMany School hasMany Grades hasMany Stream hasMany Students

Answer 1

您可以使用子查询选择来获取total_students的计数,total_boys,total_girls。然后对于total_students_in_ward和total_schools你可以使用Collection的sum数和方法——不需要单独查询计数。

public function getSchoolsinWard($id)
{
$schools = School::query()
->where('ward_id',$id)
->addSelect([
'total_students' => Student::selectRaw('count(*)')
->whereIn(
'stream_id', 
Stream::select('id')->whereIn(
'grade_id',
Grade::select('id')->whereColumn('school_id', 'schools.id')
)
),

'total_boys' => Student::selectRaw('count(*)')
->whereRaw('gender = "male"')
->whereIn(
'stream_id', 
Stream::select('id')->whereIn(
'grade_id',
Grade::select('id')->whereColumn('school_id', 'schools.id')
)
),

'total_girls' => Student::selectRaw('count(*)')
->whereRaw('gender = "female"')
->whereIn(
'stream_id', 
Stream::select('id')->whereIn(
'grade_id',
Grade::select('id')->whereColumn('school_id', 'schools.id')
)
)      
])->get();
return response()->json([
'message' => 'schools in wards',
'schools' => $schools,
'total_students_in_ward' => $schools->sum('total_students'),
'total_schools' => $schools->count()
]);
}

Laravel Docs - Eloquent -子查询select

Laravel Docs - Collections - Method Sum

Laravel Docs - Collections - Method Count