Show me all assignment items requiring grading

Show me all assignment items requiring grading

by Lee Cujes -
Number of replies: 26

Hi all

Desired outcome:

  • A configurable SQL report showing assignment items that require grading (note we've tried all such supplied reports and none fit the bill)
  • We only want to see the latest attempt from the student in this report.

We have the following query established (we obtained this from the 'submitted SQL reports' on the Moodle site but it didn’t work in the supplied form and required rebuilding):

SELECT
asub.id,
CONCAT(u.firstname ,' ',u.lastname) AS User,
CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.fullname,'</a>') AS Course,
ccat.name AS Course_category,
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/view.php',CHAR(63),'id=',cm.id,'">',a.name,'</a>') AS Assignment,
asub.status,
agr.grade,
# agr.grader,
CONCAT(u.firstname ,' ',u.lastname) AS User,
FROM_UNIXTIME(asub.timecreated) AS Submitted

FROM prefix_assign_submission AS asub
JOIN prefix_assign AS a ON a.id = asub.assignment
JOIN prefix_user AS u ON u.id = asub.userid
JOIN prefix_course AS c ON c.id = a.course
JOIN prefix_course_categories AS ccat ON ccat.id = c.category 
JOIN prefix_course_modules cm ON cm.instance=a.id AND cm.course = a.course AND cm.module = 1
# note: cm.module equalling 1 represents the Assignment module
LEFT JOIN prefix_assign_grades agr ON agr.assignment = a.id AND agr.userid = asub.userid

WHERE agr.grade IS NULL AND asub.attemptnumber = asub.latest AND (asub.status = "submitted" OR asub.status = "new")
# grade is null because there will be no table entry here at all until graded
# WHERE 1=1 
%%FILTER_CATEGORIES:c.category%%
ORDER BY asub.timecreated DESC

This produces a result as follows:




Our Issue: 

Records are appearing in the report (e.g. Valerie Christie) stating the status is “submitted”, yet when we view this in the user interface, we see this:



The item in the report has a submission date of 25 October (when Valerie originally submitted), and we can see above it has a last modified date of 27 October, when the marker has accessed the submission, found it lacking, and provided feedback to Valerie that more detail is required (hence status of “reopened”).

So, given this submission has a status of “reopened”, it should not be appearing in this report whatsoever (the SQL asks only for status=submitted or status=new).

Looking at the database for Valerie’s submissions on this particular assignment we begin to understand why we are seeing this record flowing into the report...

2kSKOrLEuOGBE8i49xDiD1dnaNaquBtntSm-uyQ_2tavD5vHqw0ARhzuDmZT1tK53dZKY4j531lEK56J27wivM-pba0Hco2ile6mdB2EDBvGRnd65jqRRski0lkZhvQl0K5L_8lq

