General developer forum

 
 
Picture of Dale Davies
Useful SQL Queries?
Group Particularly helpful Moodlers
I've been working on some queries recently, thought I'd share some of the ones that others may find useful. I'm also very interested to hear any thoughts on how I can improve my SQL. Im not the best at writing SQL so any advice will be much appreciated.

This first one counts up the modules added to a given course, just change the course id number for "c_modules.course" in the WHERE clause...


SELECT modules.name, COUNT(c_modules.id) AS "Module Count"

FROM
mdl_course_modules c_modules
INNER JOIN mdl_modules modules ON c_modules.module = modules.id

WHERE
c_modules.course = 560
AND c_modules.visible = 1

GROUP BY

modules.name


This next one produces a basic report with a row per course, counting the amount of students, student course views (hits) and the average view per student, a few other peices of data (such as the path and a few id's) are thrown in to make it useful within a web application. Also, you may wish to ignore the "TopLevelCatName", I store our cost centre codes in the category description field...

SELECT
y.TopLevelCatID,
description AS TopLevelCatName,
y.CategoryID,
y.CategoryName,
y.CategoryPath,
y.CourseID,
y.CourseName,
y.Views,
y.Students,
y.ViewsPerStudent

FROM
mdl_course_categories cats
INNER JOIN (
SELECT
x.CourseID,
x.CourseName,
x.Views,
count(*) AS Students,
(x.Views/count(*)) AS ViewsPerStudent,
TRIM(LEADING '/' FROM SUBSTRING_INDEX(x.CategoryPath, '/', 2)) AS TopLevelCatID,
x.CategoryID,
x.CategoryName,
x.CategoryPath

FROM
mdl_role_assignments roles
INNER JOIN (
SELECT
course.id AS CourseID,
course.fullname AS CourseName,
count(*) AS Views,
categories.id AS CategoryID,
categories.name AS CategoryName,
categories.path AS CategoryPath,
context.id AS ContextID

FROM
mdl_course_categories categories
INNER JOIN mdl_course course ON categories.id = course.category
INNER JOIN mdl_log log ON course.id = log.course
INNER JOIN mdl_context context ON course.id = context.instanceid

WHERE
log.module = "course"
AND log.action = "view"
AND context.contextlevel = '50'

GROUP BY
categories.id,
categories.name,
categories.path,
course.fullname,
course.id
) x ON roles.contextid = x.ContextID

WHERE
roles.roleid = '5'

GROUP BY
x.CourseID,
x.CourseName,
x.CategoryID,
x.CategoryName,
x.CategoryPath,
x.Views
) y ON cats.id = y.TopLevelCatID

ORDER BY
y.CategoryName, y.CourseName
 
Average of ratings:Useful (4)
Picture of Dale Davies
Re: Useful SQL Queries?
Group Particularly helpful Moodlers
Here's a few more too, not sure if Ive posted these before but someone asked me for them recently so here they are again.

Select users who have not logged in for over 180 days (but not those who have never logged in)...

SELECT *
FROM mdl_user
WHERE lastlogin < UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 180 DAY))
AND lastlogin != 0
AND lastaccess < UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 180 DAY))
AND deleted = 0


Delete users who have not logged in for over 180 days (but not those who have never logged in)....

UPDATE mdl_user
SET deleted=1
WHERE lastlogin < UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 180 DAY))
AND lastlogin != 0
AND lastaccess < UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 180 DAY))
AND deleted = 0


---------------------------------------------------------------------------------------------


Select users Who have NEVER logged in........

SELECT id, concat( firstname, " ", lastname ) AS name, lastaccess, lastlogin, currentlogin
FROM mdl_user
WHERE currentlogin = 0
AND lastlogin = 0
AND lastaccess = 0
AND deleted = 0


Delete users Who have NEVER logged in........

UPDATE mdl_user
SET deleted=1
WHERE currentlogin = 0
AND lastlogin = 0
AND lastaccess = 0
AND deleted = 0
 
Average of ratings: -
Lei Zhang
Re: Useful SQL Queries?
 
Thanks Dale, i've got a sql query might be useful for some. It lists a number of views(hits) per student in a course from 2010.
just change 1965 to your course id number.

SELECT u.username AS Username, u.firstname AS "First Name", u.lastname AS "Last Name", u.email AS Email, count(l.userid) AS Views
 FROM `prefix_log` l, `prefix_user` u, `prefix_role_assignments` r
 WHERE l.course=1965
 AND l.userid = u.id
 AND l.time > UNIX_TIMESTAMP('2010-01-01 00:00:00')
 AND r.contextid= (
 SELECT id
 FROM prefix_context
 WHERE contextlevel=50 AND instanceid=l.course
 )AND r.roleid=5
 AND r.userid = u.id
 GROUP BY l.userid
 ORDER BY Views
 
Average of ratings:Useful (2)
Picture of Dale Davies
Re: Useful SQL Queries?
Group Particularly helpful Moodlers
Nice, cheers Lei. Here's an extended version of my first one (the one that counts course modules), which gives a count for resources and activities in a given course (as oposed to just modules from the modules table)....

SELECT x.type, COUNT(x.type) AS mod_count
FROM (
SELECT resources.type
FROM
mdl_resource resources
WHERE
resources.course = 560
UNION ALL
SELECT modules.name AS type
FROM
mdl_course_modules c_modules
INNER JOIN mdl_modules modules ON c_modules.module = modules.id
WHERE
modules.name != 'resource'
AND c_modules.course = 560
) x
GROUP BY
x.type

Once again though, Im no SQL expert, if anyone knows a better way to do this I'd love to hear it smile
 
Average of ratings: -
Training
Re: Useful SQL Queries?
 
Hi Guys,

I don't suppose you can help me out with this MySQL query found in the following post?

http://moodle.org/mod/forum/discuss.php?d=153130

Thanks!

Steve
 
Average of ratings: -
Picture of Daniel Neis Araujo
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful MoodlersGroup Translators
Hello,

Congratulations for the initiative of publishing usefull queries!

do you know the CustomSQL admin report? http://moodle.org/mod/data/view.php?d=13&rid=2884
With it you can store these queries and made them available to users to run it on demand or schedule the queries to run monthly or weekly.


 
Average of ratings:Useful (2)
Picture of Amir Elion
Re: Useful SQL Queries?
Group Translators

I added a link to this useful psot in the relevant DOC:

http://docs.moodle.org/en/Custom_SQL_queries_report#See_also

 
Average of ratings: -
Picture of Dale Davies
Re: Useful SQL Queries?
Group Particularly helpful Moodlers
Thanks Amir, I had no idea that documentation was there. Hopefully the link will provide useful information to some people.

I'm going to continue to post queries I find useful as I come up with them, so if anyone else has any to share please do. Also, as I keep saying, if anyone can help me to improve my SQL I would be extremely grateful smile
 
Average of ratings: -
Picture of Dale Davies
Re: Useful SQL Queries?
Group Particularly helpful Moodlers
Finally, whilst I was working on counting stuff in course pages, I put this query together which lists all the resources and modules available and makes a count for a given course for those resources or modules that course contains.

Just change the course id at resources.course and c_modules.course ...

SELECT score.type, IFNULL(y.mod_count,0) mod_count
FROM (
SELECT x.type, COUNT(x.type) AS mod_count
FROM (
SELECT resources.type
FROM
mdl_resource resources
GROUP BY TYPE
UNION ALL
SELECT modules.name AS type
FROM
mdl_modules modules
WHERE
modules.name != 'resource'
GROUP BY TYPE
) x
GROUP BY
x.type
) AS score
LEFT JOIN (
SELECT x.type, COUNT(x.type) AS mod_count
FROM (
SELECT resources.type
FROM
mdl_resource resources
WHERE
resources.course = 560
UNION ALL
SELECT modules.name AS type
FROM
mdl_course_modules c_modules
INNER JOIN mdl_modules modules ON c_modules.module = modules.id
WHERE
modules.name != 'resource'
AND c_modules.course = 560
) x
GROUP BY
x.type
) y ON score.type = y.type


Once again, your feedback is much appreciated.
 
Average of ratings: -
Picture of Jeff Snyder
Re: Useful SQL Queries?
 

Hello,

I'm not sure I'm putting this post in the right place, but here goes.

