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 (12)
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 (3)
Lei Zhang
Re: Useful SQL Queries?
Group Plugin developers
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 Core developersGroup Particularly helpful MoodlersGroup Plugin developersGroup 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 Bhavani R
Re: Useful SQL Queries?
 

Hello,

I am new to this moodle Kindly know the below mentioned query..

I need a query that shows the login user and his course name as well as completion of the course of a login user ... Kindly let me know the query.



---Bhavani

 
Average of ratings: -
Picture of Bhavani R
Re: Useful SQL Queries?
 

Hello guys,


here is the answer..Thanks to 'Emmanuel Bakare' for a  awesome query.. I have added some extra stuff to this query according to the my requirements...

SELECT u.id, u.username,  c.id courseID, c.fullname CourseName
,
   
        IFNULL((SELECT COUNT(gg.finalgrade)
          FROM mdl_grade_grades AS gg
          JOIN mdl_grade_items AS gi ON gg.itemid=gi.id
          WHERE gi.courseid=c.id
           AND gg.userid=u.id
           AND gi.itemtype='mod'
           GROUP BY u.id,c.id),'0') AS 'Activities Completed'
,
 
        IFNULL((SELECT COUNT(gi.itemname)
          FROM mdl_grade_items AS gi
          WHERE gi.courseid = c.id
           AND gi.itemtype='mod'), '0') AS 'Activities Assigned'
,
 
/*If Activities completed = activities assigned, show date of last log entry. Otherwise, show percentage complete. If Activities Assigned = 0, show 'n/a'.--*/
(SELECT IF(`Activities Assigned`!='0', (SELECT IF((`Activities Completed`)=(`Activities Assigned`),
/*--Last log entry--*/
(SELECT CONCAT('100% completed ',FROM_UNIXTIME(MAX(log.TIME),'%m/%d/%Y'))
FROM mdl_log log
WHERE log.course=c.id
AND log.userid=u.id),
/*--Percent completed--*/
(SELECT CONCAT(IFNULL(ROUND((`Activities Completed`)/(`Activities Assigned`)*100,0), '0'),'% complete')))), 'n/a')) AS '% of Course Completed'


FROM mdl_user u

INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id

INNER JOIN mdl_enrol e ON e.id = ue.enrolid

INNER JOIN mdl_course c ON e.courseid = c.id

INNER JOIN mdl_course_modules cm ON c.id=cm.course

INNER JOIN mdl_modules module ON module.id=cm.module

INNER JOIN mdl_course_sections cs ON cs.id=cm.section
WHERE u.id='$userid'
 

 AND cs.section !=0   /* Remove this portion if you want to also list the general activities on top of a course */

 GROUP BY c.fullname


Note: instead of userId we can go with username or else we can also provide particular userId 0r name



--Bhavani N R

 
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 Core 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?
Group Plugin developers

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 Core 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?
Group Particularly helpful Moodlers

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

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

What are you considering as empty?

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

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

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

Hi Susan

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

Thanks smile

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

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?
 
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?
 

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

Im new to Sql quries as well....

I recntly installed ad-hoc 2.9 to my 2.9 Moodle site. I can not get the scheduled reports to email to admins.. My other smtp functions work on the site...

Is this  plugin issue or A site php issue... Any assistance would be appreciated.

Thank you,

Mark

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Useful SQL Queries?
Group Core developersGroup Documentation writersGroup Particularly helpful MoodlersGroup Plugin developers

The messages are sent using Moodle's standard message_send function https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L618. So, of other Moodle emails work on the site (e.g. forum subscription emails) I don't know why these ones don't work.

 
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 SBruni .
Re: Useful SQL Queries?
 

Séverin, can you help me?

In one of the courses some ons set : Visitors cccess even if the don´t have the code. 

In the table 'mdl_course' the password is written but even if a student don't inform it he can enter the course. 

How can I see all courses in this situation?


Oh it´s a Moodle 1.9.X...

 
Average of ratings: -
Picture of Nadav Kavalerchik
Re: Useful SQL Queries?
Group Core developersGroup Particularly helpful MoodlersGroup Plugin developersGroup 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?
Group Plugin developers

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 Core developersGroup Moodle HQGroup Particularly helpful MoodlersGroup Plugin developersGroup 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?
Group Plugin developers

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?
Group Plugin developers

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?
Group Plugin developers

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: Useful (1)
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?
Group Plugin developers

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?
 

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

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

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

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

Hello,

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

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

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

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

Thanks

 

 

 

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

Can anyone help me with this query?

thanks

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

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

Retrieve all COURSES in ONE CATEGORY:

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

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


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

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

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

 

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

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

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

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

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

Rosario


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

Thanks for all the useful queries!


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

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


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


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


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

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

This is awesome!

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

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

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

Thanks!

Brian

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

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

Rosario

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

Hi Sonya, 

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

Im using moodle 1.9.6

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

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

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

Thank you 

Carlos Lara

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

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

    scorm_scoes_track

    scorm

    course c

thanks

Linda

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

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 Core developersGroup Particularly helpful MoodlersGroup Plugin developersGroup 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.
Group Plugin developers
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 Core developersGroup Particularly helpful MoodlersGroup Plugin developersGroup 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.
Group Plugin developers
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 Plugin developersGroup 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 Core developersGroup Particularly helpful MoodlersGroup Plugin developers

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 Core developersGroup Particularly helpful MoodlersGroup Plugin developers
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 Core developersGroup Documentation writersGroup Particularly helpful MoodlersGroup Plugin developers

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: Useful (1)
Picture of David Mudrák
Re: Useful SQL Queries?
Group Core developersGroup Documentation writersGroup Moodle HQGroup Particularly helpful MoodlersGroup Plugin developersGroup TestersGroup 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 Plugin developersGroup 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 Core developersGroup Particularly helpful MoodlersGroup Plugin developers

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 Shaun Tyson
Re: Useful SQL Queries?
 

I understand how the storage works but is it possible to query this? Our issues it we have 1000's of courses and use middleware to manage them. When we attempt to backup and restore courses over 4gb it fails. I'd like to be able to find courses that would backup to over 4gb. My first attempt at the query isn't working as the content is in different components and my query seems to only get course.  Any suggestions?
Thanks.

select c.shortname, sum( f.filesize) as Size from mdl_files as f
join mdl_context as con on f.contextid = con.id
join mdl_course as c on con.instanceid = c.id
group by c.shortname
order by Size desc

 
Average of ratings: -
Picture of Daniel Thies
Re: Useful SQL Queries?
Group Core developersGroup Particularly helpful MoodlersGroup Plugin developersGroup Testers

Is this MDL-41838? What version are you using? In 2.6-2.8 it might be fixed with a config setting change.

 
Average of ratings: -
Picture of Shaun Tyson
Re: Useful SQL Queries?
 

There is the ability to use the experimental zip setting however this causes other issues and we are trying to reduce the size of courses to increase performance when backing up and restoring.

 
Average of ratings: -
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 Core 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 Octavio Gonzalez
Re: Useful SQL Queries?
 

Hello, Can anybody help me PLEASE?

I'm trying to make the following query, but managed not find the error

SELECT 
u.lastname AS 'LASTNAME', 
u.firstname AS 'NAME', 
u.email AS 'EMAIL', 
gi.itemname AS 'ITEM',
c.fullname AS 'COURSE', 
g.name AS 'GROUP',
ROUND(gg.finalgrade,2) AS 'NOTE', 
FROM_UNIXTIME(gg.timecreated, '%d/%m/%Y %H:%i') AS 'CREATE'

FROM prefix_course AS c
JOIN prefix_groups AS g ON c.id = g.courseid
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE  gi.courseid = c.id AND gi.itemname != 'Attendance'
AND
( u.city ='concentro' OR  u.city ='CONCENTRO')
AND
(gg.timecreated!='')
AND
(gi.itemname!='Ejercicio: Subir tarea en archivo')
AND
(gi.itemname!='Ejercicio: Tarea de texto en línea')
AND
(FROM_UNIXTIME(gg.timecreated) LIKE '%2015%')
 
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:u.lastname%%
 
