Custom SQL queries report on Moodle 1.9 & MySQL

Custom SQL queries report on Moodle 1.9 & MySQL

by Nancy Hepinstall -
Number of replies: 11

I would like to use the report described at http://docs.moodle.org/22/en/ad-hoc_contributed_reports#Site-Wide_Grade_Report_with_Just_Course_Totals

As written, this query errors out in my Moodle.  I have tried tweaking it with no success.  Is there a query that would produce the same results for Moodle 1.9 and MySQL? 

The Global Grades report plugin works well but unfortunately doesn't give the course title in the final mark column, so requires a lot of manipulation in Excel every time it is run.

Thank you.

Average of ratings: -
In reply to Nancy Hepinstall

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by Nancy Hepinstall -

Please disregard.  I found the solution. 

Average of ratings: -
In reply to Nancy Hepinstall

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by Sergio Daniel Franco Reyes -

Hi Nany, can you tell me what is the solution, I have the same proble.

Thank you

Average of ratings: -
In reply to Sergio Daniel Franco Reyes

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by Joshua De la Vega -

Hi Sergio,

Hope this works for you as it does for me:


SELECT u.firstname AS 'First', u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name', cc.name AS 'Category',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT( c.fullname, ' Course Total' )
ELSE gi.itemname
END AS 'Item Name', ROUND( gg.finalgrade, 2 ) AS Grade, DATE_ADD( '1970-01-01', INTERVAL gi.timemodified
SECOND ) AS Time
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemtype = 'course'
ORDER BY lastname
LIMIT 0 , 30

/iwm.pX0dx

Average of ratings: -
In reply to Joshua De la Vega

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by JC Dodo -

Hi,

I hope someone could help me to convert this SQL statement to MySQL.

When I saved this to Moodle 1.9, it gives me error "No explicit prefix"..

 

SELECT u.firstname AS First, u.lastname AS Last, c.fullname AS Course, a.name AS Assignment, 

''<a href="http://moodle.domain.com/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 mdl_assignment_submissions AS asb INNER JOIN
mdl_assignment AS a ON a.id = asb.assignment INNER JOIN
mdl_user AS u ON u.id = asb.userid INNER JOIN
mdl_course AS c ON c.id = a.course INNER JOIN
mdl_course_modules AS cm ON c.id = cm.course AND a.id = cm.instance
WHERE (asb.grade < 0) AND (cm.module = 1) AND (asb.teacher = 0)
ORDER BY a.course, asb.assignment, Last

 

Looking forward to hear anyone.

Thanks.

James

Average of ratings: -
In reply to JC Dodo

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by Nigel Irwin -

James, there is no prefix for Last in your Order by clause, should be u.last

Average of ratings: -
In reply to Nigel Irwin

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by JC Dodo -

Hi Nigel,

I have followed what you said and this is what the final SQL Query statements I have.

SELECT
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment",

'<a href="http://moodle.domain.com/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 mdl_assignment_submissions AS asb
JOIN mdl_assignment AS a ON a.id = asb.assignment
JOIN mdl_user AS u ON u.id = asb.userid
JOIN mdl_course AS c ON c.id = a.course
JOIN mdl_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.last

But it's still giving me error "No explicit prefix" message.

James

Average of ratings: -
In reply to JC Dodo

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by Nigel Irwin -

Coukld it be that you have two (+)s between char(63) and 'id=' 

char(63) +
+ 'id='

Average of ratings: -
In reply to Nigel Irwin

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by JC Dodo -

Still the same. Are you sure that is allowed in MySQL? Looks like it's different but I am not so sure.

Average of ratings: -
In reply to JC Dodo

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by G. M. -

Hi James,

Given the fact that you already solved that error (please avoid posting twice):

http://moodle.org/mod/forum/discuss.php?d=129901#p905432

Take note that you cannot cast into a VARCHAR; you need to use CHAR(##), like so:

SELECT
u.firstname AS "First",
u.lastname  AS "Last",
c.fullname  AS "Course",
a.name      AS "Assignment",

'<a href="http://moodle.domain.com/mod/assignment/submissions.php' + char(63) + 'id=' +
cast(cm.id AS char(25)) +
'&userid=' +
cast(u.id AS char(25)) +
'&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

For the example I used a CHAR of size 25, but you need to check what would be the best size.

Average of ratings: -
In reply to G. M.

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by JC Dodo -

Hi Guillermo,

Im so sorry for my duplicate posts in this forum.

I tried your advise and its accepting. However, when I click View Report, the column "Assignmentlink" does not showing URLs. It is displaying total numbers only. I did this at first to replace varchar into char and it's working but the output is not url links.

I am following this custom sql at http://docs.moodle.org/22/en/ad-hoc_contributed_reports#All_Ungraded_Assignments_w.2F_Link

Thanks.

James

Average of ratings: -
In reply to G. M.

Re: Custom SQL queries report on Moodle 1.9 & MySQL

by JC Dodo -

Finally, I got the answer with my own experimentation. 

I think this would be helpful for other Moodle users who are using configurable reports and MySQL DB backend.

 

SELECT 
u.firstname AS "First",
u.lastname  AS "Last",
c.fullname  AS "Course",
a.name      AS "Assignment",

concat('<a href="http://moodle.domain.com/mod/assignment/submissions.php' , char(63) , 'id=' ,
cast(cm.id AS char) ,
'&userid=' ,
cast(u.id AS char) ,
'&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

 

Thanks.

James

Average of ratings: Useful (1)