What I'm having trouble with is this...I'd like one report where I can see user information that is already present on other reports.  The information I need is this:  All Site Users in the Student Role, their grades on every quiz in every course, whether they have accessed the resources in every course or not, and whether or not the enrolled user has ever logged in.  It would be nice to have all of these fields sorting, but I can do that after export.

What do you think...is it possible?  What might be the best, most efficient way to build this intricate report?  I've figured out enough about using the query browser to make it work so SQL would be great.

Thanks for your input in advance!!

--Jeff

 
Average of ratings: -
Picture of yonney atsu
Re: Useful SQL Queries?
 

Very kind of you guys for sharing your queries. Wondering if anyone could advice me on the query below. Basically it sought to find out the number of students who have completed and passed each module at a particular time. Its however relies on the certificate issued table. I donot want to rely on certificates issued because there are chances of a student completing a quiz but not accesss his certificate . Cant seems to figure it out using the quiz tables.

SELECT c.name AS 'COURSE NAME', Count(c.name) AS 'NO OF COURSE COMPLETERS '
    
      
FROM   user u, certificate_issues ci, certificate c

where u.id = ci.userid AND
      ci.certificateid = c.id

GROUP BY c.name

 

 
Average of ratings: -
Picture of Ivn Vai
Re: Useful SQL Queries?
 

Hi there,

I am using Moodle 2.2.1 and I would also like to display all resources (of any type) and activities (everythng including quiz,scorm,assignment, etc, all of them) for a particular course by passing the course id.

I tried using your query that gives a count for resources and activities in a given course (as oposed to just modules from the modules table).... but its been giving me the following error

'Unknown resources.type in d SQL query.' I am guessing since there is no type field in new mdl_resource I am getting this error but then type's being called many times in this query and I dont know how to sort the error. Can any one help me with this please?

Thanks.

 
Average of ratings: -
Picture of Ivn Vai
Re: Useful SQL Queries?
 

I would really appreciate if some one could reply in this context and help me with my queries as I am getting nothing but errors or results which are not 100% right.

Hoping to see a reply from some one soon.

Thanks.

 
Average of ratings: -
wen photo
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

This is a very helpful SQL query and useful discussion thread, thanks!

By the way I'm helping a friend of mine to figure out a query but she want to focus on the number of "forum post" instead of number of views (hits). I took a look and it seems that in Moodle 1.9.x the table mdl_forum_posts table would help but to come up with such a SQL query is a bit tricky (not to mention that she want to be course-centered to find out reports based on each course and the # of teacher/student forum postings). Has anyone here done similar query before? Any help would be really appreciated. Thanks!

Wen

 
Average of ratings:Useful (1)
Rosario playing soccer
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

A week or so ago I had to make investigations about some wiki posts. As far as I see, for forums, there are the following tables involved

  • mdl_forum (one record for every forum with course-ID to which it belongs)
  • md_forum_discussions (with the course-id and forum-id to which the discussion belongs)
  • mdl_forum_posts (with discussion-id to which it belongs and fortunately also user-id of person who wrote the post)

Now you need to relationally combine these records with the course or the courses you want to count/see and make also a relation to context and roles to find out teacher and/or student posts.

I could try to make an example here friday. Rosario

 
Average of ratings:Useful (1)
wen photo
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

Thought that I would share with my current findings with all of you. Here is the query that I have so far:

SELECT lo.course AS Course_ID, co.shortname AS Course_ShortName, co.fullname AS Course_FullName, r.name, COUNT(r.name) as Forum_Posts
FROM prefix_log lo
INNER JOIN prefix_course co ON (co.id = lo.course)
INNER JOIN prefix_user us ON (lo.userid = us.id)
INNER JOIN prefix_role_assignments ra ON (ra.userid = us.id)
INNER JOIN prefix_context ctxt ON (ctxt.id = ra.contextid AND
ctxt.instanceid = co.id AND ctxt.contextlevel = 50)
INNER JOIN prefix_role r ON (r.id  = ra.roleid)
WHERE lo.action LIKE '%add%' AND FROM_UNIXTIME(lo.time) > '2012-03-19' AND FROM_UNIXTIME(lo.time) < '2012-03-23' AND lo.course <> 1 AND lo.module = 'forum'
GROUP BY Course_ID, r.name
ORDER BY co.fullname

 

You might have noticed that this is good for "weekly" report and can show the administrator(s) the ratio between student forum postings and instructor forum postings in each course. I tried to look into the mdl_forum_discussion and mdl_forum_posts tables but these two can not really generate what I was looking for, so I had to use mdl_log table at the end (use "module" and "action" fields). If anyone has any better ideas please let me know. It's fun to do data mining with Moodle! smile

Wen

 
Average of ratings:Useful (1)
Picture of Susan Mangan
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Hi all, I'm looking to do a query to find empty courses within a certain category for clean up purposes.  I can pull out all the courses within the category I want and I was HOPING that I'd get the majority of my results by comparing timecreated to timemodified but that's not going to be enough sad  I'm wondering what the best course of action might be here ... I'm thinking I could look at the teacher logs for 'change' activity in each course since timecreated?  Thoughts on this greatly appreciated!!!

 
Average of ratings: -
Picture of Geoff Wild
Re: Useful SQL Queries?
 

What are you considering as empty?

 
Average of ratings:Useful (1)
Picture of Susan Mangan
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Hi Geoff - I think I would define empty as no content.  I guess that would mean no modules although each of our templates includes at least one News Forum to begin with.  User count would be irrelevant if there is no content, or any changes made since the creation of the site.  I tried looking at activity/changes since site creation but it doesn't look like the logs track importing content?  I find that a bit odd since that is definitely a change.   I managed to get through most of our stuff manually before the holidays, I'll work on something for the future and check back for any useful tips posted here smile

 
Average of ratings: -
Picture of Emilie LENEL
Re: Useful SQL Queries?
 

Hi Susan

Did you found the right SQL query to list empty courses within a certain category for clean up purposes. ?

Thanks smile

 
Average of ratings: -
C'est moi :-)
Re: Useful SQL Queries?
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

It's a very good idea to share usefull queries smile

I use this one to see permissions overides on categories :

SELECT rc.id, ct.instanceid, rc.roleid, rc.capability, rc.permission, DATE_FORMAT( FROM_UNIXTIME( rc.timemodified ) , '%Y-%m-%d' ) AS timemodified, rc.modifierid, ct.instanceid, ct.path, ct.depth
FROM `mdl_role_capabilities` AS rc
INNER JOIN `mdl_context` AS ct ON rc.contextid = ct.id
AND `contextlevel` =40

change 40 to 50 to see permissions overrides on courses wink

 
Average of ratings:Useful (2)
C'est moi :-)
Re: Useful SQL Queries?
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Another (very) simple one, to show totally opened courses (visible, opened to guets, with no password) :

SELECT id , category , fullname , shortname , enrollable
FROM `mdl_course`
WHERE `guest` =1
AND `password` = ""
AND `visible` =1

hope this can help...

 
Average of ratings:Useful (2)
Picture of Dale Davies
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Thanks I'll give those a try later, I have a few more lying around here somewhere too so I'll get around to posting them sometime smile

 
Average of ratings: -
Picture of Nadav Kavalerchik
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Hi Severin smile

I made a little change to your sql query :

(display categories names, too)

SELECT rc.id, ct.instanceid, ccat.name, rc.roleid, rc.capability, rc.permission, DATE_FORMAT( FROM_UNIXTIME( rc.timemodified ) , '%Y-%m-%d' ) AS timemodified, rc.modifierid, ct.instanceid, ct.path, ct.depth
FROM `mdl_role_capabilities` AS rc
INNER JOIN `mdl_context` AS ct ON rc.contextid = ct.id
INNER JOIN `mdl_course_categories` AS ccat ON ccat.id = ct.instanceid
AND `contextlevel` =40

and added it to the list:

http://docs.moodle.org/en/ad-hoc_contributed_reports#Permissions_Overides_on_Categories

Which is part of the Custom SQL report plugin

Thanks smile

 
Average of ratings:Useful (2)
C'est moi :-)
Re: Useful SQL Queries?
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Hi,

very nice smile

It's what i like by sharing : everybody can improve things smile

And you've done the work to include it in moodledocs (i won't have to do that), and took time to inform me, so everything is really OK  !