ORDER BY gg.timecreated DESC, c.fullname

I had difficulty g.name add the table as I need to get the name of the group to which the student belongs.

My moodle version is 2.3
thanks for the attention.
.


 
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?
Group Plugin developers

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 Plugin developersGroup 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 Core 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 Core 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 Core 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 Core 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 Core 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 Core 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 Core 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 Core 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 Core 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 Core 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 Core 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 Core developersGroup Documentation writersGroup Particularly helpful MoodlersGroup Plugin developers

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 Core 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 (3)
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?
 

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?
 

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?
Group Plugin developers

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?
 

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?
 

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 (4)
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 Troy Fennell
Re: Useful SQL Queries?
 

Good money well spent! smile I'll tell you what the best part(s) actually are though:

No stupid mdl_ prefixes to have to remove before testing! Yes!

It actually works on my system.

I can replace the role id with my particular role ids and i get records back on just those students!

I'm not done yet. But this helps me very much.

Thank you for sharing!


 
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 Core developersGroup Documentation writersGroup Particularly helpful MoodlersGroup Plugin developers

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 Miki Alliel
Re: Useful SQL Queries?
Group Translators

I'm just updating that in moodle 2.9 + the  'mdl_question_multichoice' table  is no more exist and not the table is

mdl_qtype_multichoice_options

the sql should be :

UPDATE  mdl_qtype_multichoice_options

SET answernumbering = 'none'

 WHERE questionid IN (SELECT id FROM mdl_questionWHERE category = 7);


 
Average of ratings: -
Mian Omer
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: -
Picture of Tifano T
Re: Useful SQL Queries?
 

Hi M'O Z,

Not sure if you got this working.

The query looks fine for me, what did you mean by "only getting single course" ?

Cheers


 
Average of ratings: -
Picture of Byron Patterson
Re: Useful SQL Queries?
 

Hi everyone.  We needed a report that :

1) showed all enrolled users
2) showed their "final grade" from a lesson activity type 
3) showed their "final grade" from the actual quiz

We then run this as an SSRS Report that is auto emailed every morning for this course.

You will need to change the course.id value to your course id.


SELECT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
course.fullname AS Course,
course.shortname as Course_code
, prefix_grade_items.courseid
, prefix_grade_items.itemmodule
, prefix_grade_items.itemname
, ROUND(prefix_grade_items.grademax,0) as grademax
, ROUND(prefix_grade_grades.finalgrade, 0) AS finalgrade
, from_unixtime(prefix_grade_grades.timecreated) AS timecreated


FROM prefix_course AS course
left outer JOIN prefix_enrol AS en ON en.courseid = course.id
left outer JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
left outer JOIN prefix_user AS user2 ON ue.userid = user2.id
left outer join prefix_grade_grades  on user2.id = prefix_grade_grades.userid
left outer join   prefix_grade_items   on prefix_grade_grades.itemid = prefix_grade_items.id 

where course.id = '484'

ORDER BY lastname, firstname, itemmodule

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

Just a quick post for people reading this thread that are starting out on writing MySQL reports.

Our final "MySQL Reporting in Moodle" online course for this year starts 1st October.
(HRDNZ, Moodle Partner)

You can read the course outline here: http://www.moodlebites.com/mod/page/view.php?id=9112

As always, we will provide a 10% discount to anyone who is a participant here in this area, so just quote the Forum URL when signing-up, Stu smile

 
Average of ratings: -
Picture of Saran Gokul
Re: Useful SQL Queries?
 

Here is a query to display the list of courses with its category and number of enrolments, number of inprogress and number of completions for each course.


SELECT min(base.id) AS id, course.fullname AS course_courselink, 

course.id AS course_id, category.name as category_name,

COUNT(base.status) AS course_completion_status, 

SUM(CASE WHEN base.status = 25 THEN 1 ELSE 0 END) AS course_completion_isinprogress, 

SUM(CASE WHEN base.status = 50 OR base.status = 75 THEN 1 ELSE 0 END) AS course_completion_iscomplete 

FROM mdl_course_completions base 

LEFT JOIN mdl_course course ON course.id = base.course 

LEFT JOIN mdl_course_categories category ON course.category = category.id

WHERE ( 1=1 ) 

GROUP BY course.fullname, course.id, course.visible, course.audiencevisible 

order by category_name

 
Average of ratings: -
Picture of Daniel Wolff
Re: Useful SQL Queries?
 

Final Grades that have been modified in the last two weeks

Greeting fellow moodle SQLers

I thought I would share a query I've been working on to return final grades that have been updated in the last two weeks across a site or with a slight modification from a particular course or a different time period.

I drew inspiration from a number of posts in this thread and the documentation. I did make one small change which I was hoping to run by folks here. Several of the reports were referencing grade_items.timemodified but it seems I was receiving when the grade item (the assignment) was created (as all results were the same) so I adjusted that to look at grade_grades.timemodified. In my testing this appears to be functioning correctly but I am primarily testing against a course that has only one grade item besides the final grade.

This code was developed to be used in the plugin ad-hoc database query so you may need to adjust depending on what reporting tool you are using. Namely replacing the curly brackets with table names (so {course} would be mdl_course).

Would love to hear the communities thoughts, especially about any glaring errors in coding or logic :D

Cheers,

Dan

Code

SELECT
    u.email,
    u.firstname,
    u.lastname,
    c.fullname CourseName,
    cc.name CategoryName,
    ROUND(gg.finalgrade,2) Grade,
    FROM_UNIXTIME(gg.timemodified, "%m/%d/%Y") Date_Modified,
    FROM_UNIXTIME(gg.timemodified,"%H:%i:%s") as Time_modified
FROM
    {course} AS c
    JOIN {context} AS ctx ON c.id = ctx.instanceid
    JOIN {role_assignments} AS ra ON ra.contextid = ctx.id
    JOIN {user} AS u ON u.id = ra.userid
    JOIN {grade_grades} AS gg ON gg.userid = u.id
    JOIN {grade_items} AS gi ON gi.id = gg.itemid
    JOIN {course_categories} AS cc ON cc.id = c.category
WHERE
    gi.courseid = c.id
    AND gi.itemtype = 'course'
    AND from_unixtime(gg.timemodified) >= DATE_SUB(now(), INTERVAL 2 WEEK)

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

Here is cleaned up and slightly modified query with few comments:

SELECT
u.email,
u.firstname,
u.lastname,
c.fullname AS CourseName,
cc.name AS CategoryName,
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified, "%m/%d/%Y") AS Date_Modified,
FROM_UNIXTIME(gg.timemodified, "%H:%i:%s") AS Time_modified
FROM
mdl_course c
INNER JOIN mdl_context ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
INNER JOIN mdl_role_assignments ra ON ra.contextid = ctx.id AND ra.roleid = 5
INNER JOIN mdl_user u ON u.id = ra.userid AND u.deleted = 0
INNER JOIN mdl_grade_grades gg ON gg.userid = u.id
INNER JOIN mdl_grade_items gi ON gi.id = gg.itemid AND gi.courseid = c.id AND gi.itemtype = 'course'
INNER JOIN mdl_course_categories cc ON cc.id = c.category
WHERE
c.category <> 0
AND gg.timemodified >= UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL 2 WEEK)

  1. When comparing context of something make sure to check the context level as well
  2. When checking role assignments it is beneficial to add also check for actual role as it limits down the number of records needed to be checked. See what roles are gradable in your instance and add appropriate conditions.
  3. When dealing with user table always add check if user record is deleted or not.
  4. Do not ever apply conversion functions to a field in where condition. That will force database engine to apply it on every record when field being checked. Better put precalculated value on the other side of condition as that one is calculated once and preserved during execution.
  5. Do not use [tablename] AS [tablealias] as it is not considered portable and as such bad practice
  6. Do use [field] AS [fieldalias] as it is considered good practice



 
