General developer forum

Useful SQL Queries?

 
 
Picture of Dale Davies
Useful SQL Queries?
 
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 (8)
Picture of Dale Davies
Re: Useful SQL Queries?
 
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:Useful (2)
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 (4)
Picture of Dale Davies
Re: Useful SQL Queries?
 
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: -
Health & Safety Training Ninja
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?
 
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?
 
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:Useful (1)
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 Developers

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?
 

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 Developers

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 Chris S
Re: Useful SQL Queries?
 

Wen,

This is a great report.  Is there a way to list each Discussion Forum in the course so we can find out how many posts are made by teacher and student?  This will help me evaluate my faculty a bit better.

Thanks, Chris

 
Average of ratings: -
Picture of Susan Mangan
Re: Useful SQL Queries?
 

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?
 

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: -
Rosario playing soccer
Re: Useful SQL Queries?
 

Only for crossreference, I have not time to verify at the moment:

https://moodle.org/mod/forum/discuss.php?d=267773

Rosario

 
Average of ratings: -
Picture of Scott Karren
Re: Useful SQL Queries?
Group Particularly helpful Moodlers
Hello all,

I am trying to create a query that shows me all courses that do not contain a quiz activity. So far I have a query that will give me a count of the number of quizzes in a course but I have not been able to figure out how to get it to give me courses that do not have quizzes.  Any help would be appreciated, here is the query I have so far.

SELECT COUNT(*), c.fullname AS Course
 
FROM mdl_course_modules cm
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_modules m ON m.id = cm.module
WHERE m.id = 13
GROUP BY c.id

Scott
 
Average of ratings: -
Picture of Perry Way
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

you're only missing a HAVING clause. A HAVING clause is like a WHERE on aggregates like COUNT, SUM, AVG, MIN, MAX, etc.

It appears after the GROUP BY. So with your query it would be...


SELECT COUNT(*), c.fullname AS Course
FROM mdl_course_modules cm
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_modules m ON m.id = cm.module
WHERE m.id = 13
GROUP BY c.id
HAVING COUNT(m.`id`) = 0;

But I would probably write the query above differently as the COUNT(*) is ambiguous or can be anyway with the addition of a DISTINCT clause. Better to pick an actual row/column identifier (as I show using COUNT(m.`id`)), this way if your query is more elaborate than the above you are working with best practice and will spend less time debugging or perfecting your queries.

 
Average of ratings: -
Picture of Dipa Agravat
Re: Useful SQL Queries?
 

Delete ex
from mdl_exam ex INNER JOIN mdl_exam_schedule exs
ON ex.id=exs.exam_id INNER JOIN mdl_course_modules cm
ON ex.id=cm.instance
Where ex.id=70;
 here query is effect in two table exam and exam_schedule but not in course_modules. All field type is same bigint(10) but still record is delete from exam and exam_schedules but records is not delete in mdl_course_module pls help what is wrong in this query?

 
Average of ratings: -
Picture of Aalishan Moosavi
Re: Useful SQL Queries?
 

Hi,

   I am newbie to moodle . I have installed 'Attendance' and 'Ad-hoc Custom SQL queries' plugin.

we have weekly classes and the attendance is taken on weekly basis by the class teacher / admin.

My requirement is to create report of students who were not present in a class.

Can someone please help me code SQL query that will list student name, class date , attendance status ( Absent, Late, etc)

Thanks in advance

Aali

 
Average of ratings:Useful (1)
Picture of Cristina Fierbinteanu
Re: Useful SQL Queries?
 

Hi,

I am interested in this subject too.

I have installed report_customsql plugin, but I am not sure what to choose for "Attendance": block_attendance, mod_attendance, block_signinsheet, or something else. Which one did you install?

I'll let you know if I come up with any useful SQL query.

Best wishes,

