SQL : Activity completion AND custom profile fields for a specific course

SQL : Activity completion AND custom profile fields for a specific course

by Salomé Desanges -
Number of replies: 4

Hi,

I am a beginner in SQL reports and I want to get Profile fields information and Activity completion on a report for one specific course.

I have the SQL for the custom profile fields :

SELECT u.firstname as 'prénom', u.lastname as 'nom', (SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'jobhager' AND UID.userid = u.id) as 'Métier',

(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'region' AND UID.userid = u.id) as 'Région',

(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'uv' AND UID.userid = u.id) as 'Unité de vente'

FROM prefix_user u


And I found this ad-hoc contributed report for activity completion but I don't understand how to get the activity completion for each of the registred user of one course.

Could anyone help me ?


Average of ratings: -
In reply to Salomé Desanges

Re: SQL : Activity completion AND custom profile fields for a specific course

by Sheick Jaufuraully -

Hi Salome,

Were you able to resolve the issue?

I am having similar issue.

How do you pull data from your custom profile fields? I created a custom profile field but unsure how i could pull data from it. And also where can i find the name of the field how moodle has saved it on the database. I would appreciate if you could help.

Many Thanks

S

Average of ratings: -
In reply to Sheick Jaufuraully

Re: SQL : Activity completion AND custom profile fields for a specific course

by Salomé Desanges -

Hi Sheick,

I did not manage to get my report with SQL only, I use activity report and kinfd of customise it myself.

You just have to replace your shortname profile field and the title you want in your table, you chose the shortname while you created it.
Here is how i get a custom profile field :

SELECT u.firstname as 'firstname', u.lastname as 'lastname', (SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'shortname' AND UID.userid = u.id) as 'Special profile field',

FROM prefix_user u

Hope it helps smile

Average of ratings: Useful (1)
In reply to Salomé Desanges

Re: SQL : Activity completion AND custom profile fields for a specific course

by Sheick Jaufuraully -

This Surely does help Salome, i will try it and see.

Thank you

Sheick

Average of ratings: -
In reply to Salomé Desanges

Re: SQL : Activity completion AND custom profile fields for a specific course

by Randy Thornton -
Picture of Documentation writers


Salomé

The usual solution is the add a WHERE clause to the end to limit to a specific course. If you search the contributed reports page https://docs.moodle.org/35/en/ad-hoc_contributed_reports you will see many examples that look like this:

WHERE c.id=16

which means restrict the results to the course with id number 16. You can also use the course shortname or fullname here too, but since the course id never changes, it is the most reliable way.  Some examples there use a placeholder like 


WHERE c.id=##

which means the query is made for you to put in the course id for the course you want on your site.


Randy

Average of ratings: Useful (2)