Average of ratings: Useful (3)
Picture of Daniel Wolff
Re: Useful SQL Queries?
 

Thank you for the feedback and code cleanup.

Quick question: how did you place the cleaned up code inside that box? I tried making a single cell table but that didn't seem to work.


I've added a line in my latest iteration right below ROUND(gg.finalgrade,2):

  IF (ROUND(gg.finalgrade,2) >= 80,'Yes' , 'No') AS Pass,

In your opinion is that all right or is it not ideal since it would cause (I think) every record to be checked.




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

To nicely format the code click on the paragraph style button and choose 'Pre-formatted'. That's it.

SELECT
u.email,
u.firstname,
u.lastname,
c.fullname AS CourseName,
cc.name AS CategoryName,
gg.finalgrade,
CASE WHEN gg.finalgrade >= 80 THEN 'Yes'
ELSE 'No'
END AS Pass,
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified, "%m/%d/%Y") AS Date_Modified,
FROM_UNIXTIME(gg.timemodified, "%H:%i:%s") AS Time_modified
FROM
mdl_course c
INNER JOIN mdl_context ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
INNER JOIN mdl_role_assignments ra ON ra.contextid = ctx.id AND ra.roleid = 5
INNER JOIN mdl_user u ON u.id = ra.userid AND u.deleted = 0
INNER JOIN mdl_grade_grades gg ON gg.userid = u.id
INNER JOIN mdl_grade_items gi ON gi.id = gg.itemid AND gi.courseid = c.id AND gi.itemtype = 'course'
INNER JOIN mdl_course_categories cc ON cc.id = c.category
WHERE
c.category <> 0
AND gg.timemodified >= UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL 2 WEEK)

This is how I would approach your need in a more standard way (CASE WHEN is SQL-92 compliant).


 
Average of ratings: Useful (4)
Work Photo 2014
Re: Useful SQL Queries?
 

Thanks to Dale for this useful query to report student engagement by course "with a row per course, counting the amount of students, student course views (hits) and the average view per student,"

I just used it and found it needed a little update for my Moodle 2.7 (because I'm using the new logging) - so here it is attached as CourseUsage.sql in case it's useful to anyone.

The second file CourseUsageAfterDate  has a line added to filter by date (I was only interested in engagement during the current academic year)

I've also attached a PNG to highlight my changes.

Carol



Picture of Daniel Wolff
Re: Useful SQL Queries?
 

Hello fellow moodlers,

We have been working with the first code snippet below to get all the forum post content from our courses. What we would also like is a query that separately totals each user posts in each forum in a given course and are having a really hard time getting it to work. Ideally we would want to specify only a few students for the report instead of the whole class. Output would be something like

UserID, Firstname, Lastname, CourseID, ForumName, PostsInForum

Any help or thoughts folks could share would be greatly appreciated.

Cheers,

Dan


All forum posts in a single course with discussion names:

    SELECT fp.id, 
           usr.id AS UserID,
           usr.firstname,
           usr.lastname,
           c.id AS CourseID,
           c.fullname AS CourseFullName,
           f.name as ForumName,
           fd.name AS DiscussionName,
           from_unixtime(fp.created, "%m/%d/%Y") AS DateofPost,
           from_unixtime(fp.created, "%H:%i.%s") AS TimeofPost, 
           from_unixtime(fp.modified, "%m/%d/%Y") AS PostModifiedDate,
           from_unixtime(fp.modified, "%H:%i.%s") AS PostModifiedTime,
           fp.subject,
           fp.message
      FROM mdl_forum_posts fp
INNER JOIN mdl_forum_discussions fd ON fp.discussion = fd.id
INNER JOIN mdl_forum f ON f.id = fd.forum
INNER JOIN mdl_course c ON f.course = c.id
INNER JOIN mdl_user usr ON fp.userid = usr.id
     WHERE EXISTS (SELECT 1
                     FROM mdl_user_enrolments ue
               INNER JOIN mdl_enrol e ON ue.enrolid = e.id
                    WHERE usr.id = ue.userid
                      AND e.courseid = f.course)
     AND c.id = 528
ORDER BY Discussionname, Dateofpost, Timeofpost


Our attempt that gets it to work for one student but it breaks if you remove the usr.id = 20295 line and returns the incorrect number of posts in a forum.

    SELECT fp.id, 
           usr.id AS UserID,
           usr.firstname,
           usr.lastname,
           c.id AS CourseID,
           c.fullname AS CourseFullName,
           f.name as ForumName,
           COUNT(fp.subject)
      FROM mdl_forum_posts fp
INNER JOIN mdl_forum_discussions fd ON fp.discussion = fd.id
INNER JOIN mdl_forum f ON f.id = fd.forum
INNER JOIN mdl_course c ON f.course = c.id
INNER JOIN mdl_user usr ON fp.userid = usr.id
     WHERE EXISTS (SELECT 1
                     FROM mdl_user_enrolments ue
               INNER JOIN mdl_enrol e ON ue.enrolid = e.id
                    WHERE usr.id = ue.userid
                      AND e.courseid = f.course)
     AND c.id = 545
       AND usr.id = 20295
GROUP BY Forumname
ORDER BY lastname, firstname, Forumname





 
Average of ratings: -
Picture of Steven Chippendale
Re: Useful SQL Queries?
 

I am a little new to Moodle (2.9) and very new to SQL. My institution wants to grade courses with certain benchmarks (like the old BSG add-on). I have begun designing queries which list courses which meet certain criteria. We will be rewarding courses which achieve a high percentage of students interacting, rather than just the amount of modules added to the course.

Here is a query which lists all courses with the following Modules (Module ID in brackets, although that appears to differ from install to install):
Database (4)
Feedback (5)
Forum (7)
Glossary (8)
IMS (9)
Lesson (12)
Quiz (14)
SCORM (16)
Wiki (19)
Workshop (20)
Respondus Quiz (23)
Blog (26)
Questionnaire (32)
Advanced Forum (41)
Assignment (53)

But only if there is any student completion:

SELECT mdl_course.id AS 'Moodle ID', mdl_course.shortname AS 'Course Code', mdl_course.shortname AS 'Course Title'

FROM mdl_course

JOIN mdl_course_modules

ON mdl_course.id = mdl_course_modules.course

JOIN mdl_course_modules_completion

ON mdl_course_modules.id = mdl_course_modules_completion.coursemoduleid

WHERE mdl_course_modules.module=4 OR mdl_course_modules.module=5 OR mdl_course_modules.module=7 OR mdl_course_modules.module=8 OR mdl_course_modules.module=9 OR mdl_course_modules.module=12 OR mdl_course_modules.module=14 OR mdl_course_modules.module=16 OR mdl_course_modules.module=19 OR mdl_course_modules.module=20 OR mdl_course_modules.module=23 OR mdl_course_modules.module=26 OR mdl_course_modules.module=32 OR mdl_course_modules.module=41 OR mdl_course_modules.module=53

AND mdl_course_modules_completion.completionstate = 1

GROUP BY shortname


If you want to try this out, you probably need to do a find-and-replace on all those mdl_ prefixes.


I will probably join the course_categories table, so that they're listed alongside their faculty, but my main aim is to join additional tables to only list courses if 75% or more enrolled students have completed 2 or more of these modules on a course. Any suggestions on how to do that would be appreciated.

 
Average of ratings: -
Picture of Mr Blue
Re: Useful SQL Queries?
 

Hey Guys,

Was wondering if anyone had created a query to generate a list of course sizes within a certain date period.

Also a list of courses that have not been accessed before a certain date.. i.e. they have become redundant ?


Thanks for the feedback !

 
Average of ratings: -
Picture of Daniel Wolff
Re: Useful SQL Queries?
 

Not a query but I use the course size report plugin to get the general feel of course sizes - its not a perfect accounting due to the way the Moodle file system works (check the documentation) but I find it sufficient to meet my needs.

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

Is there a way to show which students have or have not completed modules?

 
Average of ratings: -
Picture of usma virtual
Re: Useful SQL Queries?
 

Hi Daniel Wolff,


I have good day, a report in which I am taking few students entered the last 30 days, but within this consultation need to show me the role of the user, ie if a student or teacher.


or someone in this forum could assist me to run this query.


Hi Daniel Wolff,


I have good day, a report in which I am taking few students entered the last 30 days, but within this consultation need to show me the role of the user, ie if a student or teacher.


or someone in this forum could assist me to run this query.


this is my query:


SELECT

    AS u.id userid,

    u.firstname,

    u.lastname,

    FROM_UNIXTIME (l.TIME) AS rvisit,

    AS c.fullname rcourse,

    AS agg.days days,

    agg.numdates,

    agg.numcourses,

    agg.numlogs

 FROM

    mdl_log l INNER JOIN mdl_user or

        ON l.userid = u.id

    C INNER JOIN mdl_course

        ON l.course = c.id

    INNER JOIN (

        SELECT

            days,

            userid,

            MAX (TIME) AS maxtime,

            COUNT (DISTINCT DATE (FROM_UNIXTIME (TIME))) AS "numdates"

            COUNT (DISTINCT course) AS numcourses,

            COUNT (*) AS numlogs

        FROM

            mdl_log l c INNER JOIN mdl_course

                ON l.course = c.id

            INNER JOIN (

                AS SELECT 30 days

           ) var

        WHERE

            l.TIME> (unix_timestamp () - ((60 * 60 * 24) * days))

            AND c.format! = "Site"

        GROUP BY userid) agg

  ON l.userid = agg.userid

  WHERE

    l.TIME = agg.maxtime

    AND c.format! = "Site"

  GROUP BY userid

  ORDER BY DESC l.TIME



thank you very much and spend good day.


thank you very much and spend good day.

 
Average of ratings: -
Picture of Daniel Wolff
Re: Useful SQL Queries?
 

Usma,

Apologies I have been away and haven't checked in here recently. As you might be able to tell from my latest post below, I also have difficulty understanding how to include a learner's role on reports that include more information. I hope you were able to find a solution for your trouble and if you did, please share!

Cheers,

Dan

 
Average of ratings: -
Picture of Matt Long
Re: Useful SQL Queries?
 
Hi all!

We recently needed to come up with reports that would list the final course grade and each individual item grade for (potentially) all students using the mdl_grade_grades_history table. It's a modification of the current grades query in the Ad-Hoc Contributed Reports. Note that this is for grades of students who were formerly (not currently) enrolled in the course. We needed the info for cohort studies in accreditation reports. I'm fairly new to SQL and ended up having to exclude Course Categories due to the Group By statement. There's probably an easy fix for that, but this worked for what we needed. 

It's formatted for the Configurable Reports plugin, so replace prefix_ with mdl_ (or whatever prefix your tables have) if you're running it straight against the database. If you don't use the idnumber field, you can replace u.idnumber with u.id or just delete that part of the Select statement.

Feel free to mention any suggestions/improvements!

Matt


All Final Course Grades and Item Grades from Grades History Table

SELECT u.idnumber AS 'ID Number' , u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) 'DisplayName',
c.fullname AS 'Course',
cc.name AS 'Category',
gh.source AS 'Source', 