Cristina

 
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 (3)
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 (3)
Picture of Dale Davies
Re: Useful SQL Queries?
 

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: -
C'est moi :-)
Re: Useful SQL Queries?
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Just updated this one for latest Moodle versions (since 2.6 if i'm right) :

SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS id,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS 'Course',
concat('<a target="_new" href="%%WWWROOT%%/enrol/instances.php?id=',c.id,'">Méthodes inscription</a>') AS 'Enrollment plugins',
e.sortorder
FROM prefix_enrol AS e, prefix_course AS c
WHERE e.enrol='guest' AND e.STATUS=0 AND e.password='' AND c.id=e.courseid AND c.visible=1
Also updated in ad'hoc contributed reports documentation (2.8).

Séverin
 
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 (3)
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 MoodlersGroup Testers

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
 

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?
 

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?
 

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 Plugins guardiansGroup 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?
Group Testers

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 (3)
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:Useful (1)
#GoHawks!
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 Iroshini Ratnapala
Re: Useful SQL Queries?
 

Hi Ahmed

Thanks for sharing the query and it is very useful, I could see one mistake in your query.

PREFIX_Resource contains the resource information only. What if PREFIX_log.info contains another number which exactly matches with an irrelevant record in PREFIX_Resource table? It gives wrong data under Information column.

I think this query is working perfectly only for the resource access log records. So I suggest to add a condition to access the resource access logs only,

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> 

AND l.module="resource"

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


Thanks

Iroshini

 
Average of ratings: -
Picture of Dale Davies
Re: Useful SQL Queries?
 

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: -
Picture of Alan Ball
Re: Useful SQL Queries?
 

Here's one to ponder:

A query that calculates the size of resources that a course uses and orders them by the biggest courses. For those of us who have disk space limits, it's good to find out which courses are sucking up all the space!


Any thoughts?

 
Average of ratings: -
Picture of David Mudrák
Re: Useful SQL Queries?
Group DevelopersGroup Documentation writersGroup Moodle HQGroup Particularly helpful MoodlersGroup Translators
Just noting that Moodle 2.x file storage system stores just one physical copy of any file on disk, regardless how many courses (contexts actually) it is used in. It is similar to hard-linking in Linux file systems. So when interpreting results of such query, one should know that removing the file from the course does not necessarily mean it is removed from the disk (and it is not done so immediately actually, there is sort of garbage collector). In other words, the report should also inform about all other places where a given file is hardlinked from.
 
Average of ratings:Useful (2)
Picture of Bob Puffer
Re: Useful SQL Queries?
Group Particularly helpful MoodlersGroup Testers

Unless I'm wrong, in real use this isn't the case. Most duplicate uses of files come about from importing from or backup and restoring courses. In these cases multiple files are stored in Moodle just like the old 1.x file system we all thought we were getting rid of.

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

Bob,

In real use file content is only stored once for identical files.

You are getting this mixed up with a different issue about linking files together, so that when one is updated they are all updated.

The case that David is talking about is that any two files that have identical content (no matter whether they were uploaded by different users, or to different courses, or if they were duplicated during a backup & restore), the content is only stored once. If one of those files is replaced with a new file (e.g. a new version is uploaded over the top of it), then the content is no longer identical and these files are stored separately on the server disk.

 
Average of ratings:Useful (1)
Picture of Carol Griffiths
Re: Useful SQL Queries?
 
We've got a large amount of data that we need to retrieve for some research, but I'm not really sure about how to go about it. We need all posts from all discussions from all forums on a particular course, including user data. So we'd need something like the following columns:

UserId, Firstname, Lastname, timestamp post created, post title, post content, discussionId, discussion name, forum ID, forum name

How do you go about constructing something like this? This is from a Moodle 1.9 installation.

Thanks for any help you can give.
 
Average of ratings: -
Picture of Andreas Panagiotopoulos
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

I have made a similar query:


SELECT
concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum,
fd.name as "Discussion",
g.name as "Group",
fp.subject as "Topic",
fp.message as "Post",
concat(u.firstname,' ', u.lastname) AS "FirstName LastName",
DATE_FORMAT(FROM_UNIXTIME(fp.created),'%d-%m-%Y %H:%i:%s') as "date post added"
FROM prefix_forum_posts AS fp
JOIN prefix_user AS u ON u.id = fp.userid
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
LEFT JOIN prefix_groups AS g on g.id = fd.groupid
JOIN prefix_forum AS f ON f.id = fd.forum
JOIN prefix_course AS c ON c.id = fd.course
%%FILTER_COURSES:c.id%%
ORDER BY f.id, fd.id, fp.id


I also have a filter option for available courses - you can simple replace that line (%%FILTER_COURSES:c.id%%) with a WHERE clause (for example "where c.id = 100").

You can also remove line "g.name as "Group"" and line "LEFT JOIN prefix_groups AS g on g.id = fd.groupid" in order not to include info about the group a discussion has been made for.


Regards,

Andreas

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

I like this idea but I cannot get it to work.  Here's what I have:


SELECT 

concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum,

fd.name as "Discussion",

fp.subject as "Topic",

fp.message as "Post",

concat(u.firstname,' ', u.lastname) AS "FirstName LastName",

DATE_FORMAT(FROM_UNIXTIME(fp.created),'%d-%m-%Y %H:%i:%s') as "date post added"

FROM prefix_forum_posts AS fp 

JOIN prefix_user AS u ON u.id = fp.userid

JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id 

JOIN prefix_forum AS f ON f.id = fd.forum 

JOIN prefix_course AS c ON c.id = fd.course

where c.id = 13482


Any thoughts on what is wrong?

 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

Even though it is an abomination that query executes correctly (provided that prefix_ part of table names is updated to correct prefix). What problems are you having with it?

 
Average of ratings: -
Picture of Melvin Lasky
Re: Useful SQL Queries?
 

Dale,

  Are you sure that top query is right? It appears to be showing total views, and not views per role. For instance, if I run this query:

select  count(*),

b.fullname, b.shortname, f.name from mdl_log a, mdl_course b, mdl_user c, mdl_context d, mdl_role_assignments e, mdl_role f

where a.course = b.id

and   a.userid = c.id

and a.module = 'course'

and a.action = 'view'

and d.contextlevel = '50'

and d.instanceid = b.id

and e.contextid = d.id

and e.userid = c.id

and f.id = e.roleid

group by b.fullname, b.shortname, f.name;

------

I'll see different values for Student and Teacher.

I love the original query. Sadly, I'm an Oracle developer helping out a friend so I'm trying to figure out how to fix it, but no luck yet.

Any suggestions will be greatly appreciated.

 
Average of ratings: -
Picture of Dale Davies
Re: Useful SQL Queries?
 

Without some more testing I'm not sure, its been 4 years since I wrote that query and we no longer run Moodle 1.9 for me to test it on.

I'm now using something like the following in Moodle 2.4 as part of a much larger report, this will get student views from the monthly_stats table broken down by Month.  

This matches up exactly with the figures shown in Moodle's own course statistics report...

SELECT 
	stat_rows.courseid crsid, 
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 9 THEN stat_rows.views END) AS 'Aug',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 10 THEN stat_rows.views END) AS 'Sept',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 11 THEN stat_rows.views END) AS 'Oct',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 12 THEN stat_rows.views END) AS 'Nov',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 1 THEN stat_rows.views END) AS 'Dec',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 2 THEN stat_rows.views END) AS 'Jan',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 3 THEN stat_rows.views END) AS 'Feb',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 4 THEN stat_rows.views END) AS 'Mar',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 5 THEN stat_rows.views END) AS 'Apr',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 6 THEN stat_rows.views END) AS 'May',
	MAX( CASE WHEN MONTH(stat_rows.monthend) = 7 THEN stat_rows.views END) AS 'Jun',