...namely, it’s showing the first submission (the first row), yet there’s a later version. We don’t want this! In our SQL, we set the clause asub.attemptnumber = asub.latest to ensure we’re only seeing the latest submission (but it's clearly not working).


I do not understand how “attemptnumber” and “latest” are actually working in the system. I would expect the following:



attemptnumber

latest

Additional action:

First submission:

0

0

none

Second attempt

1

1

First submission record “latest” is updated to 1

Third attempt

2

2

First submission record “latest” is updated to 2

Second submission record “latest” is updated to 2


Can anyone shed some light on the mechanics of this?


And we'd really appreciate any advice as to how to best alter our query to get the result we’re after, that is:

  • Only show us assignment items requiring grading

  • Don’t show us items that have been subsequently updated and are not the latest versions

Kind Regards

Lee Cujes




Average of ratings: -
In reply to Lee Cujes

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers

I think this looks good but I am not sure that the attemptnumber equals latest, which is certainlynot the case with assignments with unlimited attempts. I think that is where to look for the issue.

You might look at the code in the /mod/assign/locallib.php for the functions like count_submissions_need_grading and others that show how the attempt number and grade are related. The grade is always the latest and seems to have the attempt number there as well.

Finally, in the WHERE clause you are filtering on the statuses for new and submitted but there is also a value for reopened (as well as draft.) See the start of the locallib.php file where the constants are declared. You might need to add that condition too.
Average of ratings: -
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Lee Cujes -
Hi Randy

Thanks for your reply, it's much appreciated.

I'll do some delving, however getting into the code might be above my capabilities, we'll see. It would be good if anyone else knows how 'attemptnumber', 'latest' and 'grade' are interrelated.

In the WHERE clause as you've rightly pointed out there are four possibilities for 'status' (new | submitted | draft | reopened). We've specifically excluded 'draft' (not ready for marking) and 'reopened' (we see this typically where the marker has provided feedback for the student and the ball is in the student's court (also not ready for marking). There may be some fine tuning to do here.

(I will say it does seem crazy to me that we need to go to these lengths to generate a list of items to be marked in an LMS -- it seems like an extremely generic, out of the box requirement! Anyway - onwards! smile)

Kind Regards
Lee Cujes
Average of ratings: -
In reply to Lee Cujes

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
Lee,

Good luck, I have not worked with assignments with multiple attempts yet, so I have not figured this all out. Perhaps someone who has can chime in here with some details.

I certainly agree that anyone would think this would be easy to do in any LMS - as a standard report. But my experience is that the data structures for the assignment grading - especially if you are using feeback or rubrics or marking guides - are a convoluted mess.
Average of ratings: Useful (1)
In reply to Lee Cujes

Re: Show me all assignment items requiring grading

by Thomas Stanley-Jones -
Hi Lee,
Maybe you could use a subquery to return the max value for attemptnumber? I'm not great at subqueries but I'm wondering if this would work.
WHERE asub.attemptnumber = (SELECT MAX(attemptnumber) FROM asub)
How the where clauses in the subquery interact with the Joins and main WHERE's I'm not sure.
Average of ratings: -
In reply to Thomas Stanley-Jones

Re: Show me all assignment items requiring grading

by Lee Cujes -
Hi Thomas

I see where you're going there and I like it.

Just banging it in there as an additional WHERE clause returns no records, and removing the other WHERE clauses so just this remains gives me all records of the student who has performed the most resubmissions (13).

So it's definitely syntax now. I have high hopes this will do what's needed. I'll do my own research and will post if i have success. In the meantime, if anyone can assist with the syntax it would certainly be much appreciated.

Kind Regards
Lee Cujes
Average of ratings: -
In reply to Lee Cujes

Re: Show me all assignment items requiring grading

by Darrell Klar -
Afternoon Lee.
I'm not sure if you have resolved this, but I am battling the same issues at the moment.

Following this thread, I have added the check for the latest submission. Code for the WHERE clause needs "AND asub.latest = (SELECT MAX(attemptnumber) FROM prefix_assign_submission)" added, and this works well.

As soon as I start testing, I have another issue... the report includes submissions that have already been graded! Any ideas on the code to remove assessments where they have been graded? Any support on that end would be appreciated.

I have also included code to add to the SELECT statement to link to grading the assessment directly - concat('Grade')

I hope this helps, and hope that someone might be able to provide the snippet of code I need for ignoring submissions that have been graded.
Regards.
Average of ratings: Useful (1)
In reply to Darrell Klar

Re: Show me all assignment items requiring grading

by Lee Cujes -
Hi Darrell

Thanks for responding!

I still have not solved this and I have also noted the issue of the report showing items that have already been graded, despite efforts to avoid this.

Fingers crossed someone can jump in to assist. As a matter of interest, the 'GradeMe' plugin (which we use) somehow manages to do this (I think). Examining the code may assist someone more savvy than me.

Kind Regards
Lee Cujes
Average of ratings: -
In reply to Lee Cujes

Re: Show me all assignment items requiring grading

by Motasem Al Haj Ali -
Hi Lee,

I hope the following will help.

Basically as coordinator looking to this report you do not want to chase the reopened cases so you do not want to keep the reopened records in this report, but in other hands you still get the same student but different and old record with submitted status. So in the above example for the student with userid = 39170, in the first code you will get just the record with submitted status then when you chase the case you find the teacher already looking after that case or student. If this is right the easiest way to allow reopened cases to appear in your report and order the report by user id, like the following. I think this solution is good especially if you have this report on the course level. In my case I found very little number of reopened records so I think it is no harm to keep this status in the report.

WHERE agr.grade IS NULL AND asub.attemptnumber = asub.latest AND (asub.status = "submitted" OR asub.status = "new" OR asub.status = 'reopen')
%%FILTER_CATEGORIES:c.category%%
ORDER BY u.id

IF you are trying to make the report smaller you can restrict the reopened records by timemodified, basically the reopened status records will be created in two situations, first when the student reopen the submission before the due date of the assignment or when the teacher reopen the submission after the assignment duedate. We can drop the first situation by (asub.timemodified> a.duedate) so we will have just reopened records that had been opened by teachers and avoid all reopened records created by the students before the assignment's duedate.

TO simplify the problem we are looking for "the last record has been create in the assig_submission table AND has no grads" excluding the draft status.

MAYBY using IF or CASE statements to the code could help.

I hope that will help, and looking forward to here from if that's help or if you already solve this issue.

Regards
Mo
Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Motasem Al Haj Ali -
Hi Lee,

First please ignore the previous reply.

I hope the following will help.

Basically as coordinator looking to this report you do not want to chase the 'reopened' cases so you do not want to keep the 'reopened' records in this report, but in other hands you still get the same student but different and old record with submitted status. So in the above example for the student with userid = 39170, in the first record you will get just the record with submitted status then when you chase the case you find the teacher already looking after that case or student. If this is right the easiest way to allow 'reopened' cases to appear in your report and order the report by user id, like the following. I think this solution is good especially if you have this report on the course level. In my case I found very little number of a 'reopened' records so I think it is no harm to keep this status in the report.

WHERE agr.grade IS NULL AND asub.attemptnumber = asub.latest AND (asub.status = "submitted" OR asub.status = "new" OR asub.status = 'reopen')
%%FILTER_CATEGORIES:c.category%%
ORDER BY u.id

IF you are trying to make the report smaller you can restrict the reopened records by timemodified, basically the reopened status records will be created in two situations, first when the student reopen the submission before the due date of the assignment or when the teacher reopen the submission after the assignment duedate. We can drop the first situation by (asub.timemodified> a.duedate) so we will have just reopened records that had been opened by teachers and avoid all reopened records created by the students before the assignment's duedate.

To simplify the problem we are looking for "the last record has been create in the assig_submission table AND has no grads" + "excluding the draft status". I know that you have ( asub.attemptnumber = asub.latest ) but I think the 'reopened' record has been created by the teacher and not the student. Also I think we need to add another condition that the current date greater than due date for the assignment as that will save us to review a lot of cases that still before the due date.

MAYBY using IF or CASE statements in the code could help but I am going to show you my code.

SELECT
asub.id,
u.id AS userid,
CONCAT(u.firstname ,' ',u.lastname) AS User,
CONCAT('',c.fullname,'') AS Course,
ccat.name AS Course_category,
CONCAT('',a.name,'') AS Assignment,
asub.status,
agr.grade,
# agr.grader,
CONCAT(u.firstname ,' ',u.lastname) AS User,
#FROM_UNIXTIME(asub.timecreated) AS Submitted,
FROM_UNIXTIME(asub.timemodified) AS TIMEMODIEFIED,
FROM_UNIXTIME(a.duedate) AS duedate,
CURRENT_TIMESTAMP() AS Currentdate


FROM prefix_assign_submission AS asub
JOIN prefix_assign AS a ON a.id = asub.assignment
JOIN prefix_user AS u ON u.id = asub.userid
JOIN prefix_course AS c ON c.id = a.course
JOIN prefix_course_categories AS ccat ON ccat.id = c.category
JOIN prefix_course_modules cm ON cm.instance=a.id AND cm.course = a.course AND cm.module = 1
# note: cm.module equalling 1 represents the Assignment module
LEFT JOIN prefix_assign_grades agr ON agr.assignment = a.id AND agr.userid = asub.userid

WHERE agr.grade IS NULL
AND CURRENT_TIMESTAMP() > a.duedate
AND FROM_UNIXTIME(asub.timecreated) LIKE '2022%'
AND asub.attemptnumber = asub.latest

AND (asub.status = "submitted" OR asub.status = "new" OR (SELECT asub.status = 'reopened' WHERE asub.timemodified > a.duedate))

#AND (u.id= xxx OR u.id= xxx)
#AND asub.id =xxx
#OR asub.id = xxx
#OR asub.id= xxx
#AND ccat.name ='xxxxx 2021/2022'
# grade is null because there will be no table entry here at all until graded
# WHERE 1=1
%%FILTER_CATEGORIES:c.category%%
ORDER BY u.id

I hope that will help, and looking forward to here from you.

Regards
Mo
Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
Lee and Motasem,

I've been lurking a bit but wanted to test out some things before I posted. I think this is how the sequence works.

1) The student submits

This creates the entry in the submissions table, with status of 'submitted' - but there is no grade table entry created yet (so agr.grade IS NULL).

2) Cron runs

The task "Background porcessing for assignments module" runs on the next cron and it creates a new entry in the assign_grades table. It sets the grade to -1.0000 and sets the grader to -1. This is how you know that the assignment has not been graded yet. -1 is not a real user for sure, and probably -1 is not a real grade (but you never know;)

3) The teacher grades it

The grader value in assign_grades is set to the grader's user id and then the grade is entered. The submission status stays as 'submitted' the whole time.


So, there are two possible conditions for an ungraded submission: it is NULL until an entry is made in the assign_grades table by the cron task, or later it has an entry with a grader of -1 and grade of -1 after the row was created. So, you would want to cover both of those conditions to find all the assignments waiting to be graded. Something like WHERE agr.grade IS NULL OR (agr.grade = -1.0000 AND agr.grader = -1) should cover both cases.

Attached is the report xml which I used to do some testing on this so I could follow the path of the submissions. Don't run it in production without adding some where filters to it! since it is written to show you *everything* in the submissions and assign grade tables ;) which is probably a lot of data.

So far, I have only tested this out on assignments with one submission.

Let me know if that is how you see it working too.






Average of ratings: Useful (2)
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
I'd like to add that I found an interesting bug (that may be a feature?).

When the cron task runs and creates the row in the assign_grades table, it set the timecreated to the time that this happens, as normal.

However it sets the timemodified to the time that it copies from the submission row in the assign_sumbissions table. Therefore, the timemodified is always EARLIER than the time created for the grades.

There may be some obscure reason why this is so; I have not looked into the code. Perhaps it needs to check the time modified date when creating any entry, or for some other purpose to exclude those until graded.

But, it is logically impossible for the time modified to be earlier than the timecreated: and in most other cases, for example creating enrolments, Moodle devs do the logical thing and set the timecreated and timemodified to be the same when it creates the entry. But apparently, not for assignment grading.

When a grader actually grades the submission, then the assign_grades entry does the proper thing: sets the timemodified to the time of the grading just as we expect.

So, the timemodified on ungraded entries is inaccurate and should not be trusted. If you are doing any time comparisons on that, you need to take that problem into account in those cases of ungraded submissions. Only after a real user has graded the submissoin will the timemodified in the assign_grades table be accurate.

In the example below, you will see in row four an ungraded entry with a modified time that is three minutes before its creation time. It will stay like this until actually graded.


Attachment screenshot_11405.jpg
Average of ratings: -
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers

The code that does this explains why this is so. It says:

$grade->timecreated = time();
// If we are "auto-creating" a grade - and there is a submission
// the new grade should not have a more recent timemodified value
// than the submission.
if ($submission) {
    $grade->timemodified = $submission->timemodified;
} else {
    $grade->timemodified = $grade->timecreated;
}

