Join users with module table and get all modules that belong to users who have the group code.
select * from modules INNER JOIN users ON users.user_id = modules.user_id WHERE users.group_code = 'enteradminsgroupcodehere';
Using these module id's from above, we can fetch all the quizzes that belong to any of these modules
select * from quizzes inner join modules on quizzes.module_id = modules.module_id INNER JOIN users ON users.user_id = modules.user_id WHERE users.group_code = 'enteradminsgroupcodehere';
Using these quiz_id's we can fetch all scores for all of these quizzes for all of the users that have done the quiz.
select scores.user_id, scores.score, users.group_code, quizzes.quiz_id, quizzes.name, modules.module_id from scores inner join quizzes on quizzes.quiz_id = scores.quiz_id inner join modules on quizzes.module_id = modules.module_id INNER JOIN users ON users.user_id = modules.user_id WHERE users.group_code = 'enteradminsgroupcodehere' order by scores.user_id;
To get it in the format that he would like for the csv, use js to create the correct object format. Alternatively, you can use a complex query to return the object for each user although that may require some expert psql knowledge.
#644
For users and modules belonging to them, you can run the same query as in the first point above and use from javascript to sum the modules by individual users which can then be displayed to the group admin
select * from modules INNER JOIN users ON users.user_id = modules.user_id WHERE users.group_code = 'enteradminsgroupcodehere';
Total Number of students for each lecturer, you can use a query like the one below and then use js accumulate all the users by lecturer_id. this way you get a total number of enrolments for each lecturer
select module_members.user_id, modules.module_id, modules.user_id as lecturer_id from module_members inner join modules on module_members.module_id = modules.module_id INNER JOIN users ON users.user_id = modules.user_id WHERE users.group_code = 'enteradminsgroupcodehere';
To get all the quizzes run for the given group you can run the query below, this will only return quizzes that have been run.
select count(*) from quizzes inner join modules on quizzes.module_id = modules.module_id INNER JOIN users ON users.user_id = modules.user_id WHERE quizzes.is_presented=true AND users.group_code = 'enteradminsgroupcodehere';
To get total number of responses for all lecturers in a group you can use the code below and some js to accumulate all responses for a given lecturer_id, this will give you the total, you can also refine the query and get the count total from the query itself.
select responses.*, users.group_code, quizzes.quiz_id, quizzes.name, modules.module_id, modules.user_id as lecturer_id from responses inner join quizzes on quizzes.quiz_id = responses.quiz_id inner join modules on quizzes.module_id = modules.module_id INNER JOIN users ON users.user_id = modules.user_id WHERE users.group_code = 'enteradminsgroupcodehere' order by responses.user_id;
#645
To get it in the format that he would like for the csv, use js to create the correct object format. Alternatively, you can use a complex query to return the object for each user although that may require some expert psql knowledge.
#644
select responses.*, users.group_code, quizzes.quiz_id, quizzes.name, modules.module_id, modules.user_id as lecturer_id from responses inner join quizzes on quizzes.quiz_id = responses.quiz_id inner join modules on quizzes.module_id = modules.module_id INNER JOIN users ON users.user_id = modules.user_id WHERE users.group_code = 'enteradminsgroupcodehere' order by responses.user_id;