FROM (
	SELECT
		crs.courseid, 
		DATE(FROM_UNIXTIME(crs.timeend)) AS monthend, 
		SUM(crs.statsreads) views
	FROM 
		mdl_stats_user_monthly crs
		INNER JOIN mdl_context AS context ON crs.courseid  = context.instanceid
		INNER JOIN mdl_role_assignments AS ra ON context.id = ra.contextid
		INNER JOIN mdl_user AS usr ON crs.userid = usr.id
	WHERE
		ra.userid = crs.userid AND 
		context.contextlevel = 50 AND
		ra.roleid = 5
		GROUP BY crs.timeend, crs.courseid
) stat_rows
GROUP BY 
	stat_rows.courseid

 
Average of ratings: -
Picture of Christine Mburu
Re: Useful SQL Queries?
 
I am using moodle for the first time. My moodle database is on sql server and at the same time, i want it to pull some information from some sql views i created from another database and store in moodle database. Kindly asking how can i pull data from these views? Attached please find a screen shot of my views and the database




 
Average of ratings: -
Rosario playing soccer
Re: Useful SQL Queries?
 

I do not understand what you want to do. If you populate the moodle database with random data, you will not be able to use it through the Moodle GUI, unless you would correctly provide data for new users, new courses, etc. The whole thing is highly relational so best thing is to use Moodle's programming API. See my uploadusersandcourses.php you can find in these forums, if you would want to program a scheduled job to create new courses and enroll people in those courses at the same time. Or see the many other modules and plugins in the modules and plugins database here to find other programming examples.

Same procedure if you would really want to introduce new data and data structures into the Moodle database. Then you would develop a module to do everything you need, i.e. add data, view/display data, manipulate/update data through the Web-GUI.

Do not think of Moodle like a simple sql-database you can manipulate directly at its table level, you would risk to break everything.

The sql-query examples you can find here are mere tricks to lookup quickly things or compose queries for a particular use, things you can not do through the Web-GUI. Some of these queries are also used in php-scripts if you program your own modules/plugins/etc. Mostly READ behaviour and not (destructive) WRITE behaviour.

Rosario

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

This one queries the log table showing distinct logins for each day using the time frame specified . 

You can change the two dates 

Any comments  to make this better. 

SELECT COUNT(DISTINCT(userid)) as users,
DATE(FROM_UNIXTIME(time)) as date 
FROM prefix_log WHERE ( 
time >= UNIX_TIMESTAMP('2012-09-01 00:00:00') AND 
time < UNIX_TIMESTAMP('2013-08-31 00:00:00') )  GROUP BY DATE(FROM_UNIXTIME(time)) ORDER BY time ASC
 
Average of ratings: -
Picture of Craig Mackey
Re: Useful SQL Queries?
 
Hi all,

We are looking forr a report that lists all messages that have been posted. In my high school we often have issues with students being sent inappropriate messages. A query to list all messages, all messages to or from a user would allow us to easily track how these conversations develop, rather than navigating through various user profiles to read them.

Apparently there used to be a report that did this in Moodle 1.9 but not now.

 I don't know much about writing sql queries but am happy to use the ad-hoc database queries report plugin to run the report.

I also have full access to the database via phppgadmin to export the messages table, though this is not as helpful as a nicely formatted report.

Any help would be greatly appreciated.

Regards
Craig



 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

Did you check out this page?:

https://docs.moodle.org/27/en/Spam

Especially this part

https://docs.moodle.org/27/en/Spam#Cleaning_up_spam

But if you insist on manually looking into messages just do a simple query like this (assuming you use MySQL):


SELECT m.id, m.fullmessage, u.id AS useridfrom, u1.id AS useridto 
FROM       mdl_message AS m
LEFT JOIN  mdl_USER AS u ON u.id = m.useridfrom AND u.deleted = 0
LEFT JOIN  mdl_USER AS u1 ON u1.id = m.useridto AND u1.deleted = 0
WHERE 
      m.fullmessage REGEXP '(tree|cat|rock|badpill|etc.)'



 
Average of ratings: -
Picture of Craig Mackey
Re: Useful SQL Queries?
 

Thanks Darko,

I tried the spam cleaner report, and that will certainly pickup on any inappropriate language that I search for.

However, I would really like to view a messages report. Our database is postgres so your MySql query didn't work ... as expected but I thought I'd try anyway.

Do you have any ideas of how to do the query in postgres?

 

Thanks again,

Craig.

 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

You can always look at the postgres documentation

http://www.postgresql.org/docs/9.3/static/functions-matching.html

In any case you do not have to use regular expression to view messages. Just do simple SELECT * FROM mdl_messages and you can see the content of the table.

 
Average of ratings: -
Picture of Craig Mackey
Re: Useful SQL Queries?
 

Thanks Darko, I'll have a look when I have a chance. Will post any success here.

 

Craig.

 
Average of ratings: -
Picture of Bernhard Wieser
Re: Useful SQL Queries?
 

Hello, 

does anyone know the SQL-statement for exporting moodle categories (moodle 2.6). I've tried to export the table "course_categories", but there seems to be data stored in another table too. We are using a complex structure of categories, which I have to reuse on an new moodle instance. If you simply replace the table "course_categories" or insert new categories via SQL, the new Categories are not visible and I get the error message "moodle_database::get_in_or_equal() does not accept empty arrays". What am I doing wrong? 

Or maybe there is a third party plugin available for that purpose? I searched the web for hours to find a solution, hope you can help me.


greetings 

bernhard


 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

Every category also must have context (located in mdl_context table) so you should export those as well, BUT since you want to just copy the category structure and we can not guarantee the exact same id's in both tables I would advise to recreate entire category structure with the PHP code. I suggest you look into coursecat::create method located in [moodle]/lib/coursecatlib.php.

This code would create new category:


require_once($CFG->libdir.'/coursecatlib.php');

$data = new stdClass();
$data->name = 'Some name';
$data->description = 'Some description or empty';
$data->descriptionformat = FORMAT_HTML;
$data->parent = 0; // 0 for top level category or parent category id.
$newcat = coursecat::create($data);





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

I'm interested in creating an sql report to run in the Moodle Dashboard that returns the course name, course ID, advanced discussion forum name.  I have the following that returns everything I want, however, the course name, course ID, and advanced discussion forum name are mixed up.  Any ideas what I'm not linking together?

select mc.shortname "course",

ma.name "Discussion Forum",

mcm.id


from mdl_course mc

join mdl_course_categories mcc on mcc.id = mc.category

join mdl_course_modules mcm on mc.id = mcm.course

join mdl_modules mm on mm.id = mcm.module

join mdl_hsuforum ma on mcm.instance = ma.id

WHERE mc.CATEGORY = 316

 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

You need to filter the type of course module. This is corrected query:


select mc.shortname "course",
       ma.name "Discussion Forum",
       mcm.id,
       mm.name
from mdl_course mc
join mdl_course_categories mcc on mcc.id = mc.category
join mdl_course_modules mcm on mc.id = mcm.course
join mdl_modules mm on mm.id = mcm.module
join mdl_hsuforum ma on mcm.instance = ma.id
WHERE mc.category = 316 AND mm.name='hsuforum'



 
Average of ratings:Useful (1)
Picture of Chris S
Re: Useful SQL Queries?
 

Thank you for your fast reply and for helping me see where I was going wrong!

Cheers, Chris

 
Average of ratings: -
Picture of Bernhard Wieser
Re: Useful SQL Queries?
 

Hello, that's exactly what I was looking for - thank you!

greetings bernhard

 
Average of ratings: -
Picture of Chris S
Attendance SQL Query Help
 

Folks, I have an attendance sql query that I run in the dashboard and it is only partially working.  When I run the report it is returning to me each student within a course, his/her email, id number, and the last time the student has logged into the course.  The bad news is that the report shows the same 'last login' date for a student in multiple courses.  I know this to be wrong because a student can login to one course and not the other courses if he/she chooses.

Does anyone have any suggestions why the data is incorrect or what I'm not linking together?

Thank you all...you've made my life easier and are helping me to work smarter instead of harder!

SELECT
c.shortname AS "Course Name",
concat( u.firstname, " ", u.lastname ) AS Fullname,
u.idnumber,
u.email,
FROM_UNIXTIME( currentlogin, '%M %D, %Y' ) AS "Last Login"
FROM mdl_role_assignments ra 
JOIN mdl_user u ON u.id = ra.userid
JOIN mdl_role r ON r.id = ra.roleid
JOIN mdl_context cxt ON cxt.id = ra.contextid
JOIN mdl_course c ON c.id = cxt.instanceid
WHERE ra.userid = u.id
AND ra.contextid = cxt.id
AND cxt.contextlevel =50
AND cxt.instanceid = c.id
AND roleid = 5
AND category = 316
ORDER BY Fullname ASC
 
Average of ratings: -
Picture of Darko Miletić
Re: Attendance SQL Query Help
Group DevelopersGroup Particularly helpful Moodlers

mdl_user.currentlogin tracks the time when user logged in into platform and nothing else. If you want to list moments when user visited course you need to filter mdl_log table searching for course view entries.

SELECT userid, FROM_UNIXTIME(MAX(time), '%m/%d/%Y %h:%i:%s') AS lastvisit
FROM mdl_log 
WHERE course = [courseid] AND module = 'course' AND action = 'view'
GROUP BY userid




 
Average of ratings:Useful (2)
Picture of Chris S
Re: Attendance SQL Query Help
 

You're wonderful at saving my day!  

  1. Is there any way I can get the above report to run for a category instead of a course?
  2. Is there any way I can get the above report to include similar data as the original I posted (course name, student full name, id number, email)?

Thanks, Chris

 
Average of ratings: -
Picture of Darko Miletić
Re: Attendance SQL Query Help
Group DevelopersGroup Particularly helpful Moodlers

This should work


SELECT
c.shortname AS "Course Name",
concat( u.firstname, " ", u.lastname ) AS Fullname,
u.idnumber,
u.email,
FROM_UNIXTIME(MAX(l.time), '%M %D, %Y' ) AS "Last Login"

FROM mdl_role_assignments ra 
JOIN mdl_user u ON u.id = ra.userid AND u.deleted = 0
JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'
JOIN mdl_context cxt ON cxt.id = ra.contextid AND cxt.contextlevel =50
JOIN mdl_course c ON c.id = cxt.instanceid
JOIN mdl_log l ON c.id = l.course AND u.id = l.userid AND l.module = 'course' AND l.action = 'view'

WHERE c.category = 8
GROUP BY l.userid
ORDER BY Fullname ASC



 
Average of ratings:Useful (2)
Picture of Chris S
Re: Attendance SQL Query Help
 

Darko, you've saved my day again.  Thank you!  I have another report I would like to create and am not sure if I am doing it correctly.  It will return the course name, course id, book chapter name, and book chapter content.

I'm trying to join everything correctly but I just don't know what I'm really doing wrong...

select mc.shortname "course",
       ma.title,
       ma.content,
       mcm.id

from mdl_course mc
join mdl_course_categories mcc on mcc.id = mc.category
join mdl_course_modules mcm on mc.id = mcm.course
join mdl_modules mm on mm.id = mcm.module
join mdl_book_chapters ma on mcm.instance = ma.id
WHERE mc.category = 316 AND mm.name='book'
 
Average of ratings: -
Picture of Darko Miletić
Re: Attendance SQL Query Help
Group DevelopersGroup Particularly helpful Moodlers

Your main problem seems to be that you try to guess query without actually analyzing the underlying database structure. First of all you should look at table structure (MySQL Workbench is recommended here) and see the actual field names. Guessing will get you nowhere.

SELECT mb.id, c.fullname, c.id, mb.name, mbc.title, mbc.content
FROM       mdl_book AS mb
INNER JOIN mdl_course AS c ON mb.course = c.id
INNER JOIN mdl_course_categories AS mcc on mcc.id = c.category
LEFT JOIN  mdl_book_chapters AS mbc ON mbc.bookid = mb.id
WHERE c.category = [category id]



 
Average of ratings: -
Picture of Chris S
Re: Attendance SQL Query Help
 

You are correct...I know very little about the database structures.  I've been working with just enough knowledge to try to put together something from what I have found online or from you all.  I will definitely look more into the MySQL Workbench.  Thank you for the recommendation!

I ran the code and received the following error message: "The SQL entered is not supported by this report or is incorrect. Error reading from database."

Tried to decipher your code but didn't have any luck finding out where something is going wrong.  Will keep trying but also would appreciate any help you can offer.  The LEFT JOIN is a bit out of my league.

Thanks, Chris

 
Average of ratings: -
Picture of Darko Miletić
Re: Attendance SQL Query Help
Group DevelopersGroup Particularly helpful Moodlers

Paste here exactly what you ran. I tested the query before posting.

 
Average of ratings: -
Picture of Chris S
Re: Attendance SQL Query Help
 
SELECT mb.id, c.fullname, c.id, mb.name, mbc.title, mbc.content
FROM mdl_book AS mb
INNER JOIN mdl_course AS c ON mb.course = c.id
INNER JOIN mdl_course_categories AS mcc on mcc.id = c.category
LEFT JOIN  mdl_book_chapters AS mbc ON mbc.bookid = mb.id
WHERE c.category = 316
That's the code...just added the category of 316.  Thanks for your help!
 
Average of ratings: -
Picture of Darko Miletić
Re: Attendance SQL Query Help
Group DevelopersGroup Particularly helpful Moodlers

Just executed it again without any problem in mysql workbench. How are you executing this?

 
Average of ratings: -
Picture of Chris S
Re: Attendance SQL Query Help
 

Darko, I run all reports in the Moodle Dashboard.

 
Average of ratings: -
Picture of Chris S
Re: Attendance SQL Query Help
 

Darko, I removed c.id in the select field and used this which ran flawlessly:

SELECT mb.id, c.fullname, mb.name, mbc.title, mbc.content
FROM mdl_book AS mb
INNER JOIN mdl_course AS c ON mb.course = c.id
INNER JOIN mdl_course_categories AS mcc on mcc.id = c.category
LEFT JOIN  mdl_book_chapters AS mbc ON mbc.bookid = mb.id
WHERE c.category = 316
 
Average of ratings: -
Picture of Darko Miletić
Re: Attendance SQL Query Help
Group DevelopersGroup Particularly helpful Moodlers

The problem was related to the fact that there where 2fields with same name ( id ). When making a query for that module all fields in select must have unique name.

 
Average of ratings: -
Picture of Chris S
Re: Attendance SQL Query Help
 

Darko, I've been doing some evaluating of these reports and noticed that this one in general does not list those that have 'never' logged in.  Is there a way to show these folks?  I tried researching the mdl_log action to change it from view since these folks obviously did not view anything.  No luck.

Thanks!

 
Average of ratings: -
Picture of Darko Miletić
Re: Attendance SQL Query Help
Group DevelopersGroup Particularly helpful Moodlers

I think it is time for you to get yourself a decent book on way SQL works.

You can start here

http://www.sqlcourse.com/

 
Average of ratings: -
Picture of Ivana Skelic
Re: Useful SQL Queries?
 

Hi thread watchers, thank you for this useful thread. Can someone suggest me how to get  the name of resource (e.g. book name) using contextid?

My idea was the following, but I'm not sure what to JOIN next

SELECT b.name FROM {book} b
JOIN {course} c ON c.id = b.course
JOIN {course_modules} cm ON cm.course = c.id
JOIN ...
WHERE cx.id = ?

