Hi everyone,
Just wondering if there is an admin report that shows Parent/Child relationships site-wide.
For example:
Something like this:
Homer's Children:
Bart's Parents:
We are planning on having quite a few "Parent" users soon, and it would be nice to easily keep track of it all.
Thanks!
Oh, I forgot - we are on Moodle 2.2.1+
Even a bit of SQL would be useful... SELECT users who have parents... or something ;)
I'll continue exploring...
Thanks again!
Getting somewhere...
# Show users who have the 'Parent' role
select username from mdl_user where id = (select userid from mdl_role_assignments where roleid = (select id from mdl_role where shortname = 'parent'))
The next step is to find out who their 'children' are...
A very polite *BUMP*
First I am going to rewrite your query
select username from mdl_user where id = (select userid from mdl_role_assignments where roleid = (select id from mdl_role where shortname = 'parent'))
to use joins instead of nested subqueries
SELECT username
FROM mdl_user user
JOIN mdl_role_assignments ra ON ra.userid = user.id
JOIN mdl_role role ON role.id = ra.roleid
WHERE role.shortname = 'parent'
Now to add the link to the child
SELECT user.username AS parent, child.username AS child
FROM mdl_user user
JOIN mdl_role_assignments ra ON ra.userid = user.id
JOIN mdl_role role ON role.id = ra.roleid
JOIN mdl_context ctx ON ctx.id = ra.contextid AND ctx.contextlevel = 30
JOIN mdl_user child ON child.id = ctx.instanceid
WHERE role.shortname = 'parent'
I have not tested that, so there may be typos, but it is about right.
Thanks Tim!
That's exactly what I was looking for.
Is there a way for those of us who are query-language-challenged to produce this kind of report? Or is that something we need to ask for in Version 2.5?
Thanks from here too. This was what I was looking for. I had something, but your query is more clear, especially with the use of the table alias child on the second use of the user table.
Tim
I just want to say that this was exactly what I was looking for as well. Many thanks for all of your help
Paul
