I am making a teacher interface, and i have four tables Professors, Students, All Assignments and Student Assignments (all assignments for individual students)
I want to send the professor all the students and all their assignments, but some have multiple assignments and some have none. I query all the student info from the first table and now I want every single row (student) to have a property assignments which is and array of all the assignments from the second table.
Students
Name Class Grade
s1 1 A
s2 2 B
Assignments
Name Student Status
a1 s1 done
a2 s1 done
And I want to get:
[
{Name:s1, Class: 1, Grade: A, Assignments:[{Name:a1, Status: done}, {Name:a2, Status: done}]},
{Name:s2, Class: 2, Grade: B, Assignments:[]}
]
I am using nodejs (mysql module) for querying the database.
UPDATE (from the comment)
version 10.4.11-MariaDB – pro_programmer
You could use JSON aggregation:
If MariaDB 10.4, where
json_arrayagg()
is not avaible (butjson_object()
is), one workaround usesgroup_concat()
: