Databases: Query to show all students and his courses

Databases: Query to show all students and his courses

by Ângelo Rigo -
Number of replies: 23

Hi

I need to show all students with all the courses where they are enrolled.

My attempt below does not show a list of courses alphabetically and not the list of students from that course.

How can it be done ?

Thank´s in advance 

SELECT c.fullname, usr.firstname, usr.lastname, usr.email
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
INNER JOIN mdl_user usr ON ra.userid = usr.id
AND ra.roleid = '5'
WHERE cx.contextlevel = '50'
GROUP BY c.id, c.shortname
ORDER BY c.fullname, usr.firstname

Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Query to show all students and his courses

by Gert Sauerstein -

Is this query what you want?

SELECT c.fullname, usr.firstname, usr.lastname, 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'
ORDER BY c.fullname, usr.firstname

Average of ratings: -
In reply to Gert Sauerstein

Re: Databases: Query to show all students and his courses

by Ângelo Rigo -
Hi gert thank´s for your response that is what i need!

One thing i do not see yet is what cx.contextlevel = '50' means what is the contexlevel collumn "role" and what the value 50 means!

A big thank´s


Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Query to show all students and his courses

by Gert Sauerstein -

The 50 means that the context where to search is a course context (CONTEXT_COURSE). This constant is defined in source code:

define('CONTEXT_COURSE', 50);

Average of ratings: -
In reply to Gert Sauerstein

Re: Databases: Query to show all students and his courses

by Deleted user -
So,

if there is a nice constant defined,
why is everybody then using 50 and not the constant CONTEXT_COURSE.

Ries

Average of ratings: -
In reply to Deleted user

Re: Databases: Query to show all students and his courses

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Because the constant is only defined in Moodle PHP code. Not if you want to copy and paste the query into PHPMyAdmin.

In PHP code, you should always use the constant. And {$CFG->prefix} instead of a hard-coded mdl_, and so on.
Average of ratings: -
In reply to Gert Sauerstein

Re: Databases: Query to show all students and his courses

by Evan Donovan -

Apologies if this is taking things on a tangent: What is the relationship between the enrollment data and the role data in the database? I have a student that shows only one course with a role, but shows a whole bunch with enrollments.

This is when contrasting:

select c.id, c.visible, e.id as enrolid, c.shortname from mdl_course c join mdl_enrol e on e.courseid = c.id join mdl_user_enrolments mue on mue.enrolid = e.id where mue.userid = 882

with

SELECT c.fullname, usr.firstname, usr.lastname, 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.firstname = "Firstname" and usr.lastname = "Lastname"
ORDER BY c.fullname, usr.firstname

If I give this student back the Student role in the courses in question, will his work be lost?

Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Query to show all students and his courses

by Peter Easton -
Thanks for this script it’s given a whirlwind of idea
How could I mod it so that I can feed it a course ID and get only the students in that course.
Or better yet how could I mod so I could give it a username return only the course that user is enrolled in.

Thanks again
Pete
Average of ratings: -
In reply to Peter Easton

Re: Databases: Query to show all students and his courses

by Ângelo Rigo -
Hi Peter

Let´s create this mod ?

For students from a given course (let´s say for eg. course id=229) can we use :

SELECT usr.firstname, usr.lastname, usr.email, c.fullname
FROM mdl_course c
INNER JOIN mdl_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50' and c.id=229
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'
ORDER BY usr.firstname, c.fullname

For courses where a user is enrolled we can use :


SELECT c.fullname, usr.firstname, usr.lastname, 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.id =150
ORDER BY c.fullname, usr.firstname
LIMIT 0 , 30

May we can use a hidden layer to show up when we pass the mouse over the user or over the course?

And/or also open a pop up window with the users/courses?

Where did you think is the best place to show this information?

Cheers.


Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Query to show all students and his courses

by Peter Easton -
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 = 'Teachers'
AND usr.username = 'peaston'
ORDER BY c.fullname, usr.lastname

which works great now i'm going to put in in a php wrapper and using the webproxy portlet of uPortal to display your currently enrolled courses with links to them.

thanks again
Pete
Average of ratings: -
In reply to Peter Easton

Re: Databases: Query to show all students and his courses

by Peter Easton -
Ok i've got just about everything working using the sql statement posted earlier using php but, yes there is always a but...
if i run the sql code in phpMyAdmin it returns the course id but when i run it in mysql the user id field over writes the course id field.
any ideas on how to fix this?
Pete
Average of ratings: -
In reply to Peter Easton