CASE
  WHEN gi.itemtype = 'course'
  THEN CONCAT(c.fullname, ' - Total')
   ELSE gi.itemname
END AS 'Item Name',

gh.finalgrade AS 'Grade',
MAX(FROM_UNIXTIME(gh.timemodified)) AS TIME
FROM prefix_course AS c
JOIN prefix_grade_items AS gi ON gi.courseid = c.id
JOIN prefix_grade_grades_history AS gh ON gh.itemid = gi.id
JOIN prefix_user AS u ON u.id = gh.userid
RIGHT JOIN prefix_grade_categories AS gc ON gc.courseid =  c.id
JOIN prefix_course_categories AS cc ON cc.id = c.category 

WHERE gi.courseid = c.id
AND gi.itemtype != 'category'
AND gh.source = 'userdelete'
/* Uncomment AND statement below to limit by course id */
/* AND c.id = 100 */

/* Uncomment AND statement below to limit by student idnumber(s). */
/* AND (u.idnumber = 100 OR u.idnumber = 110) */

/* Uncomment AND statement below to limit by grade item name(s). */
/* AND (gi.itemname LIKE '%Quiz%' OR gi.itemname LIKE '%Reading%') */

GROUP BY
   CASE
   WHEN gi.itemtype = 'course'
          THEN CONCAT(c.fullname, ' - Total')
      ELSE gi.itemname
  END,
  u.id

ORDER BY u.lastname

 
Average of ratings: -
Picture of Daniel Wolff
Re: Useful SQL Queries?
 

Hello everyone,

Our quest to get meaningful exports of forums for research and evaluation continues. Our latest iteration is below - which allows the export of all the forum posts from a course category including parent post information so the structure of the forum is captured as well. The one piece I am struggling with is capturing the poster's role in the course - any suggestions from you SQL experts or novices on how we might do this?

Cheers,

Dan

SELECT fp.id AS Post_id,
  u.id AS User_id,
  u.firstname AS First_name,
  u.lastname AS Last_name,
  c.category AS Course_category,
  c.id AS Course_id,
  c.fullname AS Course_Full_Name,
  f.name as Forum_name,
  fd.name AS Discussion_name,
  fp.parent AS Parent_post_id,
  from_unixtime(fp.created, "%m/%d/%Y") AS Date_of_post,
  from_unixtime(fp.created, "%H:%i.%s") AS Time_of_post, 
  from_unixtime(fp.modified, "%m/%d/%Y") AS Post_modified_date,
  from_unixtime(fp.modified, "%H:%i.%s") AS Post_modified_time,
  fp.subject AS Post_subject,
  fp.message AS Post_message
FROM mdl_forum_posts fp
  INNER JOIN mdl_forum_discussions fd ON fp.discussion = fd.id
  INNER JOIN mdl_forum f ON f.id = fd.forum
  INNER JOIN mdl_course c ON f.course = c.id
  INNER JOIN mdl_user u ON fp.userid = u.id
WHERE EXISTS
  (SELECT 1 FROM mdl_user_enrolments ue
    INNER JOIN mdl_enrol e ON ue.enrolid = e.id
      WHERE u.id = ue.userid
      AND e.courseid = f.course)
  AND c.category = 70
ORDER BY Discussion_name, Date_of_post, Time_of_post
 
Average of ratings: -
Picture of Sam Aut
Re: Useful SQL Queries?
 

Hi all,

I am writing queries for the report that shows the columns in a following order: Username, Firstname, Lastname, Country, Coursecategory, Coursesubcategory, Coursename, Finalgrade, Maxiumgrade, Minimumgrade, Description, Outcomeresult. I have a problem to show the first course categories in the report. Everything else works but I get an error every time I try to modify the query to show both course categories (first category and sub-category). Now I have only sub-categories in the report with the following SQL query: 

SELECT u.username,

       u.firstname,

       u.lastname,

       u.country,

       cc.name as CourseSubCategory,

       c.fullname as CourseName,

       gg.finalgrade,

       gg.rawgrademax as MaxiumGrade,

       gg.rawgrademin as MinimumGrade,

       o.description,

       om.result as OutcomeResult

FROM mdl_user u

JOIN mdl_user_enrolments ue ON (ue.userid = u.id)

JOIN mdl_enrol e ON (ue.enrolid = e.id)

