List of all forums, subscription and ratings settings

List of all forums, subscription and ratings settings

by Philip Roy -
Number of replies: 14

Hi all,

We've made quite mix up with forums across a large site, and we need to review each and every forum and amend settings related to the subscription mode and if ratings are enabled and set (as they should be) across the entire site for consistency.

Does anyone know how we could generate a complete list of all forums, what their subscription setting is and if ratings are enabled...and what the ratings settings are?

It would be great is the forums were listed as clickable links that we could then go in and fix.

I'm not an SQL'er at all (and I can't think of another way to do it with configurable reports) so all and any help welcome.

Phil

Average of ratings: -
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by Philip Roy -

I've made a start but I am getting errors.....this script does run, but it's not right....


SELECT prefix_forum.course AS 'Course ID', prefix_course.fullname AS 'Course Name', concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?id=',prefix_forum.id,'">',prefix_forum.name,'</a>') AS 'Forum name',prefix_forum.id AS 'Wrong ID',
CASE
WHEN prefix_forum.forcesubscribe = 0 THEN "Optional"
WHEN prefix_forum.forcesubscribe = 1 THEN "Forced"
WHEN prefix_forum.forcesubscribe = 2 THEN "Auto"
WHEN prefix_forum.forcesubscribe = 3 THEN "Disabled"
END As 'Subscription Mode'
FROM prefix_forum
INNER JOIN prefix_course
INNER JOIN prefix_course_modules
ON prefix_forum.course=prefix_course.id AND prefix_course_modules.course=prefix_course.id


This outputs...

  • The course ID (which is correct)
  • The course name (which is correct)
  • The forum name (which is the right name for the forum, but the link to edit the forum has the wrong ID in it...see next bullet point)
  • The wrong ID for each forum (I can't figure out where to pull the correct ID for each forum from?)
  • The subscription mode (which is correct)

The other problem is that I get multiple listings that are exactly the same (see attached image)....so I don't know what I am doing wrong.

Any help gratefully accepted, as this is beyond me.

I also haven't even tried to list "ratings" settings for each forum yet.


Phil


Attachment 2015-03-09_14h07_35.jpg
Average of ratings: -
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by Philip Roy -

Anyone? Sorry for the shameless bump (I won't do it again) but I'm stuck.


Phil

Average of ratings: -
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by ryan sanders -

it looks like you are only pulling information from "course" table. and not "forum" table.  and you are pulling information from a url notation? huh? there is also "forum_subscription" table within the database.

is your database messed up? or did someone not do a good initial creation of forums? and needing to go back and verify / adjust settings?  and in that you are wanting a quick click thing to go through? 

if you are using mysql database,  phpmyadmin is fairly popular, i think it has some settings for additional database types as well. might be easier creating an SQL through phpmyadmin. to see references and tables you can pick from, and then the given field that holds information within each table.



Average of ratings: -
In reply to ryan sanders

Re: List of all forums, subscription and ratings settings

by Philip Roy -

Thanks Ryan, you're correct when you say..."did someone not do a good initial creation of forums? and needing to go back and verify / adjust settings?  and in that you are wanting a quick click thing to go through?"

A lot of forums have been set up incorrectly. We posted a "How to" for teaching staff (who can control settings) on how to fix the issue, but we do need to go through and check subscriptions and ratings.

My first post didn't get any responses...

https://moodle.org/mod/forum/discuss.php?d=282790

So I decided it was better to try and generate a report that lists all the forums, so we can quickly see the values with each forum across the site. Probably better than doing a forced SQL change across the site.

I don't have direct access to the database (our install is hosted by Catalyst) and in fact Configurable Reports has SQL queries deactivated as they saw the ability to run direct SQL queries as possibly impacting server performance...so even when I get the SQL report correct (I'm testing on my own install), I'll need to send it to them to add in to the reports tool.

I confess I was hoping for help here in the forum as the alternative was to pay Catalyst to run or develop a script for us, and we're trying to decrease some of our costs at the moment.

Phil


Average of ratings: -
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by ryan sanders -

no direct help in creating an SQL statement.... 

with that... stop trying to find a shortcut and just get it done and over with...

  1. google your browser and see how to adjust settings. so when "opening up new tab" it does not take you to the new tab automatically.
  2. goto some course, and "turn on editing"
  3. go back to "site admin menu -> course listings"
  4. "right click" on a course
  5. "left click" on open in new tab
  6. do above about 20 times....
  7. wait a minute or so... and then click through them...  (hopefully only one forum per course)
  8. if more than one forum, right click, and open in new tab.. for "edit settings" of additional forums. per course.
  9. wait a minute or so (waiting for pages to load)
  10. then go back and double check settings, adjust as you need to. then click "save"
  11. go through the 20 ish or so tabs you have open...
  12. go back to beginning, tab, hopefully save is done and refreshed, and you can close it out.. and repeat. 

have "notepad" or like program open.  to do a quick copy paste. of where you left off, between opening tabs. so you have a reference point

been through enough of above. that i know after about 40 courses per say. my clicker finger is out of the job in pain, and time to rest up doing something else for a little bit. before going back at it.  hence notation of "notepad"  

===============

with above... i could almost see you replying well.... we have a few hundred courses if not near a 1000 courses... and/or course formats = only showing a little bit of them. vs a entire listing of all sections.

Average of ratings: -
In reply to ryan sanders

Re: List of all forums, subscription and ratings settings

by Philip Roy -

Thanks, I guess? I find your final comment amusing as it points to how the rest of the post isn't all that useful for me...I knew all that...but again, doesn't really help.

As stated, we've had staff adjust some forums and this isn't simply about trying to resolve the multitude of forums that need adjusting...it's about trying to develop a script that I see as useful for the long term for ongoing use of our Moodle site....of being able to generate a report of all the forums in a site and what their associated subscription and ratings settings are. This is why I came back to the site after posting a 'quick fix' post and decided configurable reports was a better, long term option.

So my request for help from anyone on developing the forum report stands.

Cheers, Phil

Average of ratings: Useful (1)
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by Peter Bowen -

Hmm - so the SQL answer....

The issue was you were referring to the forum id, whereas the links refer to the course module id. Also, you only want to list items that are actually a forum.

Corrected SQL below.

SELECT prefix_forum.course AS 'Course ID', prefix_course.fullname AS 'Course Name', concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?id=',prefix_course_modules.id,'">',prefix_forum.name,'</a>') AS 'Forum name',prefix_course_modules.id AS 'Wrong ID',
CASE
WHEN prefix_forum.forcesubscribe = 0 THEN "Optional"
WHEN prefix_forum.forcesubscribe = 1 THEN "Forced"
WHEN prefix_forum.forcesubscribe = 2 THEN "Auto"
WHEN prefix_forum.forcesubscribe = 3 THEN "Disabled"
END As 'Subscription Mode'
FROM prefix_forum
INNER JOIN prefix_course
INNER JOIN prefix_course_modules

ON prefix_forum.course=prefix_course.id AND prefix_course_modules.course=prefix_course.id


INNER JOIN prefix_modules
ON prefix_course_modules.module=prefix_modules.id and prefix_modules.name='forum'


Average of ratings: Useful (1)
In reply to Peter Bowen

Re: List of all forums, subscription and ratings settings

by Philip Roy -

That's awesome. I haven't had time to get back to this, so I really appreciate what you have done.

I don't quite understand that significance of the "Wrong ID" column...was that just to show me what I was doing wrong?

Two things I need to work on...

1) the courses and their forums are still being listed a number of times (as per attached image)

2) I'm hoping if it's not too complex, to also list the settings for ratings for each forum