This is in /mod/assign/locallib.php around line 3947 where the function get_user_grade() creates the grade entry if there is not one already.

Why "the new grade should not have a more recent timemodified value than the submission" is not clear to me since the timecreated time is the actual time of modification. Perhaps this date is used later on in some logic when comparing the two entries.

To sum up:

  • assign_grade timecreated means the time the grade entry was created by cron
  • assign_grade timemodified means the time the submission (not the grade) was last modified when the grader is -1
  • assign_grade timemodified is updated to the actual time of grading only when it has actually been graded by a grader as indicated by a real userid
 





Average of ratings: -
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Motasem Al Haj Ali -
Randy,

I test it, you are right.

Basically, The conditions should be
- (agr.grade IS NULL OR (agr.grade = -1.0000 AND agr.grader = -1))
- AND a.duedate - AND asub.attemptnumber = asub.latest THIS work some time but it dose not achieve what we expected as the example that LEE share with us in the first post,
-AND (asub.status = "submitted" OR asub.status = "new" OR (SELECT asub.status = 'reopened' WHERE asub.timemodified > a.duedate)) I tried this condition and I got a record where the student has new status but in the module he got his marks.

WE need to notice the following
- there is two stakeholder creating records in more than one table AND we need the last record has been created by any one of them
- This report just deal with assignments but what about other modules which has weight in the course
-WE need to think about some other tables such grade_items and grade_grades to add another restriction to get all weighted modules (assignment but they also could be Quizzes or Forums..)
- There is also a case where the student did not create any record, I mean did not access the assignment at all, this record probably will not be there unless the teacher access the student grading area (not sure, please correct me if I am wrong)

I have tried the following code
SELECT
####Grade_Grages
mgg.id,
mgg.userid,
mgg.finalgrade,
mgg.timemodified,


###USER
mu.firstname,
mu.lastname,

###grade_items
mgi.itemname,
mgi.itemmodule,
CONCAT('',mgi.courseid,'') AS Courseid,
mgi.aggregationcoef

FROM prefix_grade_items AS mgi
JOIN prefix_grade_grades AS mgg ON mgg.itemid = mgi.id
JOIN prefix_user AS mu ON mgg.userid = mu.id
#JOIN prefix_assign_grades AS mag ON mu.id = mag.userid
#JOIN prefix_grade_grades_history AS mggh ON mu.id = mggh.userid

WHERE
mgi.courseid = xxxxx
AND mgg.finalgrade IS NULL
AND mgi.aggregationcoef >0 
#this condition to get all graded items has a weight in the course.

It is Ok, but I found two issues, first one that show the teachers, second some students did not submit or graded but still they do not appear in the report. WE fix the first issue by restrict the report to show just students. But I could not find any reason why the other students dose not appear in the report. I checked the logs for each students has no grades in the assignment looking for any logic reason but I could not find a thing.  

Please try this and let me know what do you think.

Mo
Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
Motasem,

Ah, I already know the answer to that one: "asub.attemptnumber = asub.latest THIS work some time but it dose not achieve what we expected" ...

That is because asub.latest is a boolean where 1 = true and 0 = false. It is not coordinated with the asub / agr attemptnumber and only has those two values.

The code always marks the most recent attempt at latest =1 and then immediately marks all the user's previous attempts as 0. That's how it tracks the most recent one. If there is only one attempt allowed, you will always see that the latest =1 and the attemptnumber = 0. When multiple attempts are allowed, then looking for latest =1 that will tell one of the user's attempts the most recent.

