Ad-hoc database queries

Tim or Mahmoud, it would be useful if you were to update the links to the documents to a later version
as it is pointing to 2.2 at the moment with lots of references to Moodle 1.9 which makes it look rather
out of date. If you just take out the version bit (the 22) it should automatically redirect to the latest version
i.e. https://docs.moodle.org/en/Custom_SQL_queries_report

A scheduled query can be sent to an email address. Look at the bottom of the form when creating a query.

Really liking this tool! We have a hosted site and are using this tool to automate attendance and other reports to help with retention. Currently the email report requires that a user log in to Moodle and have viewing permissions. Suggestion to allow .csv file to be sent directly to ftp site or email address.

Try changing FROM_UNIXTIME(created) -> created in the WHERE.

This is a great plugin. Thanks.
However, I am having problems entering a date as parameter.
This works:
SELECT
subject, firstname, lastname, email, FROM_UNIXTIME(created)
FROM
{forum_posts}
INNER JOIN
{user} ON {forum_posts}.userid = {user}.id
WHERE
parent > 0
AND FROM_UNIXTIME(created) BETWEEN '2015/04/27' AND '2015/05/03'
Whereas this doesn't (although it correctly prompts for start and finish dates):
SELECT
subject, firstname, lastname, email, FROM_UNIXTIME(created)
FROM
{forum_posts}
INNER JOIN
{user} ON {forum_posts}.userid = {user}.id
WHERE
parent > 0
AND FROM_UNIXTIME(created) BETWEEN :start_date AND :end_date
Any thoughts why?
Thank you for your time.
Craig

I am panicking. My Moodle for 2015 has been using the new standard log store with legacy logging turned off, and I have no idea how to tweak this SQL code to make it work!
SELECT COUNT(l.id) hits, concat('',c.fullname,'') AS Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,CASE
WHEN c.fullname LIKE '%-13%' THEN '2013'
WHEN c.fullname LIKE '%-14%' THEN '2014'
WHEN c.fullname LIKE '%-15%' THEN '2015'
END AS YEAR
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
FROM prefix_log l
INNER JOIN prefix_course c ON l.course = c.id
GROUP BY c.id
HAVING Modules > 2
ORDER BY YEAR DESC, hits DESC

I got it!!! It's not perfect (yet), but this gives the outstanding activities (not graded) for each student.
SELECT Company, StudentID, Student, Course, GROUP_CONCAT(Item)
FROM (
SELECT Institution AS 'Company', u.id AS StudentID, CONCAT(u.lastname, ', ', u.firstname) AS 'Student', c.fullname AS Course, gi.itemname AS 'Item', gg.itemid AS ItemsCompleted
FROM prefix_user AS u
JOIN prefix_groups_members AS gm ON gm.userid = u.id
JOIN prefix_groups AS g ON g.id = gm.groupid
JOIN prefix_course AS c ON c.id = g.courseid
JOIN prefix_grade_items AS gi ON gi.courseid = g.courseid
LEFT JOIN prefix_grade_grades As gg ON gi.id = gg.itemid
WHERE u.institution != '' AND NOT u.deleted AND gi.itemmodule = 'assign'
AND gg.itemid IS NULL
ORDER BY CONCAT(Company, Student, Course), gi.sortorder) AS tmp
GROUP BY CONCAT(Company, Student, Course)

The error I get is Error when executing the query: Error reading from database Incorrect key file for table '/var/mysqltmp/#sql_1f3_0.MYI'; try to repair it...I think I'm blowing the tmp space I think I'll need to increase the size.

I didn't know the debug would work The AS tmp is not part of the ORDER BY, it's the naming for the nested SELECT

Try turning on https://docs.moodle.org/29/en/Debugging (set it to DEVELOPER level temporarily) then try again. You should get a useful error message.
AS tmp in the ORDER BY looks wrong.

Hi Tim, How's it going? I trying to run this query ti get the outstanding activities for each student. it runs in phpmyadmin, but it won't run in Moodle. In Moodle, the first query (nested select) runs fine, it's when I add the NOT IN select that causes the problem. Any idea? Or another way to do this? Cheers, Mykl
SELECT Company, StudentID, Student, Course, GROUP_CONCAT(Item), SortOrder
FROM (
SELECT Institution AS 'Company', u.id AS StudentID, CONCAT(u.lastname, ', ', u.firstname) AS 'Student', c.fullname AS Course, gi.itemname AS 'Item', gi.sortorder AS SortOrder
FROM {user} AS u
JOIN {groups_members} AS gm ON gm.userid = u.id
JOIN {groups} AS g ON g.id = gm.groupid
JOIN {course} AS c ON c.id = g.courseid
JOIN {grade_items} AS gi ON gi.courseid = g.courseid
WHERE u.Institution != '' AND NOT u.deleted
AND itemmodule = 'assign' AND CONCAT(u.id, ', ', gi.id) NOT IN (
SELECT CONCAT(u.id, ', ', gg.itemid) 'Item'
FROM {user} AS u
JOIN {grade_grades} AS gg ON gg.userid = u.id )
ORDER BY CONCAT(Company, Student, Course), SortOrder ) AS tmp
GROUP BY CONCAT(Company, Student, Course)

Hi Tim, I'm not to good on the SQL stuff, and I need to run a simple report to extract info on single users, for course/subject completion, dates and grades.
Can you advise the best code for this please?
Thanks
Bruce

After adding the plugin, did you got to Site administration -> Notifications to complete the install?

Hi.
We tried to install this plugin to our Moodle version 2.56. We installed the newest version 2.7 for Moodle 2.4+ (2014061800) but we ended with en error.
Capability "report/customsql:view" was not found! This has to be fixed in code.
line 389 of /lib/accesslib.php: call to debagging()
line 1258 of /lib/adminlib.php: call to has_capability()
line 3408 of /lib/navigationlib.php: call to admin_externalpage->check_access()
...
Did anyone experienced this error? Thanks,
Ben

If you know exactly how many placeholders you want, you can do
WHERE courseid IN (:id1, :id2, :id3)
Or, you could do
WHERE course.shortname LIKE :pattern
and pattern would need to be somthing like %maths%
Anything more than that would require a fair amount of development.