Phil

Forum listing

Average of ratings: -
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by Peter Bowen -

The wrong ID was in the SQL you had provided - so it was still there when I sent it back to you...


I haven't time to test (I don't have multiple forums per course which I think is what you have) but if you try the following... I will have another look tomorrow as well as the ratings.


SELECT prefix_forum.course AS 'Course ID', prefix_course.fullname AS 'Course Name', concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?id=',prefix_course_modules.id,'">',prefix_forum.name,'</a>') AS 'Forum name',prefix_course_modules.id AS 'Wrong ID',
CASE
WHEN prefix_forum.forcesubscribe = 0 THEN "Optional"
WHEN prefix_forum.forcesubscribe = 1 THEN "Forced"
WHEN prefix_forum.forcesubscribe = 2 THEN "Auto"
WHEN prefix_forum.forcesubscribe = 3 THEN "Disabled"
END As 'Subscription Mode'
FROM prefix_forum
INNER JOIN prefix_course ON prefix_forum.course=prefix_course.id
INNER JOIN prefix_course_modules ON prefix_course_modules.course=prefix_course.id AND prefix_course_modules.instance=prefix_forum.id
INNER JOIN prefix_modules
ON prefix_course_modules.module=prefix_modules.id and prefix_modules.name='forum'

Average of ratings: Useful (1)
In reply to Peter Bowen