JOIN mdl_course c ON (c.id = e.courseid)

LEFT JOIN mdl_course_categories cc ON (c.category = cc.id)

LEFT JOIN mdl_grade_items gi ON (gi.courseid= c.id)

LEFT JOIN mdl_grade_grades gg ON (gi.id = gg.itemid && u.id = gg.userid)

LEFT JOIN mdl_outcome_marks om ON (u.id = om.userid && c.id = om.courseid)

LEFT JOIN mdl_outcome o ON (om.outcomeid = o.id)

WHERE gi.itemtype = "Course"

Can anybody help me to modify the query so that both course categories will appear in the report?




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

Here is the updated query:

SELECT u.username,
u.firstname,
u.lastname,
u.country,
ccp.name AS CourseParentCategory, //added
cc.name AS CourseCategory,
c.fullname AS CourseName,
gg.finalgrade,
gg.rawgrademax AS MaxiumGrade,
gg.rawgrademin AS MinimumGrade,
o.description,
om.result AS OutcomeResult
FROM mdl_user u
JOIN mdl_user_enrolments ue ON (ue.userid = u.id)
JOIN mdl_enrol e ON (ue.enrolid = e.id)
JOIN mdl_course c ON (c.id = e.courseid)
LEFT JOIN mdl_course_categories cc ON (c.category = cc.id)
LEFT JOIN mdl_course_categories ccp ON (cc.parent = ccp.id) //this is added
LEFT JOIN mdl_grade_items gi ON (gi.courseid= c.id)
LEFT JOIN mdl_grade_grades gg ON (gi.id = gg.itemid AND u.id = gg.userid)
LEFT JOIN mdl_outcome_marks om ON (u.id = om.userid AND c.id = om.courseid)
LEFT JOIN mdl_outcome o ON (om.outcomeid = o.id)
WHERE gi.itemtype = "course"

Note that itemtype is compared to course not Course. You where lucky it worked since you use MySQL which by default uses case insensitive string comparison. On other databases it might fail.


 
Average of ratings: Useful (1)
Picture of Sam Aut
Re: Useful SQL Queries?
 

Thanks for the correction. Now it's working.

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

Darko, thank you for directing me to this query for the parent category and category connection.  What if I were interested in ONLY having the role of teacher (role id = 3) in my Moodle database.  

Would the below query addition work? Thanks for the help!

FROM mdl_user u
JOIN mdl_user_enrolments ue ON (ue.userid = u.id)
JOIN mdl_enrol e ON (ue.enrolid = e.id)
JOIN mdl_role_assignments ra ON (ra.userid = u.id)AND (ra.roleid = '3')
JOIN mdl_context ct ON (ct.id = ra.contextid) AND (ct.contextlevel = 50)
JOIN mdl_course c ON (c.id = e.courseid) AND (ct.id = ra.contextid)
JOIN mdl_role r ON (r.id = ra.roleid) AND (r.id = '3')
 
Average of ratings: -
Picture of Chris S
Re: Useful SQL Queries?
 

I actually found that this works for me:

FROM mdl_user u
JOIN mdl_role_assignments ra ON (ra.userid = u.id) AND (ra.roleid = '3')
JOIN mdl_context ct ON (ct.id = ra.contextid) AND (ct.contextlevel = 50)
JOIN mdl_course c ON (c.id = ct.instanceid) AND (ct.id = ra.contextid)
JOIN mdl_role r ON (r.id = ra.roleid) AND (r.id = '3')

Is it necessary to have the mdl_user_enrolments and mdl_enrol tables in the join?

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

Nope.

 
Average of ratings: -
Picture of usma virtual
Re: Useful SQL Queries?
 

Hi Darko,


I have good day, a report in which I am taking few students entered the last 30 days, but within this consultation need to show me the role of the user, ie if a student or teacher.


or someone in this forum could assist me to run this query.


this is my query:


SELECT

    AS u.id userid,

    u.firstname,

    u.lastname,

    FROM_UNIXTIME (l.TIME) AS rvisit,

    AS c.fullname rcourse,

    AS agg.days days,

    agg.numdates,

    agg.numcourses,

    agg.numlogs

 FROM

    mdl_log l INNER JOIN mdl_user or

        ON l.userid = u.id

    C INNER JOIN mdl_course

        ON l.course = c.id

    INNER JOIN (

        SELECT

            days,

            userid,

            MAX (TIME) AS maxtime,

            COUNT (DISTINCT DATE (FROM_UNIXTIME (TIME))) AS "numdates"

            COUNT (DISTINCT course) AS numcourses,

            COUNT (*) AS numlogs

        FROM

            mdl_log l c INNER JOIN mdl_course

                ON l.course = c.id

            INNER JOIN (

                AS SELECT 30 days

           ) var

        WHERE

            l.TIME> (unix_timestamp () - ((60 * 60 * 24) * days))

            AND c.format! = "Site"

        GROUP BY userid) agg

  ON l.userid = agg.userid

  WHERE

    l.TIME = agg.maxtime

    AND c.format! = "Site"

  GROUP BY userid

  ORDER BY DESC l.TIME



thank you very much and spend good day.


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

Your query looks really strange. Here is the cleaned up version:

    SELECT u.id AS userid,
u.firstname,
u.lastname,
FROM_UNIXTIME (l.time) AS rvisit,
c.fullname AS rcourse,
agg.days,
agg.numdates,
agg.numcourses,
agg.numlogs,
r.name AS role
FROM mdl_log l
JOIN mdl_user u ON l.userid = u.id AND u.deleted = 0
JOIN mdl_role_assignments ra ON ra.userid = u.id
JOIN mdl_context ct ON ct.id = ra.contextid AND ct.contextlevel = 50
JOIN mdl_course c ON c.id = ct.instanceid AND l.course = c.id AND c.format <> 'site'
JOIN mdl_role r ON r.id = ra.roleid
JOIN (
SELECT
30 AS days,
l1.userid,
MAX(l1.time) AS maxtime,
COUNT(DISTINCT DATE(FROM_UNIXTIME(l1.time))) AS numdates,
COUNT(DISTINCT l1.course) AS numcourses,
COUNT(*) AS numlogs
FROM mdl_log l1
JOIN mdl_course c ON l1.course = c.id
WHERE
l1.time > UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL 30 DAY)
AND
c.format <> 'site'
GROUP BY l1.userid
) agg ON l.userid = agg.userid
WHERE l.time = agg.maxtime
GROUP BY userid
ORDER BY l.time DESC



 
Average of ratings: Useful (1)
Picture of usma virtual
Re: Useful SQL Queries?
 

Hello Darko,


You rock,'re right my query was something strange, thank you cooperation.


For my part when you require any support, do not hesitate to ask.


I will mention you have this step consultations being, where it shows me the number of students per course, but I can not show this amount by group within each course.


this is my query:


SELECT c.id AS Course_ID,c.shortname AS Course_Shortname,c.fullname AS Course_fullname, r.name AS Role,COUNT(u.id) AS Count_Active_Users

FROM mdl_role_assignments ra

LEFT JOIN mdl_context cx ON (ra.contextid = cx.id)

LEFT JOIN mdl_course c ON (cx.contextlevel = 50 AND cx.instanceid = c.id)

LEFT JOIN mdl_user u ON (ra.userid = u.id)

LEFT JOIN mdl_role r ON (ra.roleid = r.id)

WHERE cx.contextlevel = 50

AND c.visible = 1

AND u.deleted = 0

AND u.suspended = 0

GROUP BY r.name, c.id

ORDER BY c.id ASC

******************************************************

I appreciate the support.

Have a nice day.

 
Average of ratings: -
Picture of usma virtual
Re: Useful SQL Queries?
 

Here is the working query, it is obvious that you mostly guess your SQL. Definitely recommend to get yourself some book or take a course to learn it better. It is wrong to guess anything.

