Find entries that use a specific block in their Matrix field?

2018-08-14 16:09:43

Suppose a matrix field named MyMatrixField has three blocks called Text, Images, and Buttons.

What MySQL query would list the section and title for all entries whose MyMatrixField field has a Buttons block in-use?

Something like:

SELECT section, title FROM `craft_entries` WHERE `field_MyMatrixField_Buttons` IS NOT NULL

You can join your matrix table and check for elements with a certain type and your owner id

{% set entries = craft

.entries()

.section(['your-section'])

.leftJoin('{{%matrixblocks}} as matrixblocks', 'matrixblocks.ownerId = entries.id')

.andWhere({'matrixblocks.typeId': 1})

.all()

%}

replace the correct section and the correct type id of your blocktype

Robin's answer set me on the right path. Here's the final code that got me the results I was looking for:

{#

Figure out everywhere the specified block appears.

This returns an array where every instance of a block is a new item.

#}

{% set entries = craft

.entri

  • You can join your matrix table and check for elements with a certain type and your owner id

    {% set entries = craft

    .entries()

    .section(['your-section'])

    .leftJoin('{{%matrixblocks}} as matrixblocks', 'matrixblocks.ownerId = entries.id')

    .andWhere({'matrixblocks.typeId': 1})

    .all()

    %}

    replace the correct section and the correct type id of your blocktype

    2018-08-14 17:00:27
  • Robin's answer set me on the right path. Here's the final code that got me the results I was looking for:

    {#

    Figure out everywhere the specified block appears.

    This returns an array where every instance of a block is a new item.

    #}

    {% set entries = craft

    .entries()

    .section(['sectionNameHere'])

    .leftJoin('{{%matrixblocks}}', '{{%matrixblocks}}.ownerId = entries.id')

    .andWhere({'{{%matrixblocks}}.typeId': blockIdNumberHere})

    .status(['enabled', 'disabled'])

    .all()

    %}

    {# Init vars #}

    {% set filteredEntries = [] %}

    {#

    Filter the previous array down so that an entry with the specified block

    can only appear once.

    #}

    {% for entry in entries %}

    {# Create a vastly pared-down array for each entry #}

    {% set filteredEntry = {

    link: entry.cpEditUrl,

    title: entry.title

    } %}

    {% if filteredEntry not in filteredEntries %}

    {% set filteredEntries = filteredEntries | merge([filteredEntry]) %}

    {% endif %}

    {% endfor %}

    {# Spit out the results #}

    2018-08-14 17:52:26