Re: List of all forums, subscription and ratings settings

by Philip Roy -

Brilliant! That solved the repeat listings bug in the script...thank you so much!

Phil

Average of ratings: -
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by Peter Bowen -

OK - have reformatted the style of SQL cause it is bugging me..... smile


Here is including the ratings. Any more, I might have to start consulting. smile


SELECT f.course AS 'Course ID', c.fullname AS 'Course Name', concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?id=',cm.id,'">',f.name,'</a>') AS 'Forum name',cm.id AS 'Wrong ID',
CASE
WHEN f.forcesubscribe = 0 THEN "Optional"
WHEN f.forcesubscribe = 1 THEN "Forced"
WHEN f.forcesubscribe = 2 THEN "Auto"
WHEN f.forcesubscribe = 3 THEN "Disabled"
END As 'Subscription Mode',
CASE
WHEN f.assessed = 0 THEN "No ratings"
WHEN f.assessed = 1 THEN "Average"
WHEN f.assessed = 2 THEN "Count"
WHEN f.assessed = 3 THEN "Maximum"
WHEN f.assessed = 4 THEN "Minimum"
WHEN f.assessed = 5 THEN "Sum"
END As 'Rating type',
CASE
WHEN f.scale < 0 THEN "Scale"
WHEN f.scale > 0 THEN f.scale
END As 'Scale'
FROM prefix_forum AS f
INNER JOIN prefix_course AS c ON f.course=c.id
INNER JOIN prefix_course_modules AS cm ON cm.course=c.id AND cm.instance=f.id
INNER JOIN prefix_modules AS m
ON cm.module=m.id and m.name='forum'

Average of ratings: Useful (1)
In reply to Peter Bowen

Re: List of all forums, subscription and ratings settings

by Philip Roy -
That is amazing! Thank you so much! I'm going to try and figure out if it can also list what scale is being used, but I'll try and figure that one out myself...appreciating that it can't list every single scale that might be on a site.


Hope I can ask one final question? That wasn't it smile


I still don't get the significance of the "Wrong ID"....is that column there just to show me what I was doing wrong?


Thank you so much again.


Phil


Average of ratings: -
In reply to Philip Roy

Re: List of all forums, subscription and ratings settings

by Peter Bowen -

The scale is listed as the forum.scale field - if it is less than 0, then it is a scale - and that depends on what scales you use in your site. (it will be -1, -2, -3 etc)

I think it was - it should be the instance of the module under the table course_modules, however initially you had it as the id from the forums table. It was in the original SQL you posted, so I continued it through - it is actually used in the link to connect to the forum itself.

Cheers

Peter


Average of ratings: Useful (1)
In reply to Peter Bowen

Re: List of all forums, subscription and ratings settings

by Philip Roy -

Fantastic, thanks. I've managed to tweak it to hide the Wrong ID column, and (after your explanation) when a Scale value is below 0, it will now say Scale and list the value. Thanks so much again!


SELECT f.course AS 'Course ID', c.fullname AS 'Course Name', concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?id=',cm.id,'">',f.name,'</a>') AS 'Forum name',
CASE
WHEN f.forcesubscribe = 0 THEN "Optional"
WHEN f.forcesubscribe = 1 THEN "Forced"
WHEN f.forcesubscribe = 2 THEN "Auto"
WHEN f.forcesubscribe = 3 THEN "Disabled"
END As 'Subscription Mode',
CASE
WHEN f.assessed = 0 THEN "No ratings"
WHEN f.assessed = 1 THEN "Average"
WHEN f.assessed = 2 THEN "Count"
WHEN f.assessed = 3 THEN "Maximum"
WHEN f.assessed = 4 THEN "Minimum"
WHEN f.assessed = 5 THEN "Sum"
END As 'Rating type', 
CASE
WHEN f.scale < 0 THEN concat ('Scale = ',f.scale)
WHEN f.scale > 0 THEN f.scale
END As 'Scale'
FROM prefix_forum AS f
INNER JOIN prefix_course AS c ON f.course=c.id
INNER JOIN prefix_course_modules AS cm ON cm.course=c.id AND cm.instance=f.id
INNER JOIN prefix_modules AS m
ON cm.module=m.id and m.name='forum'

Average of ratings: -