Re: Databases: Query to show all students and his courses

by Gert Sauerstein -
Is this because of bug MDL-10787? You must specify the retrieved fields in your sql query in the correct order. The first field will be estimated as the key by which the records are indexed. So it must be unique (yes, I know you might have a query where it mighty NOT be possible to find a unique key).

So this bug is very nasty and a big problem in ddllib.php. I wrote my own ddl library as a workaround for this bug. I can send it to you if you want.
Because many libraries are affected the moodle development team won't fix this bug in the next time sad --> please vote for it wink

Yours, Gert

PS: If I am wrong with the reason for your problems, please post your query here. Maybe we will find a mistake and correct it wink
Average of ratings: -
In reply to Gert Sauerstein

Re: Databases: Query to show all students and his courses

by A K -
Not sure if I've misunderstood this
'You must specify the retrieved fields in your sql query in the correct order.'
In an SQL query (SELECT statement) there are no constraints on the order in which you specify columns. A user might wish to generate results in a particular order, for their own convenience, and has total freedom in their choice. The order in which they are stored in tables is independent of display order for any particular query.

Also, not quite clear what you mean by
'The first field will be estimated as the key by which the records are indexed.'
Indexes are set up explicitly as part of a table definition. They aren't estimated / assumed by the RDBMS (as far as I know). Again, this is nothing to do with the display order in a query.

I wonder, might you be confusing the mandatory uniqueness within table indexes and the uniqueness, or otherwise, of column names in a query (see my other posting) which determines whether a column actually appears in a query result.

Not trying to be 'picky', but trying to avoid confusion for readers (often being a reader myself!)
Average of ratings: -
In reply to A K

Re: Databases: Query to show all students and his courses

by Gert Sauerstein -

You must specify the retrieved fields in your sql query in the correct order.