I've updated my query, on my site wink

Thanks

 
Average of ratings: -
Picture of Lavanya Manne
Re: Useful SQL Queries?
 

Hi Nadav Kavalerchik,

I had 3users in Manager role and now i want to assign each user to a category by going to a category and selecting assign roles, now we get two boxes as 'existing users' and 'potential users' . When i Click on add, i should get only manager role users inside potential box (i.e., 3users) but not entire users, I think we need to modify the query in admin/roles/lib.php around line 1095, I modified the query like

$sql = " FROM {user} WHERE $wherecondition AND id IN ( SELECT u.id FROM {role_assignments} r, {user} u WHERE r.contextid = :contextid AND u.id = r.userid AND r.roleid = :roleid)";

$order = ' ORDER BY lastname ASC, firstname ASC';

And its working and i got only manager role users at potential box, but the issue is when i add an user, it is adding to existing box and not hiding in the potential box after adding user.

 
Average of ratings: -
Picture of Tran Duy Loc
Re: Useful SQL Queries?
 

Can you help me the sql query to select all question that had been used in all quiz include their answer and their choices (for multiple choice question only).

Thanks.

 
Average of ratings: -
Picture of Juan Leyva
Re: Useful SQL Queries?
Group DevelopersGroup Moodle HQGroup Particularly helpful Moodlers

For displaying this queries as reports inside Moodle there are two contrib plugins:

Custom SQL queries -  By Tim Hunt

