Forum

Counting the number of ratings received on a forum by users

 
Picture of Ravi Murugesan
Counting the number of ratings received on a forum by users
Group Moodle Course Creator Certificate holders
Hi,

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
 
Average of ratings: Useful (1)
Picture of Ravi Murugesan
Re: Counting the number of ratings received on a forum by users
Group Moodle Course Creator Certificate holders

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:

  1. forum post id, count of ratings received for this post, user who made this post (ie, the user who received the ratings)
  2. user, no. of posts rated by this person

This is what I need for the time being smile

 
Average of ratings: -
Picture of naim islam
Re: Counting the number of ratings received on a forum by users
 

Your information is very helpful. I hope that you will give again helpful post. thanks

 
Average of ratings: -
Picture of Farhan Karmali
Re: Counting the number of ratings received on a forum by users
Group Particularly helpful MoodlersGroup Plugin developersGroup Testers
Hi Ravi


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

 
Average of ratings: Useful (1)