And cx.id should be from context table. 

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

The bad news is that the name is in the table like mdl_quiz, mdl_forum, etc, so the only way to do it with a JOIN is to join the tables for all activity types.

(With hind-sight, the name column should have been in the course_modules table, but that design mistake was made about 14 years ago, and is rather hard to fix now.)

 
Average of ratings: -
Picture of Ivana Skelic
Re: Useful SQL Queries?
 

So I cannot extract the name of the resource (Book, Assignment) that user is currently looking at? Or unique id?

The only thing that comes to my mind is some kind of extraction of id from $PAGE->url but I suppose that this solution can give me a lot of headaches

 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group DevelopersGroup Particularly helpful Moodlers

As Tim already said you have to do something like this:

SELECT cm.id, 
	   CASE
            WHEN mf.name IS NOT NULL THEN mf.name
            WHEN mb.name IS NOT NULL THEN mb.name
            // add more checks for other modules
            ELSE NULL
       END AS activityname      
FROM       mdl_course_modules AS cm
INNER JOIN mdl_context AS ctx ON ctx.contextlevel = 70 AND ctx.instanceid = cm.id
INNER JOIN mdl_modules AS mdl ON cm.module = mdl.id
LEFT JOIN mdl_forum AS mf ON mdl.name = 'forum' AND cm.instance = mf.id
LEFT JOIN mdl_book AS mb ON mdl.name = 'book' AND cm.instance = mb.id
// Add more joins here for other modules



 
Average of ratings:Useful (2)
Picture of Ivana Skelic
Re: Useful SQL Queries?
 

Woah, I could think for a 100 years and never could've think of something like this. Thank you Darko and Tim, I hope this will be useful to someone else!

 
Average of ratings: -
Picture of Chiraq Beats
Re: Useful SQL Queries?
 

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.


 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
 
Average of ratings: -
Picture of Chris S
SQL Query for Labels
 

Hi everyone!  I'm loving this forum.  I've gotten so many ideas and they have made my life so much easier.  I am interested in creating an sql query that returns every label (mdl_label) in a specific category (316).  I'd like the report to include the following items: Course, Course ID, Label Name, and Label Intro.

Thanks for your help!

 
Average of ratings: -
Picture of Steven Swanson
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

The date and time of the students last login if its been more that 200 days then it will say "never" because they have never logged in. I added that in because students that never logged in would show a last date of 1969 for some reason. you can see this when using "browse users", but here you can export it.


SELECT firstname, lastname, email,phone1,
CASE
WHEN DATEDIFF (NOW(), FROM_UNIXTIME(lastaccess)) >200 THEN "never" 
ELSE  FROM_UNIXTIME(lastaccess, "%m-%e-%y @ %h:%i%p")
END AS last_login
FROM prefix_user
WHERE deleted=0 


_---------------------------------------------------------------------------------------------------------------------------------------

This will show a list of students who have not logged in for 7 or more days. The report will show only those students. in the column "last login" will show the amount of days since their last login. If they have never logged in before it will say never. Helps identify students who are not engaged.Change 7 to what ever number you would like.


SELECT firstname, lastname, email,phone1,
CASE
WHEN DATEDIFF (NOW(), FROM_UNIXTIME(lastaccess))>200
THEN "never"
ELSE
DATEDIFF (NOW(), FROM_UNIXTIME(lastaccess))
END AS last_login
FROM `prefix_user`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(lastaccess)) > 7 AND deleted=0

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

Also for dedication time using configurable reports, I know where to adjust the code so that time is in the unit you need. normally it is 3 hours 43 min format. I needed hours rounded up to the nearest quarter hour last example would be 3.75 . If you come across this and would like to know, just message me.

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

Thanks for sharing those Steven !

For people starting out with writing Moodle reports via SQL they are nice and simple.

It would be a nice bit of homework for people to take this second query and develop it to report on just one Course eh ?

Stu smile

 
Average of ratings: -
Picture of Steven Swanson
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

Anybody know how to add a column for my custom profile field. I created a field called "what school does student attend" shortname : "schoolname" . I have tried everything to add this column including the following:


SELECT u.firstname, u.lastname, u.email,u.phone1,uid.data, uif.schoolname,
CASE
WHEN DATEDIFF (NOW(), FROM_UNIXTIME(u.lastaccess)) >200 THEN "never" 
ELSE  FROM_UNIXTIME(u.lastaccess, "%m-%e-%y @ %h:%i%p")
END AS last_login
FROM prefix_user AS u
LEFT JOIN prefix_user_info_data AS uid ON uid.userid = u.id
LEFT JOIN prefix_user_info_field AS uif ON uif.id = uid.fieldid
WHERE u.deleted = 0

Anyone help with this issue??


 
Average of ratings: -
Picture of Bryan Smith
Re: Useful SQL Queries?
 

I'm working on a query to list all users, rolls, and course they are enrolled in. Where I'm running into a problem is I'm only getting students so far, no teachers / course creators.


Any help would be appreciated.



SELECT 

mdl_user_enrolments.userid as useenrol_userid,

mdl_user_enrolments.id as useenrol_id,

mdl_user_enrolments.enrolid as userenrol_enrolid,

mdl_user_enrolments.userid as userenrol_userid,

mdl_enrol.courseid as enrol_courseID,

mdl_course.fullname as courses_fullname,

mdl_enrol.roleid as enrol_roleid,

mdl_role.shortname as enrol_shortname


FROM mdl_user_enrolments