Configurable reports - Developed by me (uses some code of Tim Hunt's plugin)

 
Average of ratings:Useful (3)
Picture of Lavanya Manne
Re: Useful SQL Queries?
 

Hi Dale Davies,

I had 3users in Manager role and now i want to assign each user to a category by going to a category and selecting assign roles, now we get two boxes as 'existing users' and 'potential users' . When i Click on add, i should get only manager role users inside potential box (i.e., 3users) but not entire users, I think we need to modify the query in admin/roles/lib.php around line 1095, I modified the query like

$sql = " FROM {user} WHERE $wherecondition AND id IN ( SELECT u.id FROM {role_assignments} r, {user} u WHERE r.contextid = :contextid AND u.id = r.userid AND r.roleid = :roleid)";

$order = ' ORDER BY lastname ASC, firstname ASC';

And its working and i got only manager role users at potential box, but the issue is when i add an user, it is adding to existing box and not hiding in the potential box after adding user.

 
Average of ratings: -
Picture of Glen Keune
Re: Useful SQL Queries?
 

Hello thread watchers... are any of you SQL experts available for paid query development? I'm in need of a report that's beyond my expertise. Contact me directly if you're interested.

Glen

 
Average of ratings: -
Picture of Lavanya Manne
Re: Useful SQL Queries?
 

I am interested to work with SQL Queries. Let me know

 
Average of ratings: -
Picture of Chris Cannon
Re: Useful SQL Queries?
 

I'm looking to generate a report that pulls the department, Tota; # enrolled, & total # of completers : Month to date and Year to date

 
Average of ratings: -
Picture of Tony Box
Re: Useful SQL Queries?
 

Here's a simple one to find all information about instructors and the courses they are enrolled in:

SELECT mdl_course.id,  mdl_course.category, mdl_course.shortname, mdl_course.fullname, mdl_course.timecreated, mdl_course.timemodified, mdl_course_categories.id, mdl_course_categories.name, mdl_role_assignments.userid, mdl_role_assignments.roleid, mdl_user.firstname, mdl_user.lastname, mdl_user.email, mdl_user_enrolments.enrolid, mdl_user_enrolments.userid

FROM mdl_course, mdl_course_categories, mdl_role_assignments, mdl_user, mdl_user_enrolments

WHERE mdl_user.id = mdl_role_assignments.userid, mdl_user.id = mdl_user_enrolments.userid, mdl_course.category = mdl_course_categories.id

AND mdl_role_assignments.roleid = 3;

I used it to make a list of all faculty email addresses who were "teacher" roles in moodle. You could change the "roleid = 3" to whatever other roleid you want. roleid = 3 is what our teacher roleid is.

 
Average of ratings: -
Moodler
Re: Useful SQL Queries?
 

I just ran this query and thought I would share it. This query will list all students not enrolled in a course. 

SELECT 'u'.'id', 'u'.'username', 'u'.'firstname', 'u'.'lastname', 'u'.'email' FROM 'mdl_user' 'u' LEFT JOIN 'mdl_role_assignments 'r' ON 'r'.'userid' = 'u'.'id' WHERE 'r'.'id' IS NULL AND 'u'.'deleted' = 0

I ran this from phpmyadmin, SQL tab. I just copied and pasted the query into the "Run mysql query on database box.  I then exported the file to excel. 

 
Average of ratings: -
Moodler
Re: Useful SQL Queries?
 

Hello Dale, great queries. These will be very useful.  What about a query that listed all users not enrolled in a course? Our technical support told us not to delete user directly from the database because it will leave orphan files, but I could use a list of users not enrolled in a course and then delete them from within moodle. 

 
Average of ratings: -
Moodler
Re: Useful SQL Queries?
 

Here is a query that I received from our Technical Support. I have not run this query at this time.

 The following query can be run from phpmyadmin to produce a listing of all active students that do not currently have any enrollments appearing:

select u.id, u.username, u.firstname, u.lastname, u.email FROM mdl_user u where u.deleted = 0 and u.id not in (select userid from mdl_role_assignments)

 
Average of ratings: -
Picture of Hubert Chathi
Re: Useful SQL Queries?
 

Joins are generally faster than "in" queries -- that query rewritten using a join would be:

SELECT u.id, u.username, u.firstname, u.lastname, u.email FROM mdl_user u LEFT JOIN mdl_role_assignments ra ON ra.userid = u.id WHERE u.deleted = 0 AND ra.id IS NULL

You may also want to modify that query to only check for the student role, rather than checking all possible role assignments.  I would also add a "u.confirmed = 1" check to the query.

 
Average of ratings: -
Moodler
Re: Useful SQL Queries?
 

Thanks for the help Hubert. I will try this query also. 

 
Average of ratings: -
Picture of Alex Walker
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

In Moodle 2, the simple mdl_course_meta table is no more and metacourse links are handled as an enrolment plugin.

This query will return details of your parent and child courses. Note that I use 'parent' and 'child' in the traditional sense, (enrolments get pushed from parent down to the child) not the backwards Moodle sense.

select e.customint1 parentid, p.shortname parentcode, p.fullname parenttitle, e.courseid child, c.shortname childcode, c.fullname childtitle from mdl_enrol e inner join mdl_course p on p.id=e.customint1 inner join mdl_course c on c.id=e.courseid where enrol="meta";

 
Average of ratings: -
Picture of John Reese
Re: Useful SQL Queries? -list all students in any category
 

Hello,

I am running 2.2.2+ (latest release) and I would like to copy what was in the user query form for the ff. SQL query of:

List of all students (Course role is "Student") in any course from any category and Authentication is equal to "LDAP server"

I would like to delete these users from the database directly.

If I delete them this way, would it affect the enrolment table if there is missing user accounts (I don't think so.. just useless records).

Thanks

 

 

 

 
Average of ratings: -
Picture of John Reese
Re: Useful SQL Queries? -list all students in any category
 

Can anyone help me with this query?

thanks

 
Average of ratings: -
Rosario playing soccer
Re: Useful SQL Queries? -list all students in any category
Group Particularly helpful Moodlers

Only the queries I found in these threads that will help you:

Retrieve all COURSES in ONE CATEGORY:

This works for category 7 but does not retrieve all courses below it!

SELECT DISTINCT crs.category, crs.id, crs.fullname, crs.shortname, c.path
FROM mdl_course AS crs,  mdl_context AS c
WHERE c.contextlevel=50 And c.instanceid=crs.id and crs.category=7


The following crashed moodle.fhnw.ch, but not moodle.test.fhnw.ch, so pay attention with the % sign in starting positions:

SELECT DISTINCT crs.shortname, crs.category, crs.id, crs.fullname
FROM mdl_course AS crs, mdl_role_assignments AS ra, mdl_context AS c

WHERE c.contextlevel='50'  And ra.contextid=c.id
And c.path Like '%/1/6/'
ORDER BY crs.fullname;

 

Re: Query to show all students and his courses
by Peter Easton - Saturday, 10 November 2007, 01:47 AM

  Actually after i posted i did some reworking and came up with this mod for both faculty and students:
 
SELECT c.fullname, c.id, usr.firstname, usr.lastname, usr.id, usr.username, usr.email
FROM mdl_course c
INNER JOIN mdl_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
INNER JOIN mdl_role r ON ra.roleid = r.id
INNER JOIN mdl_user usr ON ra.userid = usr.id
WHERE r.name = 'Student'
AND usr.username = 'peaston'
ORDER BY c.fullname, usr.lastname

SELECT c.fullname, c.id, usr.firstname, usr.lastname, usr.id, usr.username, usr.email
FROM mdl_course c
INNER JOIN mdl_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
INNER JOIN mdl_role r ON ra.roleid = r.id
INNER JOIN mdl_user usr ON ra.userid = usr.id
WHERE r.name = 'Teacher'
AND usr.username = 'peaston'
ORDER BY c.fullname, usr.lastname

The same to find all Teachers in all courses from the above code:

SELECT usr.email, c.shortname
FROM mdltest_course c
INNER JOIN mdltest_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN mdltest_role_assignments ra ON cx.id = ra.contextid
INNER JOIN mdltest_role r ON ra.roleid = r.id
INNER JOIN mdltest_user usr ON ra.userid = usr.id
WHERE r.shortname = 'editingteacher'
ORDER BY usr.email

Rosario


 
Average of ratings: -
Picture of Sonya de Jager
Re: Useful SQL Queries?
 

Thanks for all the useful queries!


I wonder if anyone can help me with this one...

(I forgot to add - we're using moodle 1.9.6)


I've managed to display a list of all the assignment uploads for a specific course (id=93) with the following columns:
First Name (Student)
Last Name (Student)
Assignment Name
Deadline (timedue of Assignment)
UploadTime (Time that file was uploaded - from prefix_log)


Does anyone know if it's possible to include a column that shows the name of the file that was uploaded?
Here is the query (Excuse any mistakes - I'm just figuring it out as I go along.)


SELECT
prefix_user.firstname AS FirstName,
prefix_user.lastname AS LastName,
prefix_assignment.name AS Assignment,
DATE_FORMAT( FROM_UNIXTIME( prefix_assignment.timedue ) , '%Y %M %D %h:%i:%s' ) AS Deadline,
FROM_UNIXTIME(prefix_log.time,'%Y %M %D %h:%i:%s') AS UploadTime  
FROM prefix_log INNER
JOIN prefix_user ON prefix_user.id = prefix_log.userid
JOIN prefix_course_modules ON prefix_log.cmid = prefix_course_modules.id
JOIN prefix_assignment ON prefix_course_modules.instance = prefix_assignment.id
WHERE prefix_log.action = 'upload'
AND prefix_log.course=93
AND prefix_log.module = 'assignment'

 
Average of ratings: -
Picture of Brian LaMaster
Re: Useful SQL Queries?
 

This is awesome!

Okay, I have experience in AS/400 (IBM) SQL and I see some similarities in the syntax. Is there any “list” of databases that are affected by creating a new user? I am currently experiencing some issues with creating new users in Moodle 2.0.3 and I am getting an “invalid email address” error. My desire is to SQL the user info in the database(s) and try to “force” the data into the system until I can figure out a workaround.

Being new to Moodle, will inserting users cause some sort of system crash or individual user crash?

I would like to know the databases affected, required fields, and maybe a sample SQL statement to see if my thinking is correct. I have a couple of test environments setup up to try it out.

Thanks!

Brian

 
Average of ratings: -
Rosario playing soccer
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Sorry for being late. To read the database with queries ist ok, but to populate it is very very hazarduous and also difficult to achieve because of the many relations between the different tables. To add users you might study the built in uploaduser.php like I did to write my uploadusersandcourses.php and uploadusersandcourses_silently.php which means you use the Moodle-API to create users, courses, categories, etc. etc. and hence being very safe and ready for any upgrades that may come, as you wil not have to change the API calls nor the logic in your own php scripts.

Rosario

 
Average of ratings: -
Picture of Carlos Lara
Re: Useful SQL Queries?
 

Hi Sonya, 

Im looking to add similar functionality than the one you describe.

Im using moodle 1.9.6

I would like to compile all assignments_submissions for the current user and display the most content.

If its a file would love to display a link to the file, if its online text then display the actual contents of the assignment submission in the report. I am suprised very few people have requested this functionality. Could we just query the assignment_submissions table in the database ? 

I see there is a post about how to do it in moodle 2.0. Im using 1.9.6 like you.

Thank you 

Carlos Lara

 
Average of ratings: -
Linda as Rat Notting Hill Carnival
Re: Useful SQL Queries?
 

We're on Moodle 1.9.3 with MySQL I want to report on all users who have a completed a SCORM course with the username, the overall score they got and the completion date. Can anyone suggest SQL for this? Or give ideas? I'm very familiar with SQL but not with the moodle database structure. I need these tables - perhaps others?

    scorm_scoes_track

    scorm

    course c

thanks

Linda

 
Average of ratings: -
Rosario playing soccer
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Sorry for being late.

>>Does anyone know if it's possible to include a column that shows the name of the file that was uploaded?

I found an attachment column in the mdl_forum_posts table. So there might be the same column also in the other activities that allow students to upload files.

Rosario

 
Average of ratings: -
Picture of William Mair
Re: Useful SQL Queries?
 

I was wondering if this is the best place for me to ask for a query to be written, that I can't seem to find elsewhere and I'm not sure how to write it.

Basically, I am using Juan Leyva's Configurable Reports block and wanting to have the option for lecturers to be able to view a report within each course that would tell them the activity count on each resource within the course (to basically see what activities/resources are accessed the most).

Would anyone be able to help me with this? Is this already out there and I just can't find it?

Thanks in advance

 
Average of ratings: -
Me at the Moodle Moot NZ11
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Hi William

This functionality already exists as standard.

When logged in as a Teacher...

Navigation (Block) > My courses > 'course name' > Reports > Activity report

Here you can see every item in the course, how many times it has been accessed, and the last time it was accessed.  All items are hyperlinked too - whch is great smile

Many teachers have no idea this Report exists - I'm really not sure why as it's so useful !

Stu

 
Average of ratings: -
Picture of William Mair
Re: Useful SQL Queries?
 

I knew that was there, but for some reason wasn't quite clicking that it was telling me how many times each thing has been accessed.

My god, I feel like an idiot blush

Thanks for pointing out the obiovus to me wink

 
Average of ratings: -
Picture of William Mair
Re: Useful SQL Queries?
 

The report shows activity since the course was created, with no way to specify dates/since the start of the year (the course wasn't reset at the beginning of this year), so I was looking for a way to use the Configurable Reports to give the same information for definable date ranges.

 
Average of ratings: -
Picture of Alejandro Cemylto
Re: Useful SQL Queries?
 

Hi Everyone and please someone help me here, it's not hard but i am not good at sql i use this report to show me the people enrolled in courses and i can filter by month
_________________________________________________________________
SELECT co.fullname as 'Course', u.username as 'Emp ID', u.country as 'Location', u.firstname, u.lastname, FROM_UNIXTIME(ra.timemodified,'%D %M %h:%i:%s %x') AS enrol_time FROM prefix_role_assignments ra,
prefix_user u, prefix_context c, prefix_course co
WHERE ra.contextid = c.id AND c.contextlevel = 50 AND ra.userid = u.id AND c.instanceid = co.id AND ra.timemodified >0 AND u.deleted = 0

%%FILTER_COURSES:co.id%% %%FILTER_STARTTIME:ra.timemodified:>%% %%FILTER_ENDTIME:ra.timemodified:<%% %%FILTER_USERS:u.country%% _________________________________________________________________
All i need is to add the coumn that shows their final grades that's it

Thanks Guys!

 
Average of ratings: -
Picture of David Richter
Re: Useful SQL Queries?
 

Thanks to all for the great SQL contributions, they are really helpful to someone who is not that strong in SQL (like me)!

I am running Moodle 2.2.1 and have installed the ad-hoc database query plugin. I was looking over the contributed SQL queries and found one that I was particularly interested in:

How many LOGINs per user and user's Activity

+ link username to a user activity graph report

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') AS Username
,count(*) AS logins
,(SELECT count(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) AS Activity
FROM prefix_log AS l JOIN prefix_user AS u ON l.userid = u.id
WHERE `action` LIKE '%login%' GROUP BY userid
ORDER BY Activity DESC

When I run the query, I receive the error:

ERROR: Incorrect number of query parameters. Expected 1, got 0.

Does someone know the source of the error?

Thanks in advance!

 
Average of ratings: -
Picture of mohammed adeeb
Ad-hoc-Report
 

very argent

i won't retrieve all marks for individual student 

thx alot

 
Average of ratings: -
Picture of Jefferson Bien-Aime
Re: Useful SQL Queries?
 

Same thing for me too. Please Can I get a help. Thanks

 
Average of ratings: -
Picture of Jefferson Bien-Aime
Re: Useful SQL Queries?
 

I modify the sql statement.

 

I refer to this forum : http://moodle.org/mod/forum/discuss.php?d=136484&parent=848817

 

The new one is this :

SELECT concat('<a target="_new" href="%%USERID%%', u.id, '&mode=alllogs">', u.firstname ,' ', u.lastname,'</a>') AS Username
,count(*) AS logins
,(SELECT count(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) AS Activity
FROM prefix_log AS l JOIN prefix_user AS u ON l.userid = u.id
WHERE `action` LIKE '%login%' GROUP BY userid
ORDER BY Activity DESC

 

and I get this result in attachment.

 

My moodle site will be implement in French. So you'll find some french word on it.


 
Average of ratings: -
Picture of Serge Baric
Re: Useful SQL Queries?
 

Hey guys, 

A very useful thread. 

I've just installed the ad-hoc database query block, and want to run a simple (i hope) query, where I get a total daily number of submitted assignments. 

 

I know there is the weekly/monthly quiz count query on here, but with my poor php skills, I cannot come even close to getting it do to exactly what I want. Any suggestions? (actual script would be ideal). 

 

Thanks in advance

 
Average of ratings: -
Picture of Graham Courtney
Re: Useful SQL Queries?
 

Can anyone help me with a SQL query that simply creates a report of new authenticated users in a given timeframe? No idea how to write this. Thanks in advance.

 
Average of ratings: -
ole djurhuus
Ang: Useful SQL Queries?
 

HI there,

Hope you can helpsmile

We are running a 2.2.3+ site

We would like to get a list of newly registrated and confirmed users the last month on our Moodle site sorted by country. Showing name, email etc. We use the email-based self-registration.

Any idea for a sql statement? - which tables to query?

Kind regards,

Ole

 
Average of ratings: -
Picture of Tomasz Muras
Re: Ang: Useful SQL Queries?
Group DevelopersGroup Particularly helpful MoodlersGroup Translators

It looks like you just need to query one table - mdl_user. All the information you need should be there.

cheers,
Tomek

 
Average of ratings: -
Picture of George Bosveld
Re: Useful SQL Queries?
 

Hi everyone, I hope someone can help my with some SQL queries

It's been a few years since my basic queries but here goes:

I want to have 2 overviews of first time scorm training starts

1 overview stating which student did which training for the first time at the registered time

and

1 overview stating each month how many first time launches have been for each training.

 

The one for each user I think I got it working (except I need to add a WHERE saying the role needs to be student), here's that code:

---------------------------------

SELECT DATE_FORMAT( FROM_UNIXTIME( sst.timemodified ) , '%Y/%m/%d %H:%i:%S' ) AS date, CONCAT(u.firstname,' ',u.lastname) AS student, c.fullname AS course, s.name AS ScormName, s.reference AS ScormPackage

FROM prefix_scorm_scoes_track sst
JOIN prefix_scorm s ON s.id = sst.scormid
JOIN prefix_course c ON c.id = s.course
JOIN prefix_user u ON sst.userid = u.id

WHERE sst.element = 'x.start.time'

GROUP BY CONCAT(u.firstname,' ',u.lastname), c.fullname, s.name, s.reference
ORDER BY sst.timemodified DESC

---------------------------------

 

The other one I don't get the amount of starts counted well, it looks like the whole amount of counted, not in the shown month:

---------------------------------

SELECT DISTINCT YEAR( FROM_UNIXTIME( sst.timemodified )) AS year, MONTH( FROM_UNIXTIME( sst.timemodified )) AS month, c.fullname AS course, s.name AS ScormName, s.reference AS ScormPackage, (SELECT COUNT(DISTINCT userid) FROM prefix_scorm_scoes_track WHERE sst.element = 'x.start.time') AS amount

FROM prefix_scorm_scoes_track sst
JOIN prefix_scorm s ON s.id = sst.scormid
JOIN prefix_course c ON c.id = s.course
JOIN prefix_user u ON sst.userid = u.id

GROUP BY u.id, c.fullname, s.name, s.reference

ORDER BY sst.timemodified DESC

---------------------------------

 

In hope someone sees the flaw and the fix smile I think somehow in the count I need to add the chosen date but not sure how to do that

 
Average of ratings: -
Picture of vasanth naidu
Please suggest if i done any mistakes in these queris
 

Here is the my queries for latest registered by user, like: Disscussion, events, files, user registered etc. to show on my home page "Please suggest if i done any mistakes in these queris"

1 //this query shows that latest last registered user info

SELECT username, firstname, lastname, email, country, FROM_UNIXTIME(timecreated,'%M %d %Y'),
IF(DATEDIFF(NOW(), FROM_UNIXTIME(timecreated))<=30,'Y','N') AS AccountCreatedInPast30Days
FROM mdl_user WHERE confirmed = 1 ORDER BY id DESC LIMIT 1

2.//this query shows that latest last Discussion info
SELECT fp.id,
           usr.id AS UserID,
           usr.firstname,
           usr.lastname,
           c.id AS CourseID,
           fg.name AS GroupName,
           c.fullname,
           fd.name,
           DATE_FORMAT( FROM_UNIXTIME( fd.timemodified ) , '%d/%m/%Y' ) AS  DiscussionCreatedOn,
           DATE_FORMAT( FROM_UNIXTIME( fp.created ) , '%d/%m/%Y' ) AS TimeofPost,
           DATE_FORMAT( FROM_UNIXTIME( fp.modified ) , '%d/%m/%Y' ) AS Modified,
           fp.subject,
           fp.message
FROM mdl_forum_posts fp, mdl_forum_discussions fd, mdl_forum f, mdl_course c, mdl_user usr, mdl_groups fg
WHERE fp.discussion = fd.id AND f.id = fd.forum AND f.course = c.id AND fp.userid = usr.id AND fd.groupid = fg.id
ORDER BY usr.id DESC LIMIT 1


3.//this query shows that latest last event info
SELECT DISTINCT e.id ,e.name, usr.firstname, usr.lastname, e.eventtype , DATE_FORMAT( FROM_UNIXTIME( e.timestart ) , '%d/%m/%Y' ) AS  TimeStarted  
FROM mdl_event e, mdl_user usr, mdl_course c, mdl_groups fg
WHERE e.courseid=c.id AND e.groupid=fg.id AND e.userid=usr.id ORDER BY e.id DESC LIMIT 1



4.//this query shows that latest last file uploaded info
SELECT DISTINCT f.id, r.name, f.filename, DATE_FORMAT( FROM_UNIXTIME( f.timecreated ) , '%d/%m/%Y' ) AS  TimeStarted
FROM mdl_resource r, mdl_files f, mdl_user usr, mdl_course c
WHERE c.id=r.course AND usr.id=f.userid ORDER BY f.id DESC LIMIT 1

 

Thanks!

P Vasanth naidu

 
Average of ratings:Useful (1)
Picture of Manuel Gogl
Re: Useful SQL Queries?
 

Hey guys!

 

I'm looking for a quere that brings up the count of all enrolled courses of each users in the db. This quere yould help to identify the best navigation course limit (in  Administration > Appearance > Navigation).

Finaly it sould look like this:

  • USER | # of courses enrolled
  • U1 | 24
  • U2 | 7
  • ....

I'm no pro in sql so maybe somewone can help solving my request.

 

Cheers,

Manuel

 
Average of ratings: -
Picture of Trevor Furtado Souza
Re: Useful SQL Queries?
 
i believe this is what you need: SELECT u.id, u.firstname, u.lastname, count(*) FROM mdl_user_enrolments AS ue JOIN mdl_enrol AS e ON ue.enrolid = e.id JOIN mdl_user AS u ON ue.userid = u.id GROUP BY u.id ORDER BY count(*) DESC Please, confirm the result, i'm not secure about it.
 
Average of ratings:Useful (3)
Picture of Manuel Gogl
Re: Useful SQL Queries?
 

Perfect! Thanks a lot!

 
Average of ratings: -
Picture of Manuel Gogl
Re: Useful SQL Queries?
 

Hey Trevor!

I'm also looking for a statement that checks the number of registered manager roles per user analog to the statement that you gave me last time.

  • USER | # of courses enrolled | # of registered manager roles
  • U1 | 24 | 21
  • U2 | 7 | 34
  • ....

Maybe you can help me once again.

Thanks, Manuel

 
Average of ratings: -
Picture of John Reese
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
 

Hello folks,


We are running moodle 2.2.4+ and need help in trying to figure out where I could find any links to either youtube, vimeo, etc. which were put /embedded on a course page.

Can't seem to find the table or tables where I can search for the pattern.

Thanks

John

 
Average of ratings: -
Picture of Richard Havinga
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
 
Hi John,

You need to look in several places. I have done this in my GSB set so you need to look at the following tables:

label and the column intro;
page and the column content;
book_chapters and the column content

and search for the embed and iframe should give you a good result
 
Average of ratings: -
Picture of John Reese
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
 

thank you Richard... I'll do a query for the urls and let you know how it goes

 
Average of ratings: -
Picture of Joseph Rézeau
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
Group DevelopersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Richard "I have done this in my GSB set..."

GSB = http://acronyms.thefreedictionary.com/GSB

Which one do you mean?

Joseph

 
Average of ratings: -
Picture of Richard Havinga
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
 
Gold, Silver Bronze Benchmarking set in the plugins database.
 
Average of ratings:Useful (1)
Picture of shivram gawde
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
 

Please can any one tell me what should be the query to check if user has viewed the course and one for users who have completed the course

 
Average of ratings: -
Picture of prakash mishra
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
 

i have problem regarding moodle query .. my sql query is to find the list of course have enrolled by a user the sql query executing properly in sqlyog but on moodle only one course is print in array  not showing all course enrolled by user ...

pls  help..........

 
Average of ratings: -
Picture of Jitendra Gaur
Re: Useful SQL Queries? - find links to youtube, vimeo, etc.
 

Can you tell me what is the code you are using i think you should use $DB->get_records_sql() to get all the records dont use $DB->get_record_sql()

 
Average of ratings: -
Picture of m chico
Re: Useful SQL Queries?
 

hi

 

I'm not good at all in Sql so i was wondering if someone could give me a hand..

 

I try to have 2 reprots, the first I think is easy, the second I think is impossible but I don't loose anything by asking:

 

1.- In the first report, I would love to get the results for a certain course of the following

  • the user name
  • the first access to the site
  • the first access to the course
  • the last access
  • ordered by first access

2.- In this other report I would to get the results of all the students for all the lessons of a scorm course, that is that instead of having to go to see the details of the scorm for each student, is there anyway I can get all the details of all the scorm lessons per student in each row ?

You are free to tell me if I'm askign a stupid question... that's how ignorant I am in sql mixed

 

 

 
Average of ratings: -
Sketch...
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

"the last access" to what? A Course or the Site?

Once you define that try this MS Transact query for your first question:

SELECT
    mdl_user.username,
    mdl_user.firstname,
    mdl_user.lastname,
    mdl_user.firstaccess AS first_accessed_site,
    mdl_user.lastaccess AS last_accessed_site,
    mdl_course.id AS course_id,
    mdl_course.fullname AS course_full_name,
    mdl_user_lastaccess.timeaccess AS last_accessed_course
FROM
    mdl_course
    INNER JOIN mdl_course_categories ON mdl_course.category = mdl_course_categories.id
    INNER JOIN mdl_user_lastaccess ON mdl_course.id = mdl_user_lastaccess.id
    INNER JOIN mdl_user ON mdl_user_lastaccess.userid = mdl_user.id
WHERE
    mdl_course.id = 'YOUR_COURSE_ID'

You can use "mdl_course.fullname" instead of "mdl_course.id" in the WHERE clause - but using "mdl_course.id" will guarantee you a unique value (whereas you could have multiple courses with the same mdl_course.fullname).

Your second question isn't as clear. Please provide more details.

 
Average of ratings: -
Picture of m chico
Re: Useful SQL Queries?
 

Sorry Luis, you are right, I forgot. I meant the last access to the course

 

thanks for your help!!

 
Average of ratings: -
Picture of m chico
Re: Useful SQL Queries?
 

Hi Luis

 

I tried your query:

SELECT
user.firstname AS Nombre,
user.lastname AS Apellido,
DATE_FORMAT(FROM_UNIXTIME(user.firstaccess), '%d/%m/%Y') AS fecha_entrada,
course.fullname AS Curso,
course.id AS codigo,
DATE_FORMAT(FROM_UNIXTIME(user.lastaccess), '%d/%m/%Y') AS ultimo_acceso,
user_lastaccess.timeaccess AS last_accessed_course
   FROM
prefix_user AS user,
prefix_course AS course,
    INNER JOIN prefix_course_categories ON prefix_course.category = prefix_course_categories.id
    INNER JOIN prefix_user_lastaccess ON prefix_course.id = prefix_user_lastaccess.id
    INNER JOIN prefix_user ON prefix_user_lastaccess.userid = prefix_user.id
WHERE
    prefix_course.id = 60


and it give me an error:

Query SQLDebe suministrar un valor aquí.

Error when executing the query: 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 'INNER JOIN mdl_course_categories ON mdl_course.category = mdl_course_categories.' at line 1

 

I'm lost...

 
Average of ratings: -
Sketch...
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Your table prefixes seeem to be missing in a few places. How about: 

SELECT
    mdl_user.firstname AS Nombre,
    mdl_user.lastname AS Apellido,
    DATE_FORMAT(FROM_UNIXTIME(mdl_user.firstaccess), '%d/%m/%Y') AS fecha_entrada,
    mdl_course.fullname AS Curso,
    mdl_course.id AS codigo,
    DATE_FORMAT(FROM_UNIXTIME(mdl_user.firstaccess), '%d/%m/%Y') AS ultimo_acceso,
    mdl_user_lastaccess.timeaccess AS last_accessed_course
FROM
    mdl_course
    INNER JOIN mdl_course_categories ON mdl_course.category = mdl_course_categories.id
    INNER JOIN mdl_user_lastaccess ON mdl_course.id = mdl_user_lastaccess.id
    INNER JOIN mdl_user ON mdl_user_lastaccess.userid = mdl_user.id
WHERE
    mdl_course.id = '60'

Assuming that your table prefix is "mdl"...

 
Average of ratings: -
Picture of m chico
Re: Useful SQL Queries?
 

I copied your query but it says "This query did not return any data."

 

the course exists but I have also tried to change the course id and it doesn't return any data with any course

 
Average of ratings: -
Picture of m chico
Re: Useful SQL Queries?
 

I FOUND THE ERROR!!

it was in one of the inner joins

INNER JOIN mdl_user_lastaccess ON mdl_course.id = mdl_user_lastaccess.id

should say INNER JOIN mdl_user_lastaccess ON mdl_course.id = mdl_user_lastaccess.courseid

 

thank you Luis

 
Average of ratings: -
Picture of Hittesh Ahuja
Re: Useful SQL Queries?
 

im working on a query that would get me all the users on all courses for all assingments who have either submitted or not submitted. No matter what way i try, i only seem to get submitted assignment s .I feel i am really close but dont know what I am missing .Here is the SQL

 

SELECT
IF(a.timedue,DATE_FORMAT(FROM_UNIXTIME(a.timedue), '%d-%m-%Y'),
NULL) AS 'Assignment Due Date',
as_sub.id,
IF(as_sub.timemodified,DATE_FORMAT(FROM_UNIXTIME(as_sub.timemodified),
'%d-%m-%Y'),NULL) AS 'Last Modified',
cat.name AS 'Category Name',
c.fullname AS 'Course Full Name',
c.idnumber AS 'Course ID Number',
a.name AS 'Assignment Name',
u.firstname,u.lastname
FROM mdl5_user u
LEFT JOIN
(SELECT DISTINCT(u.id),e.courseid FROM mdl5_user u
JOIN mdl5_role_assignments ra
ON (ra.userid = u.id AND ra.roleid = 5 -- Only Students
)
left JOIN mdl5_user_enrolments ue
ON ue.userid = u.id
JOIN mdl5_enrol e
ON e.id = ue.enrolid WHERE u.deleted = 0 ) u2
ON u2.id = u.id
left JOIN
mdl5_assignment_submissions as_sub
ON u.id = as_sub.userid 
left JOIN mdl5_assignment a ON a.id = as_sub.assignment
JOIN mdl5_course c ON c.id = a.course
JOIN mdl5_course_categories cat
ON cat.id = c.category
WHERE u.deleted = 0
GROUP BY as_sub.id
ORDER BY a.timedue DESC

How to cross check ? 

I take a course, look for submitted assingments for an assingment in that course, filter the page to view all and then look for the users in my resultset who have not submitted an assingment/show NULL besides their name fields as I believe a LEFT JOIN would do its magic.

 
Average of ratings: -
this is joserey
Re: Useful SQL Queries?
 

hi all,

Can you help me with a simple query to list the students who have actually enrolled in only one course? We are not an academic institution and all our courses are free. While there are a lot who have taken multiple courses,  we would like to call and get feedback on those who have taken only one course. Thanks.

 
Average of ratings: -
Davo
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

JoseRey - off the top of my head:

SELECT u.id, u.firstname, u.lastname, u.email
FROM mdl_user u
JOIN (SELECT userid, COUNT(*) c
FROM mdl_user_enrolments
GROUP BY userid)  ue ON ue.userid = u.id
WHERE ue.c = 1

Probably some typos in there and I haven't got access to a database to try it right now, but should be a good starting point.

 
Average of ratings: -
this is joserey
Re: Useful SQL Queries?
 

Thanks Davo for the reply.

But I can't find the mdl_user_enrolments table. Oh I'm using Moodle 1.9. Should it be there? Or what should I be looking for instead?

 
Average of ratings: -
Davo
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers
The query I gave was for Moodle 2.x, for Moodle 1.9 you would have to look at the role_assignments table. Note, it is always helpful to state your Moodle version when asking questions in these forums, especially if you are using an old, unsupported version of the software.
 
Average of ratings: -
this is joserey
Re: Useful SQL Queries?
 

Thanks Davo. Sorry I missed stating my Moodle version the first time. I think I may have found what I'm looking for in that role_assignments table.

 
Average of ratings: -
Picture of Judy Hsu
Re: Useful SQL Queries?
 

This is a wonderful thread. Thanks for all the discussion!

Just a quick question. Is it possible to write a SQL query to query on how installed language packs are being used by any courses or any users? We initially installed a lot of language packs but seem that most of them were not used by any users, so we would like to remove some of them. Thanks!

 
Average of ratings: -
Randy Thornton
Re: Useful SQL Queries?
Group Documentation writersGroup Particularly helpful Moodlers

Judy,

Here's a simple one to show language packs used in courses:

SELECT c.shortname, c.lang FROM prefix_course AS c WHERE c.lang <> ' '

That's a single blank space there in that <> ' ' which means the course has no forced language and so uses the system default. If it has a forced language, It returns the language code (e.g. es, en_us).

You will find another one-liner for user language settings already over in the 2.5 ad hoc reports - http://docs.moodle.org/25/en/ad-hoc_contributed_reports#List_of_users_with_language

 

 

 

 

 

 
Average of ratings: -
Picture of Geoff Wild
Re: Useful SQL Queries?
 

Here's one that I have tweaked from the ad-hoc reports. This was made to run with Moodle 2.4. It is really simple, so feel free to improve on it. 

It counts the number of distinct logins from NOW() until a specified number of days back. You can specify the roleid for Teacher(3) or Student(5) (at least on our system, check what the role ids are for your system to make sure they what you are looking for):

SELECT COUNT(DISTINCT u.id) AS Logins

FROM prefix_user u
INNER JOIN prefix_role_assignments ra ON u.id = ra.userid

WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 90
# ^ Change 90 to desired # of days ^ #
AND ra.roleid = 3
# ^ 3 is for Teacher, 5 is for student ^ #

ORDER BY Logins

I've got a very basic understanding of SQL, so it may not be the best, but it is serving my purposes. What I would like to see this expanded with, if possible, specifying a start date and an end date. This would help me get some historical data that would be very useful.

I also realize that the ORDER BY is unnecessary, but my admin reports scream at me when I have comments as my last or first line. We are using Moodlerooms at our institution.

 
Average of ratings:Useful (2)
Me at the Moodle Moot NZ11
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Hi Geoff - nice quick query - thanks for sharing !

I'm sure our online MoodleBites MySQL Reporting in Moodle course (outline here) is too basic for you, but I might use your example as a starting point for one of our lessons if that's OK ?!

Stu smile

PS - how wonderful to see someone sharing queries with comments in as well - awesome !

 
Average of ratings: -
Picture of Geoff Wild
Re: Useful SQL Queries?
 

Stu,

Yes, feel free to use that query in your course. Just as a reminder, I got the code from here. I just added some of my own stuff to it. Glad to hear the comments are helpful too!

Thanks,

Geoff

 
Average of ratings: -
Picture of Geoff Wild
Re: Useful SQL Queries?
 

Here's another one that I've been playing around with. I've been looking at the Moodle Mobile App, and I was trying to figure out what I can use to tell me someone is accessing our site through Moodle Mobile. This is just a count, but you should be able to use the Select from within the main From to get more specific details. Take out the distinct if you want to see all access and not just unique login ids.

SELECT COUNT(*) AS "Number of users accessing"

FROM
(
SELECT DISTINCT
l.action,
u.username AS Login_ID,
CONCAT(u.firstname, ' ', u.lastname) AS User

FROM prefix_log l
JOIN prefix_user u ON l.userid = u.id

#Uncomment the next line if you would like to specify the number of days
#WHERE DATEDIFF(NOW(), FROM_UNIXTIME(time)) < 180
# 'core_get_component_strings' is what is registered in the logs when someone
# launches the Moodle Mobile App
AND action = 'core_get_component_strings'

ORDER BY Login_ID ASC
) AS accessed

If there is something that I'm off about, please let me know. If someone has a better way of getting at that data, I'm very open to hearing it! I also have a section of code to limit by number of days from the current date. If the AND by itself isn't working for you (worked for my site), just change the AND action to WHERE action. It will correct that problem. Once again, this is for Moodle 2.4

 
Average of ratings: -
Picture of Damien Wendlinger
Re: Useful SQL Queries?
 

Hi all !

I'm trying to make a query (for the report_customsql plugin) where I could retieve the list of the best quiz attempt for every student that have completed the quiz. The settings we have for quiz is unlimited attempts, grade based on best attempt. I'm obviously *not* SQL-black-belted, but tried this query (which won't work as is) :

SELECT u.username, u.lastname, qa.id, DATE_FORMAT(FROM_UNIXTIME(qa.timefinish), '%d/%m/%Y') AS dt_Quiz
FROM prefix_quiz_attempts AS qa
JOIN prefix_user as u
WHERE u.deleted = 0 AND qa.state = 'finished' AND qa.timefinish > '1382601600' AND (qa.timefinish = MAX(qa.timefinish))

The qa.timefinish > '1382601600' is also mandatory here. The query works when removing the last "AND" statement.

I guess I need some nested SELECT, but I don't succeed in managing them..

Could anyone help me with this point (we use Moodle 2.5.1) ?

Thanks in advance !

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Useful SQL Queries?
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

Why not just use the quiz reports?

If you have a good reason for hacking this yourself, you can at least see what SQL the quiz reports use, and copy that.

 
Average of ratings: -
Picture of Malaiarasan jayaraj
Re: Useful SQL Queries?
 

This is what I use on the command line, but it should work in sql, phpmyadmin

SELECT c.fullname, u.firstname, u.lastname, u.email, u.id FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id Where cx.contextlevel = '50';

that will give you what students are enrolled in which courses

 
Average of ratings: -
Me at the Moodle Moot NZ11
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Thanks for sharing Malaiarasan - nice simple query but useful smile

For anyone that wants to use this with the Ad-hoc queries plugin by Tim it needs a couple of tweaks - no semicolon at the end, and remove the table name prefixes and enclose table names in braces, so it looks like this:

SELECT c.fullname, u.firstname, u.lastname, u.email, u.id FROM {course} c LEFT OUTER JOIN {context} cx ON c.id = cx.instanceid LEFT OUTER JOIN {role_assignments} ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN {user} u ON ra.userid = u.id Where cx.contextlevel = '50'

Stu smile

 
Average of ratings: -
Augusto Weiand
Re: Useful SQL Queries?
 

Hello,
I'm needing to create a query that returns me a totalizer records daily chat .... had a some time and now it does not, can anyone help? Below is my this not working ....

SELECT FROM_UNIXTIME(mcm.timestamp) AS data, count(mcm.id) AS atendimentos
     FROM mdl_chat_messages mcm
     INNER JOIN mdl_chat mc ON (mc.id = mcm.chatid)
WHERE
     mc.course = 106 AND FROM_UNIXTIME(mcm.timestamp) BETWEEN '2010-01-01 00:00:01' AND '2013-12-10 23:59:59'
GROUP BY DAY(DATE(FROM_UNIXTIME(mcm.timestamp)))
ORDER BY FROM_UNIXTIME(mcm.timestamp) ASC

 

Thank's!!

 
Average of ratings: -
Picture of Javi Ac
Re: Useful SQL Queries?
 

Hello,

I am trying to build a sql to get, for every row (student in a course), several columns (time spent in a module of the course, and result in evaluation of each module), and so on for every module of the course.

I have not found something similar. Does anybody could help me? I dont know very well the moodle database table structure.

Thanks.

 
Average of ratings: -
Picture of Blair F.
Re: Useful SQL Queries?
 

I'm using the ad-hoc report plugin and I'm trying to turn the following line into one that accepts user input for the number of days.  I've tried using SET to create a variable at the beginning of the code, but I get an error.

WHERE from_unixtime(timeaccess)>CURDATE() - interval 7 DAY

I've searched and searched for a way of doing this, but cannot find it, so I'm breaking down and asking for assistance.  Does anyone know how I can do this?  It's probably so simple, I'm going to slap myself.

Thanks.

 

 

 
Average of ratings: -
Picture of Bonnie Mioduchoski
Re: Useful SQL Queries?
 

Hi Dale,

Thanks for sharing all of these queries. I wonder if you can help me? I'd like to use the configurable reports block to see users (first name, last name & email) and all the courses they're currently enrolled in. So far I can only see one of their courses. I'm doing this so I can see if anyone has been enrolled into two courses of the same class accidentally. I tried to duplicate the course part but I am not knowledgeable in mySQL. Here's what I have: 

SELECT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
course.fullname AS Course
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) AS ROLE
,(SELECT name FROM prefix_role WHERE id=en.roleid) AS RoleName
 
FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS user2 ON ue.userid = user2.id

I appreciate any help you can give. Thanks!!

 
Average of ratings: -
Picture of KS Pratap
Re: Useful SQL Queries?
 

hi Dale Davies,

if we able to find the total assignments,forums , quizzes and resources in single course by using single query .

please help me

 

 
Average of ratings: -
TBC
Re: Useful SQL Queries?
 

Dear All,

 

Is there anybody know how to generate the course log report via SQL to looks like the same as the Moodle Log Report which exported to Excel?

 

Thanks in advance.

 

Best regards,

 

Ahmed

 
Average of ratings: -
TBC
Re: Useful SQL Queries?
 

Hi All,

 

I found it... and I would like to share it with you.

This SQL Statement is generating Moodle course log report just like the exported Excel Moodle Log:

 

SELECT c.fullname AS CourseName, from_unixtime(l.time) AS DateandTime,u.username Username,
l.ip IPAddress, u.firstname FirstName, u.lastname LastName, u.email Email, l.module Activity,
l.action Action, l.url URL, rc.name AS Information, l.userid AS UserID

FROM {log} l LEFT JOIN {user} u ON l.userid = u.id
LEFT JOIN {resource} rc ON rc.id = l.info
LEFT JOIN {course} c ON c.id = l.course

WHERE c.id =<YOUR_COURSE_ID>

GROUP BY u.username, l.time order by l.time DESC;

 

Thanks.

 

Ahmed

 
Average of ratings:Useful (2)
Me at the Moodle Moot NZ11
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Just to let people who find this forum know about our next run of our specialist "MySQL Reporting in Moodle" online course. You can read details here:

http://www.moodlebites.com/mod/page/view.php?id=9112

The course is suitable for beginners who haven't created queries before, and for people who are starting to want to do this.

If you are already happy creating MySQL queries to do what you need then this course will be too basic for you.

We only run this course twice a year, and the next start date is 1st April.

We give a 10% discount to anyone who find the course from this post - just mention this when you register smile

Stu

 
Average of ratings: -
Picture of Chirag Patel
Re: Useful SQL Queries?
 

Guys this query is for finding all courses in which user is teacher

 

select cu.fullname from mdl_course cu
join (select u.id as userid,u.firstname as name , mrs.contextid , mrs.roleid, c.instanceid from mdl_user u
join mdl_role_assignments mrs on mrs.userid=u.id
join mdl_context c on c.id=mrs.contextid
where u.id=13 and mrs.roleid=3 and c.contextlevel=50) as part on part.instanceid=cu.id

You just need to change u.id="Userid"!!!

 
Average of ratings: -
Go Beavers!
Re: Useful SQL Queries?
 

That's useful.  How about adding some code that would create a hyperlink back to the course?

 
Average of ratings: -
Picture of Dale Davies
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Wow I cannot believe it has been nearly 4 years since I started this thread and it is still going!!

I've been looking into using the sessions table to give an indication of the average session length. This query can be used if you implement DB sessions, I have applied a lower bound of 15 seconds (an arbitrary value) and an upper bound of the system session time out value. I've only tested this on Moodle 2.4 so far..

SELECT
	TIME_FORMAT(SEC_TO_TIME(ROUND(AVG(sessions.diff))),'%Hh %im') AS "Avg. Session Length"
FROM 
	(
		SELECT 
			id,
			userid,
			FROM_UNIXTIME(timecreated) created,
			FROM_UNIXTIME(timemodified) modified,
			TIMESTAMPDIFF(SECOND,FROM_UNIXTIME(timecreated),FROM_UNIXTIME(timemodified)) diff
		FROM 
			mdl_sessions
		WHERE 
			userid != 0 AND
			TIMESTAMPDIFF(SECOND,FROM_UNIXTIME(timecreated),FROM_UNIXTIME(timemodified)) > 15
	) sessions
LEFT JOIN 
	(
		SELECT 
			id,
			userid,
			FROM_UNIXTIME(timecreated) created,
			FROM_UNIXTIME(timemodified) modified,
			TIMESTAMPDIFF(SECOND,FROM_UNIXTIME(timecreated),FROM_UNIXTIME(timemodified)) diff
		FROM 
			mdl_sessions
		WHERE 
			userid != 0 AND
			TIMESTAMPDIFF(SECOND,FROM_UNIXTIME(timecreated),FROM_UNIXTIME(timemodified)) > (SELECT value FROM mdl_config WHERE name = 'sessiontimeout')
	) maxes ON sessions.id = maxes.id
WHERE maxes.id IS NULL
ORDER BY 
	sessions.diff DESC

 

As with all my stuff here, your feedback is much appreciated smile

 
Average of ratings:Useful (3)
Picture of Federico Fede
Re: Useful SQL Queries?
 

Hello everyone smile

Hope you all are doing great!

Hope someone can help me, I want to write a query that allows me to get all the users that have unfinished quizzes: the user id or the user name, the name of the quiz and the course's name.

And I am lieaving this query that shows all the quiz grades form a certain user:

select c.fullname, g.grade, q.name

from mdl_course c
left join mdl_quiz q on q.course = c.id
left join mdl_quiz_grades g on g.quiz = q.id
where g.userid=490
order by c.fullname, q.name;

 

thanks again smile

 
Average of ratings: -