CraftCMS Matrix blocks count usage on pages

craftcms-v2-count-content-matrix-blocks-usage

SET SESSION group_concat_max_len = 1000000;

-- All matrix block types
SELECT * FROM matrixblocktypes;

-- All matrix block types and some related information
SELECT mb.id, mb.ownerId, 
    mb.fieldId, 
    mt.name, 
    mt.handle, 
    c.title, 
    et.locale, 
    et.slug, 
    et.enabled,
    et.uri
FROM matrixblocks mb
INNER JOIN matrixblocktypes mt 
  ON mt.fieldId = mb.fieldId
  AND mt.id = mb.typeId
INNER JOIN elements_i18n et ON et.elementId = mb.ownerId
INNER JOIN content c ON c.elementId = mb.ownerId
WHERE et.uri <> ""
GROUP BY mt.handle, mb.ownerId;


-- Count of matrix block usage and urls that they're used at
SELECT 
    r.id,
    r.`name`,
    r.`handle`, 
    count(r.`handle`) times_used, 
    GROUP_CONCAT(r.url) urls 
FROM (
    SELECT mt.id, mt.name, mt.handle, CONCAT('http://www.example.com/', et.uri) url
    FROM matrixblocks mb
    INNER JOIN matrixblocktypes mt 
      ON mt.fieldId = mb.fieldId
      AND mt.id = mb.typeId
    INNER JOIN elements_i18n et ON et.elementId = mb.ownerId
    WHERE et.uri <> '' AND et.locale = 'en_us' AND et.enabled = 1
    GROUP BY mb.ownerId, mt.handle
) r GROUP BY r.handle;