All ungraded assignments w/Link returns no results

All ungraded assignments w/Link returns no results

by jensy will -
Number of replies: 14

Hello,

I'm using M3.2. I've just added the Configurable reports block. I'm trying to add "All ungraded Assignments w/Link

SELECT 
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment",
 
'<a href="http://mymoodleaddress.com.au/mod/assignment/submissions.php' + CHAR(63) +
+ 'id=' + CAST(cm.id AS VARCHAR) + '&userid=' + CAST(u.id AS VARCHAR) 
+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
AS "Assignmentlink"
 
 
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


But get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR) + '&userid=' + CAST(u.id AS VARCHAR) 
+ '&mode=single&filter=0&offset=' at line 8

Can someone plz help? 

Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Randy Thornton -

You left out the CONCAT at the beginning of 


'<a href=
 for the AssignmentLink selection.
Average of ratings: -
In reply to Randy Thornton

Re: All ungraded assignments w/Link returns no results

by jensy will -

Thanks Randy. I added 

CONCAT'<a href="http://mymoodleaddress.com.au/mod/assignment/submissions.php' + CHAR(63) +

+ 'id=' + CAST(cm.id AS VARCHAR) + '&userid=' + CAST(u.id AS VARCHAR) 

+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'

AS "Assignmentlink"


but still get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ CHAR(63) +
+ 'id=' + CAST(cm.id AS VARCHAR) + '&userid=' + CAST(u.id AS VARCH' at line 7
Where am I going wrong?!
Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Randy Thornton -

You are not using the proper syntax for CONCAT.

I suggest you look at some examples in the Ad-hoc reports pages: https://docs.moodle.org/32/en/ad-hoc_contributed_reports where there are a lot of good examples of it being used this way.

And take a look at the documentation for it: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat and http://www.mysqltutorial.org/sql-concat-in-mysql.aspx.

When you get syntax error message doing reports, you ALWAYS have a syntax error. And usually it is a simple formal error with some punctuation missing from the required syntax.

Average of ratings: -
In reply to Randy Thornton

Re: All ungraded assignments w/Link returns no results

by jensy will -

Hi Randy,

Yes, those are the resources I referred to first. I tried all syntax corrections, but still returns 'no results'.


thanks for your help though.

Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Randy Thornton -

Good. If the query runs without syntax errors, that is good.

If it is returning no results, then there are several possibilities but the two most common are:

- There's actually no matching data. So no results are returned. 

- The query doesn't really do what you think it does. It may have a logic problem.

- The query is for an older or different version of Moodle, where some component may be deprecated or changed, but not so much as to throw an error.


There's a note on the query on the page that it was written for the older, original Assignments module that was replaced by the newer one between Moodle 2.2 and 2.3. 

So that is why there is no data at all: you are on 3.2 and though this module still exists in 3.2, it is turned off in newer versions of Moodle, and is there only to help those upgrading old courses who need to convert those older assignments. It is not the one you are using (or ever should use). 

All the assignments on your site are done in the  newer assignment module. (In queries, you will see this because the new assignment module uses the table with names 'assign' not 'assignment'; so queries with prefix_assign will work on your site.) 

So this query will not help you to get what you want, unfortunately. 


Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Randy Thornton -

Jensy,

That query is just a mess. So old and broken.

Try this code instead. It works in 3.1. This one SHOWS the grades, but that's what you want to test before turning it off to show just the ungraded ones. Let me know if this works for you.

- Randy


SELECT
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,
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/view.php',CHAR(63),'id=',cm.id,'">',a.name,'</a>') AS Assignment,
asub.status,
agr.grade
 
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_modules cm ON cm.instance=a.id AND cm.course = a.course AND cm.module = 1
# note - tis sloppy to assume Assign is mod 1 but it almost always will be
LEFT JOIN prefix_assign_grades agr ON agr.assignment = a.id AND agr.userid = asub.userid
# WHERE agr.grade IS NULL
# grade is null because there will be no table entry here at all until graded
 
ORDER BY c.fullname, a.name, u.lastname


Average of ratings: -
In reply to Randy Thornton

Re: All ungraded assignments w/Link returns no results

by jensy will -

Thanks Randy. this gets us a bit closer to more efficient tracking of program completion.

Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Gerhold Kooper -

Good day,

I have also tried that script and got the same error. I now tried the example code given by Randy Thornton below and it runs, but not quite what I am looking for.

I wish to have create a report that would basically the Grading Summary as seen with assignment activity as teacher; but for multiple courses recursively in a category.  Would so appreciate that. Hope I can find help here.

Average of ratings: -
In reply to Gerhold Kooper

Re: All ungraded assignments w/Link returns no results

by jensy will -

Agreed! We are also looking for a report that will show the assignment status (submitted / graded, etc) for all courses in a category. I was hoping that this report would help with tracking assignments and grading. Currently, we have to go into each course individually to allocate graders and see assignment status. It would be amazing to have this info in a single report!

Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Randy Thornton -

To add a Category filter you can use as a pull down in Configurable Reports is simple. Use the code below, and add a "Categories (include sub categories)" filter to the report in the Filters tab.

However, that is a simple category list of all categories, it is not a hierarchical, structured list. To do that would be very difficult given the poor way in which the data for categories is organized in the tables. It is not normalized for SQL and designed to be used by PHP. That's the reason you won't find any such reports in the Ad-hoc reports page. Meanwhile, this at least is a basis you can use to extend it to other reports or add other fields if you need.


SELECT
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
 
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 - tis sloppy to assume Assign is mod 1 but it almost always will be
LEFT JOIN prefix_assign_grades agr ON agr.assignment = a.id AND agr.userid = asub.userid

# WHERE agr.grade IS NULL
# grade is null because there will be no table entry here at all until graded
WHERE 1=1 
%%FILTER_CATEGORIES:c.category%%
 
ORDER BY c.fullname, a.name, u.lastname
Attachment screenshot_2192.jpg
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: All ungraded assignments w/Link returns no results

by jensy will -

this is exactly what we need! But for some reason it returns "no records found". I copied and pasted exactly as above. Head scratch.

Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Randy Thornton -

Did you also add the Category filter in the Filters tab of the report? It will look like the picture below...

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

Re: All ungraded assignments w/Link returns no results

by jensy will -

I did. But this is the result


Attachment SQL Report no records.PNG
Average of ratings: -
In reply to jensy will

Re: All ungraded assignments w/Link returns no results

by Randy Thornton -

Try this for a sanity check - just shows a simple list of courses and categories. Do you get the expected results? It's possible or even probable there's some error in my code which works on my test but not in your site. This will show tell us if the data is good, so that the places the query is pulling from are the same as I expect.


SELECT
c.shortname AS Course,
ccat.name AS Course_category 
FROM prefix_course AS c
JOIN prefix_course_categories AS ccat ON ccat.id = c.category 
WHERE 1=1 
%%FILTER_CATEGORIES:c.category%%
ORDER BY c.shortname
Average of ratings: -