Where to look for reference on ad hoc reports?

Where to look for reference on ad hoc reports?

by John Bear -
Number of replies: 2

Good afternoon,

I am trying to craft reports using the "Ad Hoc database queries" in Moodle 3.3.

Although I found the schema it is partial, and missing several tables. I have looked across all the documentation and have a hard time locating anything that provides clear picture how all the tables interrelate.

Where could I get more information on how to craft ad hoc SQL statements?

For example - I am trying to write a query that lists all courses within a date range, the count of enrolled students and the count of passing student in each.

I can get the number of students enrolled, but how do I combine it with 

SELECT
 prefix_course.shortname,
 prefix_course.id,
 prefix_course.startdate AS Start_Date,
 prefix_course.enddate AS End_Date,
 COUNT(prefix_user_enrolments.id) AS Enrolled
FROM prefix_course
 JOIN prefix_enrol ON prefix_enrol.courseid = prefix_course.id
 JOIN prefix_user_enrolments ON prefix_user_enrolments.enrolid = prefix_enrol.id 
WHERE prefix_course.startdate >= :startdate
GROUP BY prefix_course.id 
ORDER BY prefix_course.startdate, prefix_course.shortname
Average of ratings: -
In reply to John Bear

Re: Where to look for reference on ad hoc reports?

by Bastian Cisternas Coquedano -

Hey! there are ver little information about the database... just like i did, the only thing you can do, is to investigate it by your own.

If you want to use ad hoc queries, you can use a method that receive your sql variable and save the results in other variable.

In the example above y write a sql and save it on $sql variable, then i gave it to the method: $DB->get_records_sql($sql) and save the resultset of the select query on $users variable.

After that in the scritp where i called the method which had the code above, y save the elements into a table using html_table() and i print it.


I hope this help you, iam almost new on this 

Bye! smile

$sql = 'select  u.id,
		u.firstname,
		u.lastname,
		u.email
                from {course} c
                join {context} ctx on c.id=ctx.instanceid and ctx.contextlevel=50
                left join {role_assignments} ra on ra.contextid=ctx.id and ctx.instanceid=c.id
                left join {user} u on u.id=ra.userid
                left join {role} r on ra.roleid=r.id
                where c.id=' . $course . ' and r.shortname="' . $role . '" order by u.id';
    $users = $DB->get_records_sql($sql);
Average of ratings: -
In reply to Bastian Cisternas Coquedano

Re: Where to look for reference on ad hoc reports?

by Elizabeth Dalton -

The two most helpful references I can suggest are:

https://docs.moodle.org/en/ad-hoc_contributed_reports

and

https://docs.moodle.org/dev/Database_Schema

Marcus Green has been contributing his time to prepare database schema documents using MySQL Workbench for many versions of Moodle now. I have found these to be very helpful, especially in combination with the ad-hoc contributed reports page.

I also learned a lot by installing tools like PHPMyAdmin or Adminer on a test site and experimenting with queries there.

Average of ratings: Useful (1)