How can i get this three things
- list of courses to which the student is registered
- list of courses avaliable to student
- list of courses that student of certan class should participate
I don't need whole SQL queries just some tips which tables and fileds to look and how they connect.
Many thanks to you all.
Databases: Some database queries about students and courses
by Bostjan Primozic -
Number of replies: 10
In reply to Bostjan Primozic
Re: Databases: Some database queries about students and courses
by tony chesney -
This will get you students and the (comma separated) courses they've been added to (I think that corresponds to the 1st *or* 2nd of your queries, it depends on the authentication method / how you add students (I think)):
SELECT u.id, GROUP_CONCAT( co.fullname )
FROM mdl2_user u
JOIN mdl2_role_assignments ra ON u.id = ra.userid
JOIN mdl2_role r ON r.id = ra.roleid
JOIN mdl2_context c ON ra.contextid = c.id
JOIN mdl2_course co ON c.instanceid = co.id
WHERE r.id = 5
GROUP BY u.id
ORDER BY u.id
(assuming 5 is the id for "student" in your role table).
In reply to tony chesney
Odg: Re: Some database queries about students and courses
by Bostjan Primozic -
Thanks for the replay it's very useful.
I am using version 1.9 and i thik the only diference in this case is in table names( names are without number 2 ).
What about third querie ? Can i determine to which course and grade student belong and what are the lectures he should/could participate in ?
Explanation for easier undestanding what i mean:
we have two courses, each having a four grades/years(i don't know what is the right name).
For each course and year we have different set of lectures. Can i get the list of this lectures for certain student ?
Best regards, Boštjan
I am using version 1.9 and i thik the only diference in this case is in table names( names are without number 2 ).
What about third querie ? Can i determine to which course and grade student belong and what are the lectures he should/could participate in ?
Explanation for easier undestanding what i mean:
we have two courses, each having a four grades/years(i don't know what is the right name).
For each course and year we have different set of lectures. Can i get the list of this lectures for certain student ?
Best regards, Boštjan
In reply to tony chesney
Odg: Re: Some database queries about students and courses
by Bostjan Primozic -
@dave ches
Can you please tell a bit more about your query ?
What is the MDL_CONTEXT table and its filed instanceid ?
I don't get it how are MDL_COURSE, MDL_CONTEXT, MDL_ROLE and MDL_ROLE_ASSIGMENTS connected.
Best regards, Bostjan.
Can you please tell a bit more about your query ?
What is the MDL_CONTEXT table and its filed instanceid ?
I don't get it how are MDL_COURSE, MDL_CONTEXT, MDL_ROLE and MDL_ROLE_ASSIGMENTS connected.
Best regards, Bostjan.
In reply to Bostjan Primozic
Re: Odg: Re: Some database queries about students and courses
by tony chesney -
Course: id < -> Context:instanceid (a course has a context)
Context:id < -> Role_assignments:contextid
for each user who has a role (student, teacher) in that context (course), there is a record in Role_assignments.
This record includes a userid and a roleid. userid maps to user:id. Roleid maps to role:id.
See also http://docs.moodle.org/en/Development:Database_schema_introduction
In reply to tony chesney
Re: Odg: Re: Some database queries about students and courses
by Tim Hunt -
Course: id < -> Context:instanceid (a course has a context) only applies if context.contextlevel = 50.
In reply to Tim Hunt
Re: Odg: Re: Some database queries about students and courses
by tony chesney -
Very true thanks.
In reply to Tim Hunt
Odg: Re: Odg: Re: Some database queries about students and courses
by Bostjan Primozic -
So if I get it right
If Context:contextlevel = 50 then
Context:id < -> Role_assignments:contextid
Context:instanceid < - > Course:id
But what Context:id and Context:instanceid menas when Context:contextlevel is 10,20,30,40 and 80 ?
If Context:contextlevel = 50 then
Context:id < -> Role_assignments:contextid
Context:instanceid < - > Course:id
But what Context:id and Context:instanceid menas when Context:contextlevel is 10,20,30,40 and 80 ?
In reply to Bostjan Primozic
Re: Odg: Re: Odg: Re: Some database queries about students and courses
by Tim Hunt -
It is always the case that
context:id < -> role_assignments:contextid
There is only one context with level 10, the system context.
level 20 does not exist.
level 30 is user context. context:instanceid < - > user:id
level 40 is course category context. context:instanceid < - > course_categories:id
Level 70 is module context. context:instanceid < - > course_modules:id
Level 80 is block context. context:instanceid < - > block_instances:id
context:id < -> role_assignments:contextid
There is only one context with level 10, the system context.
level 20 does not exist.
level 30 is user context. context:instanceid < - > user:id
level 40 is course category context. context:instanceid < - > course_categories:id
Level 70 is module context. context:instanceid < - > course_modules:id
Level 80 is block context. context:instanceid < - > block_instances:id
In reply to Tim Hunt
Re: Odg: Re: Odg: Re: Some database queries about students and courses
by Calvin Wang -
Thanks,this really helps.Today,I'm trying to use the moodle_role_assign web-service function and need to know what context level is.Does this mean every user record has its context record with level:30 in this table?Or is there any other mechanism?
In reply to Bostjan Primozic
Re: Odg: Re: Odg: Re: Some database queries about students and courses
by tony chesney -
Correct.
The context levels are set up as constants in /lib/accesslib.php, because they can't be added to or changed by the user, and for reasons explained here.
There's no way of knowing where they come from, though, when you are following things through in the database and find yourself staring at the contextlevel field.