Is it possible to find what Matrix Block aren't being used

Question

I have a fairly large site with the main content on most pages driven by a Matrix field. That Matrix field has 15 or so different Blocks - we are now doing a tidy up on the site and going to try and reduce the number of blocks available - is there any way to tell if a block isn't being used?

Answer 1

Nothing native, but it can be done with an SQL query – something like this should work (note that all the queries assume that your dbPrefix setting is set to 'craft_'):

SELECT bt.id, bt.handle, f.handle as field 
FROM craft_matrixblocktypes bt 
INNER JOIN craft_fields f ON bt.fieldId = f.id 
LEFT JOIN craft_matrixblocks b ON bt.id = b.typeId 
WHERE b.id IS NULL 

The above query would output a table with the id and handle for all block types that aren't used, in addition to the handle for the Matrix field they belong to, i.e.

id  | handle             | field
------------------------------------------
13  | someMatrixBlock    | someMatrixField
4   | anotherMatrixBlock | someMatrixField

To actually execute the SQL query, you can use a free tool like Sequel Pro or phpMyAdmin – or write a custom plugin that uses the QueryBuilder.

Edit

Upon request in the comments below, here's a query that will list all block types that are in use (i.e. there are blocks for each particular block type). The number of blocks for each type is listed in a count column:

SELECT COUNT(bt.id) as count, bt.id, bt.handle, f.handle as field 
FROM craft_matrixblocktypes bt 
INNER JOIN craft_fields f ON bt.fieldId = f.id 
LEFT JOIN craft_matrixblocks b ON bt.id = b.typeId 
WHERE b.id IS NOT NULL 
GROUP BY bt.id    

...and here's a query that will output a table with all blocks, sorted by block type, with the ID and URI for the entry which "owns" each particular block. The following query should work for Craft 2:

SELECT b.id as blockId, bt.handle as blockType, bt.id as blockTypeId, e.id as entryId, i18n.uri as entryUri, f.handle as field
FROM craft_matrixblocks b
INNER JOIN craft_entries e on e.id = b.ownerId
INNER JOIN craft_fields f on f.id = b.fieldId
INNER JOIN craft_matrixblocktypes bt on bt.id = b.typeId
LEFT JOIN craft_elements_i18n i18n on i18n.elementId = e.id
ORDER BY bt.id

For Craft 3 (and 4), you'll need to replace the craft_elements_i18n table with craft_elements_sites:

SELECT b.id as blockId, bt.handle as blockType, bt.id as blockTypeId, e.id as entryId, sites.uri as entryUri, f.handle as field
FROM craft_matrixblocks b
INNER JOIN craft_entries e on e.id = b.ownerId
INNER JOIN craft_fields f on f.id = b.fieldId
INNER JOIN craft_matrixblocktypes bt on bt.id = b.typeId
LEFT JOIN craft_elements_sites sites on sites.elementId = e.id
ORDER BY bt.id

If you want to exclude drafts, revisions and soft-deleted entries, include this in the query:

INNER JOIN craft_elements els on els.id = b.ownerId 
WHERE els.dateDeleted IS NULL AND els.draftId IS NULL AND els.revisionId IS NULL AND els.archived = 0

Edit: For Craft 4, you'll want to replace all instances of b.ownerId with b.primaryOwnerId.

If you want to limit that last query to only list the blocks for a particular block type, you can replace the ORDER BY statement with this:

WHERE bt.id = 2

Where 2 is the ID for the block type you want to list.