Of course the there are no field order constraints in plain SQL statements. But if you write a module (aren't you?) you have to know that the Moodle DDL library functions (such as get_records_sql()) require a unique key field in a query to be specified FIRST. So you may use an user ID field here, for example. This is due to a constraint in dmllib.php.
If you dont't care about this you maybe will have strange effects like missing records etc. as described by Peter.

If you are NOT using dmllib.php to execute your query, you won't have any trouble since you can use your plain SQL statement with any field order as said in your comment.

Average of ratings: -
In reply to Peter Easton

Re: Databases: Query to show all students and his courses

by A K -
It looks as if a small but important detail was omitted from the first line of the query. Instead of
SELECT c.fullname, c.id, usr.firstname, usr.lastname, usr.id, usr.username, usr.email
try
SELECT c.fullname, c.id cid, usr.firstname, usr.lastname, usr.id uid, usr.username, usr.email

The column list in the first SELECT statement includes two identically named columns from different tables. Each column name in the result must be unique, so SQL will only include one of them in the result. To prevent conflicting (i.e. identical) column names you give a new name to the result columns by adding the new output column names after the input column names, separated by a space. (You only need to rename one of them, but it's clearer if you rename both to make it obvious in the output which is the course Id and which is the user Id).

Note that the other output column names (fullname, firstname, lastname, username, email) do not include any duplicates and so are not 'lost' in the output.

You can, in fact, rename any output columns, however you only need to rename them when names clash (i.e. when column names match across different input tables). This is standard SQL.
E.g.
SELECT c.fullname aFullName, c.id aCourseId, usr.firstname aFirstName, usr.lastname aLastName, usr.id aUserId, usr.username aUserName,

Average of ratings: -
In reply to A K

Re: Databases: Query to show all students and his courses

by Gert Sauerstein -

SELECT c.fullname, c.id cid, usr.firstname, usr.lastname, usr.id uid, usr.username, usr.email

If you are using the moodle DDL libraries please use the AS operator to rename the columns because your code above is not platform independent:

SELECT c.fullname, c.id AS cid, usr.firstname, usr.lastname, usr.id AS uid, usr.username, usr.email

See also http://docs.moodle.org/en/Development:Coding, paragraph 11.

Average of ratings: -
In reply to Gert Sauerstein

Re: Databases: Query to show all students and his courses

by A K -
'Of course' wink. My posting was not homing in on the minutiae of SQL standards compliance (some dialects require 'AS', others don't), but on the general SQL requirement to qualify all column names which are duplicated across tables by prepending the name of the table, i.e. on the cause of the problem described.
Average of ratings: -
In reply to A K

Re: Databases: Query to show all students and his courses

by Ângelo Rigo -
Hi

How can i have the number of students that below to courses inside a given category and all its subcategorys ?

Wich tables must i relate with each other ?
I know there is course_categories, but how this table is related to users and course table ?

Course has the category collumn, and course categories has id and parent collumns, how can i relate to for eg. retrieve a count for students from courses that belong to courses that belong to a category with id = 1 ?

SELECT count(DISTINCT u.id)
FROM mdl_user u
INNER JOIN mdl_role_assignments ra on ra.userid = u.id
INNER JOIN mdl_role r on ra.roleid = r.id
--INNER JOIN What ?=What? (How we see all users below a given category ?)
WHERE r.shortname = 'student'

Thank´s in advance3
Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Query to show all students and his courses

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
INNER JOIN mdl_context con on ra.contextid = con.id
INNER JOIN mdl_course c on con.instanceid = c.id AND con.contextlevel = 50

are probably the missing steps you need.
Average of ratings: -
In reply to Tim Hunt

Re: Databases: Query to show all students and his courses

by Ângelo Rigo -
Thank´s

Now i see that Course and Context tables relates each other by the id and instanceid collumns. Role_assignment and Context tables relates by the contextid and id collumns.

How can i say : give me the total numbers os students os each subcategory from a given category ?
eg.: All students from graduation category. Or all courses from graduation category. Where Graduation category can have just subcategorys and each subcategory will have more subcategorys with some courses inside?

How can i use the information of the path collumn ?

I have done it by explicitely passing the category id i need that information :

i would like to pass just the parent id of all these categorys just one time at the query.

For students:
SELECT
cc.id,
cc.name,
COUNT( us.userid ) AS students
FROM
mdl_course_categories cc
LEFT OUTER JOIN
mdl_course c ON cc.id = c.category
LEFT OUTER JOIN
mdl_user_students us ON us.userid = u.id AND us.course=c.id
WHERE
cc.id =23
OR cc.id =74
OR cc.id =465
OR cc.id =447
OR cc.id =202
OR cc.id =411
GROUP BY
cc.id, cc.name
ORDER BY
cc.name

For courses:
SELECT cc.id, cc.name, COUNT( c.id ) AS courses
FROM mdl_course_categories cc
LEFT OUTER JOIN mdl_course c ON cc.id = c.category
LEFT OUTER JOIN mdl_user_students us ON us.course = c.id
LEFT OUTER JOIN mdl_user u ON u.id = us.userid
WHERE cc.id =43
OR cc.id =54
OR cc.id =132
OR cc.id =122
GROUP BY cc.id, cc.name
ORDER BY cc.name

Thank´s in advance


Average of ratings: -
In reply to Ângelo Rigo

Re: Databases: Query to show all students and his courses

by Paul Davis -

Hi guys, to get the parent category of a sub category in one query, add this to your select statement where you are displaying the columns you want to show. Its realy just the parentId and parentCategory column that is needed, but I included the others so you can actually see the relevant data to match it back.

`mdl_course_categories`.`name` AS `category`, `mdl_course_categories`.`path` AS `path`,  trim(leading '/' from substring_index(`mdl_course_categories`.`path`,'/',2)) AS `parentId`,(select `mdl_course_categories`.`name` from `mdl_course_categories` where (`mdl_course_categories`.`id` = `parentId`)) AS `parentCategory` 

Average of ratings: -
In reply to Paul Davis

Re: Databases: Query to show all students and his courses

by jj v -

All

This discussion let me start thinking about a global grade report. Is it difficult to show also the grades in such a report as discussed above? Should be a miracle, regarding all the post I've read, written by desperate Moodle admins  cool 

cheers

JaapJan

Average of ratings: -
In reply to jj v

Re: Databases: Query to show all students and his courses

by Les Culver -
Is there a way to get students' courses to appear in their profile? I know there is suppose to be a field "My courses", but it doesn't show. Also, I uploaded a group of students with a specified "course1" field, but it's not showing up anywhere. Thanks.
Average of ratings: -
In reply to Les Culver

Re: Databases: Query to show all students and his courses

by james mergenthaler -

If you browse to a student profile, their courses are listed as part of that.

http://moodle_site_address/user/profile.php?id=xxx

IF you followed the example in this page

http://docs.moodle.org/23/en/Upload_users , look at the heading Valid upload file for testing, you should be able to see the students enrolled in the course.

I would check first that the students accounts were created by browsing to

http://moodle_site_address/admin/user.php

IF there account is there and when you click on their name and do not see any course listed in the profile, then your upload process did not successfully enroll them. 

Average of ratings: -