can any one help me to get sql query that get all required grading assignment for all the courses
i have the old one that not work now in moodle aover 2.7
i use moodle 3.1
this is the old sql query
select `c`.`category` AS `ClassRoom`,`c`.`id` AS `CourseID`,`a`.`id` AS `AssignmentID`,`asb`.`id` AS `SubmissiontID`,`u`.`id` AS `UserID`,`c`.`fullname` AS `Course`,`a`.`name` AS `Assignment`,concat('',`a`.`name`,'') AS `Assignmentlink`,concat(`u`.`firstname`,' ',`u`.`lastname`) AS `UserName`,`asb`.`grade` AS `Grade`,`gi`.`grademax` AS `Max`,from_unixtime(`asb`.`timemodified`) AS `timemodified`,from_unixtime(`asb`.`timemarked`) AS `timemarked` from (((((`mdl_assignment_submissions` `asb` join `mdl_assignment` `a` on((`a`.`id` = `asb`.`assignment`))) join `mdl_user` `u` on((`u`.`id` = `asb`.`userid`))) join `mdl_course` `c` on((`c`.`id` = `a`.`course`))) join `mdl_course_modules` `cm` on((`c`.`id` = `cm`.`course`))) join `mdl_grade_items` `gi` on((`a`.`id` = `gi`.`iteminstance`))) where ((`cm`.`instance` = `a`.`id`) and (`cm`.`module` = 1) and ((`asb`.`timemodified` > `asb`.`timemarked`) or (`asb`.`grade` < 0)) and (((`asb`.`data1` is not null) and (`asb`.`data1` <> '')) or (`asb`.`numfiles` > 0)) group by `u`.`id`,`a`.`id` order by `c`.`category`,`c`.`id`
If you want some help cleanup your SQL properly and post it inside Pre-Formatted paragraph so that we can actually see what is happening.
SELECT
`c`.`category` AS `ClassRoom`,
`c`.`id` AS `CourseID`,
`a`.`id` AS `AssignmentID`,
`asb`.`id` AS `SubmissiontID`,
`u`.`id` AS `UserID`,
`c`.`fullname` AS `Course`,
`a`.`name` AS `Assignment`,
concat('', `a`.`name`, '') AS `Assignmentlink`,
concat(
`u`.`firstname`,
' ',
`u`.`lastname`
) AS `UserName`,
`asb`.`grade` AS `Grade`,
`gi`.`grademax` AS `Max`,
from_unixtime(`asb`.`timemodified`) AS `timemodified`,
from_unixtime(`asb`.`timemarked`) AS `timemarked`
FROM
(
(
(
(
(
`mdl_assignment_submissions` `asb`
JOIN `mdl_assignment` `a` ON (
(
`a`.`id` = `asb`.`assignment`
)
)
)
JOIN `mdl_user` `u` ON ((`u`.`id` = `asb`.`userid`))
)
JOIN `mdl_course` `c` ON ((`c`.`id` = `a`.`course`))
)
JOIN `mdl_course_modules` `cm` ON ((`c`.`id` = `cm`.`course`))
)
JOIN `mdl_grade_items` `gi` ON (
(
`a`.`id` = `gi`.`iteminstance`
)
)
)
WHERE
(
(`cm`.`instance` = `a`.`id`)
AND (`cm`.`module` = 1)
AND (
(
`asb`.`timemodified` > `asb`.`timemarked`
)
OR (`asb`.`grade` < 0)
)
AND (
(
(`asb`.`data1` IS NOT NULL)
AND (`asb`.`data1` <> '')
)
OR (`asb`.`numfiles` > 0)
)
GROUP BY
`u`.`id`,
`a`.`id`
ORDER BY
`c`.`category`,
`c`.`id`
this is the old sql the new moodle database use new assign table the result i hope to get is to get all required graded submission
Why is cm.module = 1 ? Is it not hardcoding? What if there is no course module with id 1?
i did n't understand you well
i want only to get the ungraded submission
so i use the cm.module = 1 which mean to get only the assignments
this is not id it is the module type =1
And who guarantees that cm.module = 1 is assignment? You should never assume these things. In most systems assignment usually has id of 2 not 1.
Another JOIN to mdl_modules to get the modules.id based on modules.name = 'assignment' (or 'assign' for an updated Moodle site)
This is the fixed query. You had incorrectly linked few tables and conditions. Also over complicating with parenthesis...
SELECT c.category AS ClassRoom
, c.id AS CourseID
, a.id AS AssignmentID
, asb.id AS SubmissiontID
, u.id AS UserID
, c.fullname AS Course
, a.name AS Assignment
, CONCAT('', a.name, '') AS Assignmentlink
, CONCAT(u.firstname, ' ', u.lastname) AS UserName
, asb.grade AS Grade
, gi.grademax AS GradeMax
, FROM_UNIXTIME(asb.timemodified) AS timemodified
, FROM_UNIXTIME(asb.timemarked) AS timemarked
FROM mdl_assignment_submissions asb
JOIN mdl_assignment a ON a.id = asb.assignment
JOIN mdl_user u ON u.id = asb.userid AND u.deleted = 0
JOIN mdl_course c ON c.id = a.course
JOIN mdl_modules mo ON mo.name = 'assignment'
JOIN mdl_course_modules cm ON a.course = cm.course AND cm.instance = a.id AND cm.module = mo.id
JOIN mdl_grade_items gi ON a.course = gi.courseid AND gi.itemmodule = 'assignment' AND a.id = gi.iteminstance
WHERE
( asb.timemodified > asb.timemarked OR asb.grade < 0 )
AND
( (asb.data1 IS NOT NULL AND asb.data1 <> '') OR asb.numfiles > 0 )
GROUP BY u.id, a.id
ORDER BY c.category, c.id
thank you a lot you really fix it but there is something
even this new query it work on old moodle
i need the same result query but for the new moodle database structure
the table assignment replaced with mdl_assign
just replace assignment with assign in the query
Actually this is quite different. Well look into database and try to understand what is stored and where.
This is a start of query, you should figure out rest of the tables and conditions
SELECT c.category AS ClassRoom
, c.id AS CourseID
, a.id AS AssignmentID
, asb.id AS SubmissiontID
, u.id AS UserID
, c.fullname AS Course
, a.name AS Assignment
, CONCAT('', a.name, '') AS Assignmentlink
, CONCAT(u.firstname, ' ', u.lastname) AS UserName
, ag.grade AS Grade
, gi.grademax AS GradeMax
, FROM_UNIXTIME(asb.timemodified) AS timemodified
FROM mdl_assign_submission asb
JOIN mdl_assign a ON a.id = asb.assignment
JOIN mdl_assign_grades ag ON ag.assignment = a.id AND asb.userid = ag.userid
JOIN mdl_user u ON u.id = asb.userid AND u.deleted = 0
JOIN mdl_course c ON c.id = a.course
JOIN mdl_modules mo ON mo.name = 'assign'
JOIN mdl_course_modules cm ON a.course = cm.course AND cm.instance = a.id AND cm.module = mo.id
JOIN mdl_grade_items gi ON a.course = gi.courseid AND gi.itemmodule = 'assign' AND a.id = gi.iteminstance
GROUP BY u.id, a.id
ORDER BY c.category, c.id
thanks a lot really for your work
i have modify it a Little and add the conditions it is now work fine
for the entire course or all the course if remove the course_id
i have modify it a Little and add the conditions it is now work fine
for the entire course or all the course if remove the course_id
SELECT c.category AS ClassRoom
, c.id AS CourseID
, a.id AS AssignmentID
, asb.id AS SubmissiontID
, u.id AS UserID
, c.fullname AS Course
, a.name AS Assignment
, CONCAT('', a.name, '') AS Assignmentlink
, CONCAT(u.firstname, ' ', u.lastname) AS UserName
, ag.grade AS Grade
, gi.grademax AS GradeMax
, FROM_UNIXTIME(asb.timemodified) AS timemodified
FROM mdl_assign_submission asb
JOIN mdl_assign a ON a.id = asb.assignment
LEFT JOIN mdl_assign_grades ag ON ag.assignment = a.id
AND asb.assignment = ag.assignment
AND asb.userid = ag.userid
AND asb.attemptnumber = ag.attemptnumber
JOIN mdl_user u ON u.id = asb.userid AND u.deleted = 0
JOIN mdl_course c ON c.id = 32 AND c.id = a.course
JOIN mdl_modules mo ON mo.name = 'assign'
JOIN mdl_course_modules cm ON a.course = cm.course AND cm.instance = a.id AND cm.module = mo.id
JOIN mdl_grade_items gi ON a.course = gi.courseid AND gi.itemmodule = 'assign' AND a.id = gi.iteminstance
WHERE
asb.latest = 1
AND asb.timemodified IS NOT NULL
AND asb. STATUS = 'submitted'
AND (
asb.timemodified >= ag.timemodified
OR ag.timemodified IS NULL
OR ag.grade IS NULL
)
GROUP BY u.id, a.id
ORDER BY u.id,c.category, c.id
Hi sir can you help me: https://moodle.org/mod/forum/discuss.php?d=344155