*******************************************************

I understand what you mention, actually I work in the consultations being, but could not identify this point.

I thank infinamente contribution to resolve this query, if at some point you require any support whatever is not hesitate to mention it.

have an excellent day.

 
Average of ratings: -
Picture of Rey T
Re: Useful SQL Queries?
 

Can anyone help me in writing a query to retrieve students who have viewed a particular course home page? e.g. (moodle.com/course/view.php?id=2) . The following query displays everyone who have viewed the course but I need to narrow down the result to only those with student role in that course. (I'm using moodle 2.8.1)


SELECT `id`, `eventname`, `target` , `action`, `crud` , `contextid`, `contextlevel`, `contextinstanceid` , `userid` , `courseid` , FROM_UNIXTIME(`timecreated`) AS DATEE 

FROM `mdl_logstore_standard_log` 

WHERE  `courseid` = 2 AND `target` = 'course' 

AND `crud` = 'r'

ORDER BY DATEE desc

 
Average of ratings: -
Picture of Emmanuel Bakare
Moodle Query to get the list of Courses and their activities
 
I have been asking around for this query for a while now but nobody seem to have an idea. So I decided to write it. This query is one query that achieves the below listed
1.  Courses assigned to a user and the list of activities underneath the course (specific to that user)
2. It also display the list of completed and not completed courses (using Activity Completion)
Note:
-- remember to change the u.id=17, userid=17,c.id=3,cm.id=3 in the sql query to reflect the userid, username, courseid you want to display

SELECT u.id userID,u.username,c.id courseID, c.fullname CourseName,module.id ModuleID,module.name ModuleName,cm.section SectionID, cs.section,cm.instance InstanceID,
CASE module.name
WHEN 'assign' THEN (select name from mdl_assign WHERE id=instance)
WHEN 'assignment' THEN (select name from mdl_assignment WHERE id=instance)
WHEN 'book' THEN (select name from mdl_book WHERE id=instance)
WHEN 'chat' THEN (select name from mdl_chat WHERE id=instance)
WHEN 'choice' THEN (select name from mdl_choice WHERE id=instance)
WHEN 'data' THEN (select name from mdl_data WHERE id=instance)
WHEN 'feedback' THEN (select name from mdl_feedback WHERE id=instance)
WHEN 'folder' THEN (select name from mdl_folder WHERE id=instance)
/* WHEN 'forum' THEN (select concat(name,'  [',ModuleName,']') from mdl_forum WHERE id=instance)*/
WHEN 'forum' THEN (select name from mdl_forum WHERE id=instance)
WHEN 'glossary' THEN (select name from mdl_glossary WHERE id=instance)
WHEN 'imscp' THEN (select name from mdl_imscp WHERE id=instance)
WHEN 'label' THEN (select name from mdl_label WHERE id=instance)
WHEN 'lesson' THEN (select name from mdl_lesson WHERE id=instance)
WHEN 'lti' THEN (select name from mdl_lti WHERE id=instance)
WHEN 'page' THEN (select name from mdl_page WHERE id=instance)
WHEN 'quiz' THEN (select name from mdl_quiz WHERE id=instance)
WHEN 'resource' THEN (select name from mdl_resource WHERE id=instance)
WHEN 'scorm' THEN (select name from mdl_scorm WHERE id=instance)
WHEN 'survey' THEN (select name from mdl_survey WHERE id=instance)
WHEN 'url' THEN (select name from mdl_url WHERE id=instance)
WHEN 'wiki' THEN (select name from mdl_wiki WHERE id=instance)
WHEN 'workshop' THEN (select name from mdl_workshop WHERE id=instance)
WHEN 'checklist' THEN (select name from mdl_checklist WHERE id=instance)
WHEN 'attendance' THEN (select name from mdl_attendance WHERE id=instance)
WHEN 'certificate' THEN (select name from mdl_certificate WHERE id=instance)

ELSE 'other types'
END AS instanceName,
if(concat(cm.module,cm.instance) IN(
   
 SELECT  concat(cm.module,cm.instance) FROM `mdl_course_modules_completion` cmc
INNER JOIN mdl_course_modules cm ON cm.id=cmc.coursemoduleid
WHERE userid=17 
AND cm.course=3
   
    ),1, 0) as Completed

FROM mdl_user u
INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
INNER JOIN mdl_enrol e ON e.id = ue.enrolid
INNER JOIN mdl_course c ON e.courseid = c.id
INNER JOIN mdl_course_modules cm ON c.id=cm.course
INNER JOIN mdl_modules module ON module.id=cm.module
INNER JOIN mdl_course_sections cs ON cs.id=cm.section
WHERE u.id=17    /* show for only specific user=14, remove to show all users*/
 AND c.enablecompletion=1 /* show records with activity completion enabled. remove to show all courses*/
 AND cs.section !=0 /* This  cs.section!=0 removes the top general activities and resources that are not in any section. remove it to see all activities and resources*/
 AND c.id=3 /* show only course that as id 3, remove to see all course assigned to this user */
 ORDER BY  cs.section




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

Hello,

We often have teachers mis-using section summary, putting a lot of things (the whole content of the course...) in it sad

For the students (and the teacher), it can be fully functionnal, clean and usefull. But you can't have the detail of wich part of the course (ressource) has been seen, in the logs (and you can have other problems).

So, i've written a query to find "big" sections, with link to the courses involved, and direct link to modify these sections.

SELECT
  c.id
, c.visible AS Open
, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS Course
, concat('<a target="_new" href="%%WWWROOT%%/course/editsection.php?id=',cs.id,'">',cs.id,'</a>') AS Section
, cs.visible AS Visible
, length(cs.summary) AS Size
, SUBSTR(cs.summary, 1, 100) AS Summary
FROM prefix_course_sections AS cs
JOIN prefix_course AS c ON c.id=cs.course
WHERE length(cs.summary) > 1000
ORDER BY length(cs.summary) DESC

If (the beginning of) section content brings bad things on your screen, you can delete the line:

, SUBSTR(cs.summary, 1, 100) AS Summary
Hope this can help.

Séverin

PS : for french reading people, you can look at this discussion.

 
Average of ratings: Useful (1)
Picture of Emmanuel Bakare
Re: Useful SQL Queries?- Get Percentage completion - progress bar
 

==========================================================================================================

THIS QUERY LIST USERNAME, COURSENAME, TOTAL ACTIVITY, COMPLETED ACTIVITY -- USE THIS TO GET PERCENTAGE COMPLETION. YOU MUST SET ACTIVITY COMPLETION ON YOUR COURSES TO USE USE THIS.

REMEMBER TO CHANGE USER NAME FROM 'user4' TO THE USERNAME YOU WANT TO RETRIEVE REPORT FOR.

ALSO READ THE COMMENT ON THE QUERY TO GET YOUR DESIRED RESULT
.... thank me later

=========================================================================================================

SELECT  u.username,  c.id courseID, c.fullname CourseName,COUNT(c.fullname) totalActivities,  

    

sum(case when (if(concat(cm.module,cm.instance) IN(

    

 SELECT  concat(cm.module,cm.instance) FROM `mdl_course_modules_completion` cmc

INNER JOIN mdl_course_modules cm ON cm.id=cmc.coursemoduleid

INNER JOIN mdl_user us ON us.id=cmc.userid

WHERE us.username='user4'  

 

    

    ),1, 0))=1 then 1  ELSE 0 END) AS Completed 

FROM mdl_user u

INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id

INNER JOIN mdl_enrol e ON e.id = ue.enrolid

INNER JOIN mdl_course c ON e.courseid = c.id

INNER JOIN mdl_course_modules cm ON c.id=cm.course

INNER JOIN mdl_modules module ON module.id=cm.module

INNER JOIN mdl_course_sections cs ON cs.id=cm.section

WHERE u.username='user4'    /* show for only specific user=14, remove to show all users*/

 AND c.enablecompletion=1 /* show records with activity completion enabled. remove to show all courses*/

 AND cs.section !=0   /* Remove this portion if you want to also list the general activities on top of a course */

 GROUP BY c.fullname

 
Average of ratings: -
Picture of Matt Miller
Re: Useful SQL Queries?
 

Hello can someone help me out with a new SQL query.  I have looked all over the internet and found some useful reports but nothing that can currently help.

I would like a report that compares two different courses and the course_total grades for those courses.

This query should only show users that are enrolled in both courses.

user id | user full name | course id 1 | course full name 1 | course total 1 | course id 2 | course full name 2| course total 2|


Any help would be much appreciated!

Thank you.


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

Hi Matt

This intrigued me so thought I'd have a crack at it for you. I've hacked the following together although I'm not the biggest SQL expert...

SELECT 
	course1.user_id, course1.user_name, course1.course_id, course1.course_name, course1.course_grade, course2.course_id, course2.course_name, course2.course_grade
FROM (
	SELECT grades.userid user_id, CONCAT(user.firstname, ' ' ,user.lastname) user_name, course.id course_id, course.fullname course_name, grades.finalgrade course_grade
	FROM mdl_grade_items grade_items
	INNER JOIN mdl_grade_grades grades ON grade_items.id = grades.itemid
	INNER JOIN mdl_user user ON grades.userid = user.id
	INNER JOIN mdl_course course ON grade_items.courseid = course.id
	WHERE grade_items.courseid = XXXXX AND grade_items.itemtype = 'course'
) course1
INNER JOIN (
	SELECT grades.userid user_id, CONCAT(user.firstname, ' ' ,user.lastname) user_name, course.id course_id, course.fullname course_name, grades.finalgrade course_grade
	FROM mdl_grade_items grade_items
	INNER JOIN mdl_grade_grades grades ON grade_items.id = grades.itemid
	INNER JOIN mdl_user user ON grades.userid = user.id
	INNER JOIN mdl_course course ON grade_items.courseid = course.id
	WHERE grade_items.courseid = YYYYY AND grade_items.itemtype = 'course'
) course2 ON course1.user_id = course2.user_id

You'll need to replace the course IDs (shown above as XXXX and YYYY).

The query should bring out only the users who are common to both courses due to the INNER join in the outer query, but I guess this could be improved with a bit more attention.

It's been a while since I've dug into the grades tables so forgive me if this turns out to be incorrect, I've not got enough data in my current local development system to verify properly but seems OK from what I've tested.

This was from a Moodle 3.1 system but should be good for previous versions. Hope it at least gets you a bit further ahead smile

 
Average of ratings: -
Picture of Matt Miller
Re: Useful SQL Queries?
 

Thank you very much Dale for your response.  Unfortunately it is not working for me right now.  I am testing this on our STG moodle platform but I get the error message "No explicit prefix".  I am trying to learn SQL a little more to try and figure this out.

I have read that it needs to know which table to reference so I tried adding the prefix_course to the FROM clause but that did not work either.  I am working on this a little more as well.

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

Change all the table prefixes in Dale's code from mdl_ to prefix_

Moodle will convert it back to "mdl_" (or whatever table prefix you specified in $CFG->prefix in your config.php file when you installed Moodle).

SELECT ...
FROM prefix_grade_items grade_items
INNER JOIN prefix_grade_grades grades ON grade_items.id = grades.itemid
INNER JOIN prefix_user user ON grades.userid = user.id
etc ...

You never hardcode the "mdl_" table prefix in the SQL in your Moodle code. Always use "prefix_" in your code.

 
Average of ratings: Useful (1)
Picture of Dale Davies
Re: Useful SQL Queries?
 

Thanks Luis, just a little nitpick however so there is no confusion. 

If you are using the Configurable Reports or Ad-hoc Database Queries plugins you'll need to use prefix_ instead of mdl_.  In code however, using the Data Manipulation API, you may still require the full table name and should not change mdl_ to prefix_.

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

Good afternoon could help me with this

Hello can someone help me out with a new SQL query.

The courses are in use, some teachers use the courses and all courses and teachers moodle.

Thank you



 
Average of ratings: -
Picture of Bhavani R
Re: Useful SQL Queries?
 

Hello Everyone

I am new to moodle.. I am searching a query that retrieves the activities with grades of a particular course for enrolled user...

Plz let me know the query guys..




--Bhavani

 
Average of ratings: -
Picture of Bhavani R
Re: Useful SQL Queries?
 

Hello Everyone,


I am in need of following queries...Kindly let me know the queries.

1)List of newly added courses.

