Moodle - delete duplicate blocks in courses

readme.md

Delete duplicate blocks from Moodle courses

We had a problem generating courses for the Spring 2021 term using the local_course_template plugin. I missed in the documentation that you should add $CFG->defaultblocks_override = ''; to your config.php file so we created duplicate blocks in hundreds of courses. I looked around for a CLI or administrative tool, or moosh script, that could identify and bulk delete these duplicate blocks but could not find one. I was wary to delete blocks by direct database queries but I picked out a particular one and tested it—it seemed to work. It was super helpful to read the blocks entity-relationship diagram to figure out a) the impact of database deletions (luckily block_instances is connected only to the context table which made me feel safe to test this out) and b) design the queries here.

The general process: run a query to identify courses with duplicate blocks in them, use that as a sub-query in another query to identify blocks that are potentialy members of duplicate sets within those courses, finally run a python script to identify the identifiers of duplicates to be deleted. Then the final step is simply:

DELETE FROM {prefix}_block_instances
WHERE id IN (...comma-separated output of the python script...)

blocks-with-duplicates.sql

SELECT b.id, b.blockname, b.parentcontextid, c.id, c.shortname
FROM {prefix}_block_instances b
JOIN (SELECT * FROM {prefix}_context WHERE contextlevel = 50) ctx ON b.parentcontextid = ctx.id
JOIN {prefix}_course c ON c.id = ctx.instanceid
-- this list of block types might need to be changed depending on Moodle instance
WHERE b.blockname IN ('search_forums', 'recent_activity', 'calendar_upcoming', 'news_items')
-- must be a better way but I just look for blocks in courses we know have duplicates
-- so we use the other query as a sub-query
AND c.id IN (
    SELECT c.id
    FROM {prefix}_block_instances b
    JOIN (SELECT * FROM {prefix}_context WHERE contextlevel = 50) ctx ON b.parentcontextid = ctx.id
    JOIN {prefix}_course c ON c.id = ctx.instanceid
    WHERE b.blockname <> 'html'
    GROUP BY b.blockname, c.id
    HAVING COUNT(b.id) > 1
)

courses-with-duplicates.sql

SELECT b.blockname, c.id, c.fullname, COUNT(*) as number_of_blocks
FROM {prefix}_block_instances b
JOIN (SELECT * FROM {prefix}_context WHERE contextlevel = 50) ctx ON b.parentcontextid = ctx.id
JOIN {prefix}_course c ON c.id = ctx.instanceid
-- note that your system may have other blocks that can appear in multiples
-- for us, multiple html is OK and dupe types are listed in the other query
WHERE b.blockname <> 'html'
GROUP BY b.blockname, c.id
HAVING COUNT(b.id) > 1

get-dupe-block-ids.py

import csv

# given CSV with these columns:
# "block_id","blockname","parentcontextid","course_id","shortname"
# 6025,"calendar_upcoming",74255,1868,"UDIST-3000-12-UDIST-3000-3-2021SP"
# 6033,"calendar_upcoming",74255,1868,"UDIST-3000-12-UDIST-3000-3-2021SP"
# print out a list of block_ids of duplicate blocks, preferring the blocks
# created later (e.g. higher id, so 6033 in the example rows)

blocks = []
dupes = set()

with open('2020-12-04-duplicate-block-instances.csv', 'r') as fh:
    reader = csv.DictReader(fh)
    for row in reader:
        blocks.append(row)

for block in blocks:
    other_blocks_in_course = [b for b in blocks if b["course_id"] == block["course_id"] and b["block_id"] != block["block_id"]]
    for b in other_blocks_in_course:
        if b["blockname"] == block["blockname"]:
            higher_id = b["block_id"] if b["block_id"] > block["block_id"] else block["block_id"]
            dupes.add(higher_id)
            break

for id in dupes:
    print(id)