LEFT OUTER JOIN mdl_enrol ON (mdl_enrol.id = mdl_user_enrolments.enrolid)

LEFT OUTER JOIN mdl_course ON (mdl_enrol.courseid = mdl_course.id)

LEFT OUTER JOIN mdl_role ON (mdl_role.id = mdl_enrol.roleid)

 
Average of ratings: -
Picture of safaa al-mamory
Re: Useful SQL Queries?
 


Dear Forum membars,

Thanks for every one who shared in this page. I have many many courses (which represent colleges) each having a teacher (i.e. a dean, head of department, dean deputy,...) and students (employees, staff faculty, ...). Every student should do a quiz.


After finishing the quiz and gathering all information from users, we are interesting in collecting the answers of every question. I installed the configurable report plugin which is an amazing tool. Then I found several SQL statements in the forum. One related SQL of these is to extract the details of a quiz. For testing, I have created a quiz with ID=1000. Then when I try to save the SQL I am getting the error "No Explicit Prefix." https://moodle.org/mod/forum/discuss.php?d=129901 suggests to replace each occurance of "mdl_" with "prefix_". However I still get an error. I am a biggner with SQL so could any one help us to extract these information from Moodle? The mentioned SQL is as follows:


SELECT

quiza.userid,

quiza.quiz,

quiza.id AS quizattemptid,

quiza.attempt,

quiza.sumgrades,

qu.preferredbehaviour,

qa.slot,

qa.behaviour,

qa.questionid,

qa.maxmark,

qa.minfraction,

qa.flagged,

qas.sequencenumber,

qas.state,

qas.fraction,

FROM_UNIXTIME(qas.timecreated)

qas.userid,

qasd.name,

qasd.VALUE,

qa.questionsummary,

qa.rightanswer,

qa.responsesummary

FROM mdl_quiz_attempts quiza

JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid

JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id

JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id

LEFT JOIN mdl_question_attempt_step_data qasd ON qasd.attemptstepid = qas.id

WHERE quiza.id = 1000

ORDER BY quiza.userid, quiza.attempt, qa.slot, qas.sequencenumber, qasd.name




Thanks, and looking forward to hearing from you soon.


Safaa O. Al-mamory


 
Average of ratings: -
Picture of Dale Davies
Re: Useful SQL Queries?
 

You seem to be missing a comma after the following line...

FROM_UNIXTIME(qas.timecreated)

Not had time to check the query properly, but it does at least run when you add a comma to the end of that line.

 
Average of ratings: -
Picture of Premi Sivanesan
Re: Useful SQL Queries?
 

Does anyone have a query which will check if the students have completed a particular course?

 
Average of ratings: -
Picture of Steven Swanson
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

SELECT u.firstname, u.lastname, uid.data AS School,u.idnumber AS ID, c.fullname AS course,
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m-%d-%y')
AS completed
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_user_info_data AS uid ON u.id = uid.userid
JOIN prefix_user_info_field AS uif ON uif.id = uid.fieldid
WHERE c.enablecompletion = 1 AND u.lastname != "Doe" AND u.email NOT LIKE "%@emuhsd.k12.ca.us" AND uid.fieldid = 1
ORDER BY FROM_UNIXTIME(p.timecompleted)

 
Average of ratings: -
Picture of Premi Sivanesan
Re: Useful SQL Queries?
 

Does anyone have a query which will check if the students have completed a particular course?

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

This forum has been a gem.  Everyone posts great examples and I'm starting to get the hang of these SQL queries.  I've got one that I'm a bit stumped on.  The new STANDARD LOG has me a bit perplexed.  From the code below, I can retrieve BOTH faculty (role 3) and students (role 5).  I'm interested in filtering between the two but can't seem to figure out how to join context and role_assignments to the below code.  Any thoughts?

select 
mdl_logstore_standard_log.target,
mdl_logstore_standard_log. action,
mdl_logstore_standard_log. courseid,
mdl_course.shortname,
mdl_course_categories.name as Category,
mdl_user.idnumber,
 concat (mdl_user.firstname, ' ', mdl_user.lastname) as Who,
mdl_user.email,
from_unixtime(mdl_logstore_standard_log.timecreated, '%M %D, %Y' ) as Time

From mdl_logstore_standard_log
       
Left join mdl_course on mdl_logstore_standard_log.courseid = mdl_course.id
Left join mdl_user on mdl_logstore_standard_log.userid = mdl_user.id
Left join mdl_course_categories on mdl_course.category = mdl_course_categories.id

WHERE mdl_course.shortname LIKE '%15WNDLC%' AND mdl_course.shortname LIKE '%2010%'
AND mdl_logstore_standard_log.timecreated > unix_timestamp('2015-1-08 00:00:00')

Thanks for your time!

Chris

 
Average of ratings: -
Picture of Dale Davies
Re: Useful SQL Queries?
 

I've not used the standard logstore yet so I'll need to fire up a more recent instance of Moodle.

However, I've run into problems creating reports based on joins to the role assignments table, because it wont bring out data for students who are no longer enrolled. So something like total number of views by students over the academic year can be difficult as it could fluctuate.

Would be interested to hear any thoughts on avoiding this.

 
Average of ratings: -
Picture of Steven Swanson
Re: Useful SQL Queries?
Group Particularly helpful Moodlers

I paid good money for this to be written, but in the spirit of sharing. This is what it does.

Lists the name of every student enrolled, their course name, their grade and number of days since last login. THE BEST PART! It shows even students that have not started the course yet and gives them a zero. A big problem with other grade reports is that it does not list students who have no grade at all.


