I teach on a Moodle 3.1 site and I like the 'single rating' scale which we have here on moodle.org forums as well. I've set up a scale on my site with one value - 'useful' - and I've used this scale in some forums.
I'd like to know if there's a way to generate a report that shows this in the context of a specific forum within a course:
| user name |
number of forum posts rated as 'useful' at least once | total number of 'useful' ratings received |
|---|---|---|
Is there perhaps an SQL query I can run, and could anyone help me write this query?
Thanks,
Ravi
Okay I've managed to figure this out. I've described my approach below, with the caveat that there must be a better way to do this.
I installed the Moodle Adminer plugin and exported the following tables with their data as CSV files (I didn't use the SQL option).
- mdl_rating: this has information on which posts (and other Moodle activities) have received ratings from which users
- mdl_forum_posts: posts made by users
Adminer gives you all the data from your Moodle site - you can't filter it at the exporting stage. The challenge is to figure out how the different id numbers match up to the course and forums you're interested in analysing.
For this matching I exported mdl_course, mdl_course_modules and mdl_context. I did a lot of back-and-forth to match up things, and I haven't documented this.
A word of caution: My mdl_forum_posts had some loss in data consistency, if I can call it that. Every now and then the tabular data was disrupted by some text.
Then I obtained a list of all users on the site using bulk user actions (mdl_user can also be used for this purpose).
I copied all the relevant data into a separate spreadsheet and did some more wrangling and lookups to get two tables:
- forum post id, count of ratings received for this post, user who made this post (ie, the user who received the ratings)
- user, no. of posts rated by this person
This is what I need for the time being
Your information is very helpful. I hope that you will give again helpful post. thanks
I made this query to get the result
SELECT u.username,COUNT(distinct fp.id) as 'number of forum posts rated as "useful" at least once',COUNT(r.id) as 'total number of "useful" ratings received'
FROM mdl_rating r
INNER JOIN mdl_scale s ON (s.id = -r.scaleid)
INNER JOIN mdl_forum_posts fp ON (fp.id = r.itemid)
INNER JOIN mdl_user u on (u.id = fp.userid)
WHERE s.name = 'useful'
AND r.component = 'mod_forum'
AND r.ratingarea = 'post'
GROUP BY fp.userid;
Am pretty sure it works fine, atleast it did in my test environment, you can test it , you can replace the name of the scale you made instead of useful that I wrote in the line "WHERE s.name = 'useful'" do let me know if it works
Am just not so sure about this line INNER JOIN mdl_scale s ON (s.id = -r.scaleid) , Idont know why its inserting the scaleid with - in front .
also this assumes that you have just one rating in the useful scale like moodle does
Farhan
Thanks for that, this query works for me. It is really weird about that dash - for some bizarre reason it puts that before the scale id in the ratings table. I presume the code needs this for some reason.