Syntax for pulling profile fields on current user

Syntax for pulling profile fields on current user

by Chris H -
Number of replies: 6

Hi All -

I've looked at the documentation and perused a number of other posts, but I cannot seem to find a solution for my scenario. I want to provide a "special" user in a course the ability to run ad-hoc reports queries. Therefore I need the ad-hoc queries to dynamically use information from the "special" user's profile (i.e. their institution). For example, the ad-hoc report I want to create would let the special user see all students from their institution in a course (and their respective grade). 

Problem is, unless I hard code the institution it doesn't seem to dynamically assign based on the special user logged in.

SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course'
FROM prefix_course AS c
JOIN prefix_user AS u
WHERE u.institution='%%USERID%%'
ORDER BY `Name` ASC

The above query, when run by the "special" user will not show any report results. If I hard-code an institution value, then I get users from that institution; but I want it to be dynamic since I have special users from multiple institutions. I'm sure what I'm failing to do is fairly simple, but it's been a while since I've written in MySQL (and I do realize the above query would not give me grades, I'm just trying to get it to pull the correct users first). 

Average of ratings: -
In reply to Chris H

Re: Syntax for pulling profile fields on current user

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

I am pretty sure that you cannot program configurable reports to dynamically fill in parts of a query dependent on the person running the report.   It is just running as sql query - any variables are going to relate to the query not the person executing it.

I think you would have to create the report specifically for your special user and then give them permission to run it.

Average of ratings: -
In reply to Emma Richardson

Re: Syntax for pulling profile fields on current user

by Chris H -

Hm. I'm confused then, because the %%USERID%% field will pull in the userid of whoever is currently running the query (as far as I understand it). I figured there would be a way to combine this %%USERID%% field with the institution field to build a unique query for each special user. 

Average of ratings: -
In reply to Chris H

Re: Syntax for pulling profile fields on current user

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

My mistake - I did not know that was built into it.  Perhaps you could do a join to join to the user table with the userid variable and then pull the institution.  Or something like - u.institution WHERE userid=%%USERID%%

Average of ratings: -
In reply to Emma Richardson

Re: Syntax for pulling profile fields on current user

by Chris H -

I think I ended up finding an alternate solution, but I'd definitely still appreciate any feedback...

I ended up using a nested query...

WHERE gi.courseid = c.id AND u.institution = (SELECT DISTINCT u.institution FROM prefix_user AS u where u.id = %%USERID%%) 
AND c.id = %%COURSEID%%

With the above, I can now present the "special" student with a list of users from only their location, and for the current course being viewed. 

Average of ratings: -
In reply to Chris H

Re: Syntax for pulling profile fields on current user

by Luis de Vasconcelos -
Picture of Particularly helpful Moodlers

Will using a subquery work for you?

SELECT u.firstname AS 'Name', u.lastname AS 'Surname', c.fullname AS 'Course'
FROM prefix_course AS c
JOIN prefix_user AS u
WHERE u.institution=(SELECT institution FROM prefix_user WHERE prefix_user.id = '%%USERID%%')
ORDER BY 'Name' ASC

Your origional code:

WHERE u.institution='%%USERID%%'
did not give you the expected results because you can't pass %%USERID%% to the u.institution field. %%USERID%% is the logged in user's mdl_user.id, not the user's institution name.
Average of ratings: -
In reply to Chris H

Re: Syntax for pulling profile fields on current user

by Luis de Vasconcelos -
Picture of Particularly helpful Moodlers
When you hard-code the institution into your query does the query really work as you expect it to? You can't join the prefix_course table onto the prefix_user table like in your query:

...
FROM prefix_course AS c
JOIN prefix_user AS u
...
There is no direct relationship between the prefix_course and the prefix_user tables.

Average of ratings: -