I am trying to write a sql query that shows activities that have been assigned to specific groups. Where are these group assignments stored? I can see how a particular course gets assigned to a group(s), but not a particular activity. (We primarily use Questionnaires).
Activities are not assigned groups specifically. If you set them to use Groups in their settings, either Separate or Visible, they simply uses the groups as defined at the course level. (Except Assignment, which is something else.) You will find the group settings for each activity in the course_modules table.
Now, if you are using Access restrictions to restrict an activity to a specific group, then that is also in the course_modules table in the column called "availability". Unfortunately, it is a mess: it is not properly normalized at all and so you will have to parse out the string to find the group. For example:
which tells you this activity is restricted by group to the group with id 93.
Oh, I hear you. A pox upon all devs who don't normalize their data.
There are some really ugly things like this floating around in the database (course section and activity sequencing, ouch). You are going to just have to cozy up and get friendly with SUBSTR() and comrades.
I suppose what I would do is make a dummy course and set up every single one of the possible access restriction types, then go in and see what those look like. So, if a restriction is set to require several groups but then also exclude other groups, how does that appear in the db? Another potential source of help is find the php code for handling the restrictions and hope there is some sensible place where there are constants set up. If you intermix the types of restrictions, you may have to know the other types to throw them away
That's a good suggestion regarding working in a development system. I suppose I am fortunate in that the users really want this to work to the point were are willing to keep their restrictions pretty simple. Still, even if it was just groups with the occasional cohort restriction, it's a pain.
I'm very new to moodle and got dragged into this because SQL is my thing. Can you tell me if it is possible to create views that you can use on custom reports? With all the Cases and substrings, views would make this easier!
Thanks again for all the help!
Ah, are you using Config reports and/or the Ad-hoc queries plugins?
Those are both read only basically, though Config Reports will let you use some basic things like UPDATE if you turn off the "SQL Security" in its plugin settings. But it can't create things like views or temp tables and so forth. However, if you create views another way, you can certainly use those in your queries, as long as the views are in the same moodle db (because the plugins run as the Moodle user set up for that db and that should be the only one it has access to.).
A common and useful view is one for user firstname and lastname. I usually do one for course completions because its a popular request. Also, if your find yourself having to do reports from the logs, a view can dramatically speed things up, for instance if you have to do a query for "last time a student entered their courses".
If you are new to Moodle then perhaps these things may help:
- Do you have Adminer installed? https://moodle.org/plugins/local_adminer. Everyone has their favorite tools, but this one is very handy because it is right there in Moodle.
- I assume you are aware of the Ad-hoc contributed queries page: https://docs.moodle.org/310/en/ad-hoc_contributed_reports but if not, it's a mismash but I borrow from it all the time.
- Finally, most of the questions about queries are posted over in the forum for the Configurable Reports plugin rather than here, mostly because of history I suppose, but most of the SQL people hang out there: https://moodle.org/mod/forum/view.php?id=7979