Assignment Database Tables

Assignment Database Tables

by Moodle Admin -
Number of replies: 9

Hello everyone,

I'm trying very hard to understand the relationship between the assignment related tables in Moodle and after I spent like 2 days to try and figure out how it's been designed I decided to ask the community in the hope that someone will reply.

What I'm trying to do is to locate a particular assignment in a course in our Moodle site and import some student grades and feedback there. In order to do this correctly I need to identify the ID of the assignment in order to map the data accordingly in my csv file. And here's comes the challenge..!

mdl_assign gives me the assignment id (4323) based on the course id (2420).

I was expecting to get results from other tables based on the same id  but this is not the case.

Therefore, I'm trying to understand how the following tables are related to each other:

mdl_assign

mdl_assignment

mdl_assignfeedback_comments

mdl_assignment_submissions

I looked in mod/assignment/db/install.xml but I can see only the structure of 2 tables and I'm there's something I'm missing here.

I would be extremely grateful if someone could help me with this!

Thank you in advance and have a lovely weekend!

 

Average of ratings: -
In reply to Moodle Admin

Re: Assignment Database Tables

by Mary Cooch -
Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators

Why do you have to do it via the database - why can't you do it via the assignment itself?  (Using the offline grading worksheet feature  - see Assignment settings

Average of ratings: Useful (1)
In reply to Mary Cooch

Re: Assignment Database Tables

by Moodle Admin -

Hi Mary,

First I tried to import the grades AND the feedback through the grade book, the process was successfully completed but the feedback was never transferred. Therefore I decided to do it manually through the DB but now I found the solution thanks to you - I hadn't tried to use offline grading at all.

The original problem was that the students couldn't see their grades/feedback in an Assignment 2.2 activity, it's very odd as only one of them could see his grade. And in order to solve this issue I tried everything (upgrade the old assignment, export-import, backup-restore..) and still I couldn't import and view both of them. That's were I thought my last option was doing it through the database.

Thanks once again for your help.

All best

Afroditi

Average of ratings: -
In reply to Moodle Admin

Re: Assignment Database Tables

by Richard Oelmann -
Picture of Core developers Picture of Plugin developers Picture of Testers

I don't think the mdl_assignmentxxx tables have any relation to the mdl_assignxxx tables - my understanding is that the mdl_assign tables relate to the newer, redeveloped assignment tool (mod/assign), while the mdl_assignment tables relate to the old 2.2 and before assignment tool (mod/assignment).

Average of ratings: Useful (1)
In reply to Moodle Admin

Re: Assignment Database Tables

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Off the top of my head:

  • mdl_assign - this holds the record with the settings for the assignment
  • mdl_assign_submission - this holds the details of all the submissions that have been made to the assignment (linked, by assignment, to the mdl_assign table; linked to either a user or a group by userid (for individual submissions) or groupid (for team submissions)).
  • mdl_assign_grades - a grade given to a particular user (userid) for a particular assignment (assignment)
  • mdl_assignsubmission_XX - these hold additional submission data for the different types of submission plugins (e.g. comments, files)
  • mdl_assignfeedback_XX - these hold the feedback for different types of feedback plugin (e.g. comments from teachers, pdf annotation, etc.)

When an assignment is created, an mdl_assign record is created for it, as well as a mdl_grade_item record, to store the grade in the gradebook.

When a student submits an assignment, an mdl_assign_submission is created, along with any mdl_assignsubmission_XX records that are needed by the types of submission plugin that are enabled.

When the teacher grades the assignment, they fill in whatever feedback plugins have been enabled - this data is stored in the mdl_assignfeedback_XX tables. They also give a grade, which is stored in the mdl_assign_grades.

After the feedback + grades have been stored internally by the assignment plugin, they are then stored in the gradebook. This is achieved by inserting a record into mdl_grade_grades (linked to the mdl_grade_item record that was created for the assignment), with the 'grade' coming from the mdl_assign_grades table and the 'feedback' coming from whichever feedback plugin has been configured to update the gradebook (by default, the 'comments' feedback plugin).

I'm not completely certain, but to manually insert grades into an assignment, you are going to need to make sure suitable entries are added to (at least): mdl_assign_submission, mdl_assign_grades, mdl_grade_grades (along with any feedback going into mdl_assignfeedback_comment).

Average of ratings: Useful (5)
In reply to Davo Smith

Re: Assignment Database Tables

by Moodle Admin -

Thanks to everyone for your replies, I'm grateful there's such a helpful community out there.

And thank you SO MUCH for such a detailed description of the database tables. 

All best
Afroditi

Average of ratings: -
In reply to Davo Smith

Re: Assignment Database Tables

by Dan Olson -

Davo,

A very helpful list. Thanks for that. I ran across this post while I was trying to run a SQL report using the Ad-hoc database queries plugin. Unfortunately I can't seem to make this report work.  Would you know how to update this to get it to work with the new assignment module? 

SELECT 

u.firstname AS "First",

u.lastname AS "Last",

c.fullname AS "Course",

a.name AS "Assignment"

 

FROM prefix_assignment_submissions AS asb

JOIN prefix_assignment AS a ON a.id = asb.assignment

JOIN prefix_user AS u ON u.id = asb.userid

JOIN prefix_course AS c ON c.id = a.course

JOIN prefix_course_modules AS cm ON c.id = cm.course

 

WHERE asb.grade < 0 AND cm.instance = a.id

AND cm.module = 1

 

ORDER BY c.fullname, a.name, u.lastname

Average of ratings: -
In reply to Dan Olson

Re: Assignment Database Tables

by Damyon Wiese -
One good tip for finding db queries in moodle is to visit a page that is probably performing a query close to what you are looking for (like the grading table in mod_assign). Then edit the page you are looking at - add this somewhere near the top.

$DB->set_debug(true);

This will print every DB query directly to the page.

The query you are trying to build is quite hard because you need to consider assignments with multiple submissions per student (and may want to exclude blind marking assignments that have not been released).

The best example in the code is probably in count_submissions_need_grading in mod/assign/locallib.php.

Average of ratings: -
In reply to Damyon Wiese

Re: Assignment Database Tables

by Samuel Than -

We were trying similar approach of trying to bulk insert assignments. Was successful to create them for Moodle 1.9

However, since upgrading to use Moodle 2.7 ... when viewing of grades, it shows blank screen and running a debugging...we found that the grade item which is related to the assignment does not belong to a grade category.


We're thinking of running the grade_regrade_final_grades($courseID) function to force the creation of the items in grade category... but doesn't seem to help..


Any thoughts on how to go around that ?



Average of ratings: -
In reply to Dan Olson

Re: Assignment Database Tables

by baraa abd el-hady -

sorry i know you post this from long time but have you found the solution for this query 

to work for the new moodle 

Average of ratings: -