SELECT
 u.firstname AS first,
 u.lastname AS last,
 u.idnumber AS ID,
 u.institution AS School,
 c.shortname AS coursefullname,
 COALESCE(ROUND(gg.finalgrade,0),0) as finalgrade,
CASE
WHEN DATEDIFF (NOW(), FROM_UNIXTIME(u.lastaccess))>200
THEN "never started"
ELSE
CONCAT (DATEDIFF (NOW(), FROM_UNIXTIME(u.lastaccess)), " ", "days")
END AS last_logged_in

FROM prefix_user u
INNER JOIN prefix_role_assignments ra ON ra.userid = u.id
INNER JOIN prefix_context ct ON ct.id = ra.contextid
INNER JOIN prefix_course c ON c.id = ct.instanceid
INNER JOIN prefix_role r ON r.id = ra.roleid
LEFT JOIN
(
SELECT
  u.id AS userid,c.id as courseid,
  g.finalgrade AS finalgrade
  FROM prefix_user u
  JOIN prefix_grade_grades g ON g.userid = u.id
  JOIN prefix_grade_items gi ON g.itemid =  gi.id
  JOIN prefix_course c ON c.id = gi.courseid where gi.itemtype = 'course'
) gg ON gg.userid = u.id and gg.courseid = c.id

WHERE r.id = 5

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

Thanks for sharing Steven

This type of query really helps others reading this forum to develop their skills - by taking working examples and tweaking them.  In fact it's a good exercise for anyone learning MySQL to take a query like this and add comments to it to understand piece by piece what is happening smile

I haven't got into the new logging system queries yet (now mdl_log is legacy) but I'm sure there will be some interesting conversations in this thread over the coming year!

 
Average of ratings: -
Me at the Moodle Moot NZ11
MySQL Reporting in Moodle course - 1st April
Group Particularly helpful Moodlers

Just thought this course might be useful for those starting out with MySQL queries in Moodle.  It's our online course that goes from the basics of setting up a development environment, command line interface, through to select queries, to joins, and various useful tools for people wanting to create their own reports.  It's too basic for some people in this Forum who are already doing these things.

We only run the course twice each year, it's 8 weeks long, and 100% online. Next start date is 1st April.  You can read the course outline here:

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

Anyone reading this can just add this Forum number "153059" to the registration form for a automatic 10% discount smile

Stu

 
Average of ratings: -
Picture of jodi collins
Query Assistance Needed**
 

My mind is going in a thousand directions. I'm working on 3 things...

1. identify the number of user records and associated first and last names of users with more than one class enrollment

2. modify a user lastname to add a hyphenated second name

3. identify users without any enrollments


Any help would be greatly appreciated!

 
Average of ratings: -
Me at the Moodle Moot NZ11
Re: Query Assistance Needed
Group Particularly helpful Moodlers

3. I think there's already a simple query earlier in this thread for returning users who are not enrolled in any courses smile

2. Changing a name is probably best (and most simply) done by editing the user profile ?
But if you wanted to use SQL it would be based around something like:
UPDATE mdl_user SET lastname='whatever' WHERE id=<userid>;

 
Average of ratings: -
Picture of Cristina Fierbinteanu
Re: Useful SQL Queries?
 

I'm looking for an SQL statement to remove the numbering of choices for all multiple choice questions in a category.

(Changing "Number the choices?" from "a.,b.,c.,.." to "No numbering", for all questions in a course, or in a category.)

Any help would be greatly appreciated.

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

It will depend on the Moodle version of course.

But in 2.9 there is a mdl_question table, so it's likely a value in there that needs an UPDATE ?

Should be fairly easy to spot as I think it's a per question setting.

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

It is a setting per multi-choice question. It does not apply to all questions, so it is in the qtype_multichoice_options table, answernumbering column.

To restrict which questions are affected, you need something like

UPDATE mdl_qtype_multichoice_options
SET answernumbering = 'none'
WHERE questionid IN (SELECT id FROM mdl_question WHERE category = 123)

Obviously replace 123 with the category id you really want to change.

 
Average of ratings:Useful (2)
Picture of Cristina Fierbinteanu
Re: Useful SQL Queries?
 

Thank you both, Tim and Stuart, I appreciate your help very much.

I had asked the same question on StackOverflow, and posted your answer there as well:

http://stackoverflow.com/q/29628590/3449288


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

Well, you got the ultimate expert answer from Tim wink

 
Average of ratings: -
Picture of Cristina Fierbinteanu
Re: Useful SQL Queries?
 
Thanks again for pointing out it depends on the Moodle version. In 2.5.9,  the following statement worked for me:

UPDATE  mdl_question_multichoice
SET answernumbering = 'none'
WHERE question IN
    (SELECT id FROM mdl_question
     WHERE category = 7);


 
Average of ratings: -
Picture of Sebastián Pizarro Cortés
Re: Useful SQL Queries?
 
Thanks for your information
 
Average of ratings: -
Picture of M'O Z
Re: Useful SQL Queries?
 

i want to get list of trainers and their courses:

I am using following query

SELECT u.id, u.firstname, u.lastname, u.email, c.fullname

FROM mdl_user u, mdl_role_assignments r, mdl_context cx, mdl_course c

WHERE u.id = r.userid

AND r.contextid = cx.id

AND cx.instanceid = c.id

AND r.roleid =3

AND cx.contextlevel =50

i am only getting single course, need help on this.


 
Average of ratings: -