So, if you want only the most recent submission, then something like WHERE asub.latest=1 is all you need.
Average of ratings: Useful (2)
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Lee Cujes -
(Hi Mo, Randy et al, I am reading this with great interest but haven't yet had time to try the various versions suggested - hope to spend some time on this soon)
Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
Motasem,

Also, the submission status is interesting. When you say, "I tried this condition and I got a record where the student has new status but in the module he got his marks. " I am not sure if you mean a status of 1 for the latest column on the attempt, or if you are referring to the submission status of 'new' in the asub.status field.

For the submission status, I am seeing that a status of 'new' is set in two cases:

1) For the student, it is created the moment they go into the Assignment to start work on the submission. If they do nothing and leave, then the row is still there in the database waiting for them the next time they come back. The status will only turn to 'draft' or 'submitted' when they save it as a draft or actually submit. This will automatically also get a latest =1 as well and therefore any earlier attempts will get latest set to 0.

This is how Moodle tracks that the user has started the attempt. It works the same way for single attempts and multiple attempts.

2) When the teacher creates the Assignment, it adds a row to the submission table with a 'new' status. Nothing ever seems to happen to this row at all. It just sits there. Perhaps it is used in some way by the code to set things up, but it does not seem to change at all.

So, when dealing with a asub.status of 'new' it matters what role the user has. You can skip all the teacher roles. Also, if you only care about what has been submitted for grading, you can skip both 'new' and 'draft'.
Average of ratings: -
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Motasem Al Haj Ali -
Randy and Lee,

I tried a few different ways but each one I end up with a problem, I think there is something wrong in the DB structuring but not sure.

I was trying to think in different way and I noticed that there are a few different cases that we are trying to identify in the way of coding that Lee or me suggest above, so I think we need to think in different way. Please check the image below. We are looking to identify 7 cases out of 8, if we identify just the first case and then excluded them from the all participants that will probably help to achieve what we looking for.  

Note: there are a few different ways of submissions (file, Mahara, onlinetext,..)  not sure if all of them connected to the assign_submission table. Also we need to think about students who submit as group, as one of the students will submit instead of others. Not sure if that will be recorded as each student summited. 

Please let me know what do you think. 
DB cases
Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
All submissions are always in the assign_submissions table.

Their details will be elsewhere depending on the type they are (online text, file, etc.) and those tables will refer back to the main submissions table. Similar for grading: the grade will be in the assign_grades table, but the details can be elsewhere depending on the grading type, eg scale, marking guide, rubric.

I am not sure what your column for "resubmit" does. What matters in the data is the asub.status field and how that is control by whether the assignment has an attemptsreopenmethod and a maxattempts > -1. The key is how many attempts are allowed in the assign setting and if there is more than one, then tracking which one is the current one (asub.latest.).

For groups, the assign.tempsubmission and the asub.groupid tell you all you need to know about group submissions: it's just one submission and you have to get the actual members from the group itself.

I think - but haven't tested this for sure - that then when the grading is done, it will set the grades individually for each group member. So, in case of a group submission you have to take that into account to align the grades even though there is only one submission.
Average of ratings: -
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Motasem Al Haj Ali -

Randy, 

There something missed here, 

Back to your second last reply, not sure if Latest has 0 or 1 value, all the cases I have it was 1. 

Try this code:

SELECT *

FROM prefix_course AS mc

JOIN prefix_assign AS ma ON mc.id = ma.course

JOIN prefix_assign_submission AS masub ON masub.assignment = ma.id

WHERE mc.id = 55579

I will share this graph just to gather the ideas together. Probably we need to full description for how is Moodle store the data in the assign_submission. For instance when I tried the following code

SELECT *

FROM 

prefix_assign_submission AS masub 

WHERE masub.assignment =74684

I got 39 record for an assignment has 29 participants. Some of those records for admins and those admins not students or teachers in that course. Their submission status is new. There are 14 records has new status but just 4 of them students did not submit, the others teachers or admins. All the records has latest = 1.

tables

Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Motasem Al Haj Ali -