2)List of popular courses.

3)List of badges earned by the particular user.


--Bhavani

 
Average of ratings: -
Picture of Edmund Evangelista
Re: Useful SQL Queries?
 

Does anyone have an SQL Query to display the list of assignment in a course? The list should display the assignment name assigned by the teacher. I can count the number of assignment in a course but I cannot seem to find the table to display the assignment name and relate it to mdl_course_modules.

 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group Core developersGroup Particularly helpful Moodlers
This is what you need
    SELECT cm.id, ma.name
      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
INNER JOIN mdl_assignment     AS ma  ON mdl.name = 'assignment' AND cm.instance = ma.id





 
Average of ratings: -
Picture of baraa abd el-hady
Re: Useful SQL Queries?
 
can any one help me to get sql query that get all required grading assignment for all the courses i have the old one that not work now in moodle aover 2.7 i use moodle 3.1 this is the old sql query select `c`.`category` AS `ClassRoom`,`c`.`id` AS `CourseID`,`a`.`id` AS `AssignmentID`,`asb`.`id` AS `SubmissiontID`,`u`.`id` AS `UserID`,`c`.`fullname` AS `Course`,`a`.`name` AS `Assignment`,concat('',`a`.`name`,'') AS `Assignmentlink`,concat(`u`.`firstname`,' ',`u`.`lastname`) AS `UserName`,`asb`.`grade` AS `Grade`,`gi`.`grademax` AS `Max`,from_unixtime(`asb`.`timemodified`) AS `timemodified`,from_unixtime(`asb`.`timemarked`) AS `timemarked` from (((((`mdl_assignment_submissions` `asb` join `mdl_assignment` `a` on((`a`.`id` = `asb`.`assignment`))) join `mdl_user` `u` on((`u`.`id` = `asb`.`userid`))) join `mdl_course` `c` on((`c`.`id` = `a`.`course`))) join `mdl_course_modules` `cm` on((`c`.`id` = `cm`.`course`))) join `mdl_grade_items` `gi` on((`a`.`id` = `gi`.`iteminstance`))) where ((`cm`.`instance` = `a`.`id`) and (`cm`.`module` = 1) and ((`asb`.`timemodified` > `asb`.`timemarked`) or (`asb`.`grade` < 0)) and (((`asb`.`data1` is not null) and (`asb`.`data1` <> '')) or (`asb`.`numfiles` > 0)) group by `u`.`id`,`a`.`id` order by `c`.`category`,`c`.`id`
 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group Core developersGroup Particularly helpful Moodlers

If you want some help cleanup your SQL properly and post it inside Pre-Formatted paragraph so that we can actually see what is happening.

 
Average of ratings: -
Picture of baraa abd el-hady
Re: Useful SQL Queries?
 
SELECT
	`c`.`category` AS `ClassRoom`,
	`c`.`id` AS `CourseID`,
	`a`.`id` AS `AssignmentID`,
	`asb`.`id` AS `SubmissiontID`,
	`u`.`id` AS `UserID`,
	`c`.`fullname` AS `Course`,
	`a`.`name` AS `Assignment`,
	concat('', `a`.`name`, '') AS `Assignmentlink`,
	concat(
		`u`.`firstname`,
		' ',
		`u`.`lastname`
	) AS `UserName`,
	`asb`.`grade` AS `Grade`,
	`gi`.`grademax` AS `Max`,
	from_unixtime(`asb`.`timemodified`) AS `timemodified`,
	from_unixtime(`asb`.`timemarked`) AS `timemarked`
FROM
	(
		(
			(
				(
					(
						`mdl_assignment_submissions` `asb`
						JOIN `mdl_assignment` `a` ON (
							(
								`a`.`id` = `asb`.`assignment`
							)
						)
					)
					JOIN `mdl_user` `u` ON ((`u`.`id` = `asb`.`userid`))
				)
				JOIN `mdl_course` `c` ON ((`c`.`id` = `a`.`course`))
			)
			JOIN `mdl_course_modules` `cm` ON ((`c`.`id` = `cm`.`course`))
		)
		JOIN `mdl_grade_items` `gi` ON (
			(
				`a`.`id` = `gi`.`iteminstance`
			)
		)
	)
WHERE
	(
		(`cm`.`instance` = `a`.`id`)
		AND (`cm`.`module` = 1)
		AND (
			(
				`asb`.`timemodified` > `asb`.`timemarked`
			)
			OR (`asb`.`grade` < 0)
		)
		AND (
			(
				(`asb`.`data1` IS NOT NULL)
				AND (`asb`.`data1` <> '')
			)
			OR (`asb`.`numfiles` > 0)
		)
		GROUP BY
			`u`.`id`,
			`a`.`id`
		ORDER BY
			`c`.`category`,
			`c`.`id`

this is the old sql the new moodle database use new assign table the result i hope to get is to get all required graded submission

 
Average of ratings: -
Picture of Darko Miletić
Re: Useful SQL Queries?
Group Core developersGroup Particularly helpful Moodlers
Why is cm.module = 1 ? Is it not hardcoding? What if there is no course module with id 1?
 
Average of ratings: -
Picture of baraa abd el-hady
Re: Useful SQL Queries?
 

i did n't understand you well 

i want only to get the ungraded submission 

so i use the cm.module = 1 which mean to get only the assignments

this is not id it is the module type =1

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

And who guarantees that cm.module = 1 is assignment? You should never assume these things. In most systems assignment usually has id of 2 not 1.

 
Average of ratings: -
Picture of Richard Oelmann
Re: Useful SQL Queries?
Group Core developersGroup Particularly helpful MoodlersGroup Plugin developersGroup Testers

Another JOIN to mdl_modules to get the modules.id based on modules.name = 'assignment' (or 'assign' for an updated Moodle site)

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

This is the fixed query. You had incorrectly linked few tables and conditions. Also over complicating with parenthesis...

  SELECT   c.category AS ClassRoom
         , c.id AS CourseID 
         , a.id AS AssignmentID
         , asb.id AS SubmissiontID
         , u.id AS UserID
         , c.fullname AS Course
         , a.name AS Assignment
         , CONCAT('', a.name, '') AS Assignmentlink
         , CONCAT(u.firstname, ' ', u.lastname) AS UserName
         , asb.grade AS Grade
         , gi.grademax AS GradeMax
         , FROM_UNIXTIME(asb.timemodified) AS timemodified
         , FROM_UNIXTIME(asb.timemarked) AS timemarked
    FROM mdl_assignment_submissions asb
    JOIN mdl_assignment               a ON a.id = asb.assignment
    JOIN mdl_user                     u ON u.id = asb.userid AND u.deleted = 0
    JOIN mdl_course                   c ON c.id = a.course
    JOIN mdl_modules                 mo ON mo.name = 'assignment'
    JOIN mdl_course_modules          cm ON a.course = cm.course AND cm.instance = a.id AND cm.module = mo.id
    JOIN mdl_grade_items             gi ON a.course = gi.courseid AND gi.itemmodule = 'assignment' AND a.id = gi.iteminstance
   WHERE 
         ( asb.timemodified > asb.timemarked OR asb.grade < 0 ) 
         AND 
         ( (asb.data1 IS NOT NULL AND asb.data1 <> '') OR asb.numfiles > 0 )
GROUP BY u.id, a.id
ORDER BY c.category, c.id




 
Average of ratings: Useful (1)
Picture of baraa abd el-hady
Re: Useful SQL Queries?
 

thank you a lot  you really fix it but there is something 

even this new query it work on old moodle

i need the same result query but for the new moodle database structure

the table assignment replaced with mdl_assign


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

just replace assignment with assign in the query


Actually this is quite different. Well look into database and try to understand what is stored and where.

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

This is a start of query, you should figure out rest of the tables and conditions

   SELECT   c.category AS ClassRoom
          , c.id AS CourseID 
          , a.id AS AssignmentID
          , asb.id AS SubmissiontID
          , u.id AS UserID
          , c.fullname AS Course
          , a.name AS Assignment
          , CONCAT('', a.name, '') AS Assignmentlink
          , CONCAT(u.firstname, ' ', u.lastname) AS UserName
          , ag.grade AS Grade
          , gi.grademax AS GradeMax
          , FROM_UNIXTIME(asb.timemodified) AS timemodified
     FROM mdl_assign_submission asb
     JOIN mdl_assign              a ON a.id = asb.assignment
     JOIN mdl_assign_grades      ag ON ag.assignment = a.id AND asb.userid = ag.userid
     JOIN mdl_user                u ON u.id = asb.userid AND u.deleted = 0
     JOIN mdl_course              c ON c.id = a.course
     JOIN mdl_modules            mo ON mo.name = 'assign'
     JOIN mdl_course_modules     cm ON a.course = cm.course AND cm.instance = a.id AND cm.module = mo.id
     JOIN mdl_grade_items        gi ON a.course = gi.courseid AND gi.itemmodule = 'assign' AND a.id = gi.iteminstance
 GROUP BY u.id, a.id
ORDER BY c.category, c.id



 
Average of ratings: -
Picture of baraa abd el-hady
Re: Useful SQL Queries?
 
thanks a lot really for your work
i have modify it a Little and add the conditions it is now work fine
for the entire course or all the course if remove the course_id
 
 SELECT   c.category AS ClassRoom
          , c.id AS CourseID 
          , a.id AS AssignmentID
          , asb.id AS SubmissiontID
          , u.id AS UserID
          , c.fullname AS Course
          , a.name AS Assignment
          , CONCAT('', a.name, '') AS Assignmentlink
          , CONCAT(u.firstname, ' ', u.lastname) AS UserName
          , ag.grade AS Grade
          , gi.grademax AS GradeMax
          , FROM_UNIXTIME(asb.timemodified) AS timemodified
     FROM mdl_assign_submission asb
     JOIN mdl_assign              a ON a.id = asb.assignment 
     LEFT JOIN mdl_assign_grades      ag ON ag.assignment = a.id 
                                    AND asb.assignment = ag.assignment 
                                    AND asb.userid = ag.userid
                                    AND asb.attemptnumber = ag.attemptnumber
     JOIN mdl_user                u ON u.id = asb.userid AND u.deleted = 0
     JOIN mdl_course              c ON c.id = 32 AND c.id = a.course
     JOIN mdl_modules            mo ON mo.name = 'assign'
     JOIN mdl_course_modules     cm ON a.course = cm.course AND cm.instance = a.id AND cm.module = mo.id
     JOIN mdl_grade_items        gi ON a.course = gi.courseid AND gi.itemmodule = 'assign' AND a.id = gi.iteminstance
WHERE
	asb.latest = 1

AND asb.timemodified IS NOT NULL
AND asb. STATUS = 'submitted'
AND (
	asb.timemodified >= ag.timemodified
	OR ag.timemodified IS NULL
	OR ag.grade IS NULL
) 
 GROUP BY u.id, a.id
ORDER BY u.id,c.category, c.id


 
Average of ratings: -
Picture of Ned Stark
Re: Useful SQL Queries?
 
Average of ratings: -