Sorry a bit of correction, no admins in the above example, just loop manager, teachers, editing teacher,... But I saw admins in different course example, I think that because they attend that course in some way, not sure any how. 


Regards

Mo

Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
If Latest is always = 1, that is normal if all the assignments have only 1 attempt. You will only see  0 if you have assignments with "Additional attempts" set to manual or untilpass and there is actually more than one graded attempt.

You will find the status of 'new' in a couple of cases: when the teacher (or manager or whoever) first creates a new assignment, it always puts an entry in there with a status of 'new' - I don't know why it does this, but it never changes and you can ignore it for real submissions from students.

I have also seen a status of 'new' then the student is actually working on the assignment: it creates the 'new' entry and it stays that way until the student either 1) saves it as a 'draft' (if the settings allow that) or 2) they submit it.

So, the roles matter if you are looking only for the student ones. I see admins in mine all the time when I am testing as an admin: it just records the user id of whoever makes the new assignment. It may not be the teacher at all.

Usually I don't bother with adding all the roles checking: I just ignore a status of 'new'. Teachers will never have a submission status of 'submitted' since they don't have permission to submit (by default anyway.)
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
To my knowledge so far, the valid statuses are:

new
draft
submitted
reopened
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Show me all assignment items requiring grading

by Motasem Al Haj Ali -

We are using Moodle 3.9 and till now I just saw new and summited status. But also we need to notice that draft and reopened are little number of cases. I mean probably they are exist in Moodle3.9 but I did not see them yet. 

There is a few different cases when we try to look for not graded students, such as admin, Moodle manager, teachers, coordinators, some one had been enrolled by mistake and then canceled and some people has graded but their submissions status still new, team submission and all other possible cases that I did not experienced yet. That is why I was trying to suggest to look for graded students and exclude them from participants instead of trying to identify all the other cases (please look the post which has the table).  To do that I tried to get all participants from a course then restrict the role to students but I still see teachers or Moodle managers and they have student role. It is a bit confusing but not sure yet. 


Something else WE can think about, if you navigate to administration >> development>> XMLDB editor >> grade/report/grader/db If we can have a table for this then we will avoid all this headache. Please navigate to one of your courses which has an assignment and marked students then navigate to your grade >> View >> grade report to see what I mean. Just suggestion.


Anyhow I think we are progressing hopefully some one else jump and help in some way.


Thanks

Mo

Average of ratings: -
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
To me, the heart of the matter is:

FROM prefix_assign_submission asub
LEFT JOIN prefix_assign_grades ag ON ag.assignment = asub.assignment AND ag.userid = asub.userid AND ag.attemptnumber = asub.attemptnumber
WHERE asub.status = 'submitted'
AND (ag.grader = -1 OR ag.grader IS NULL)

If the user can't submit, no matter their role, it will not be here. By default only the Student role has mod/assign:submit allowed. If you have admins and managers enrolling themselves as students in courses, then, Moodle can't help you much since they are properly enrolled. (Maybe put those people in a cohort and then exclude those cohort members from the query in your where clause?).


I have never seen a grade for a 'new' status other than -1 with a grader of -1. You can't grade a 'new' status anyway. Also, 'reopened' status is used when "Additional attempts" is set to manual or until pass. The additional attempts get the 'reopened' status after the teacher has allowed a new attempt or if using until pass then the assignment did not get the passing grade. In either case, a new submission entry is made with the 'reopened' status. This marks a new, additional attempt and it will have the 'reopened' status until the student actually makes a submission. If you aren't using "Additional attempts" you won't see it.
Average of ratings: Useful (1)
In reply to Motasem Al Haj Ali

Re: Show me all assignment items requiring grading

by Randy Thornton -
Picture of Documentation writers
By the way, here is how group submissions work.

The asub.userid is 0 and then the groupid column holds the id of the group. It doesn't record the id of the user who does the actual submission on behalf of the group (maybe in the logs it does, but not in the submission table).
Average of ratings: -