Suppose a Mtrix 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
    .leftJoin('{{%matrixblocks}} as matrixblocks', 'matrixblocks.ownerId = entries.id')
    .andWhere({'matrixblocks.typeId': 1}) 

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

  • Any typos in there? I'm getting an Internal Server Error. – Sandwich Aug 14 '18 at 14:26
  • That's because you have a table prefix, so you'll wrap all tables with {{%}} or create an alias. Of course you need to adjust it to your needs. It's just an example that worked for my system – Robin Schambach Aug 14 '18 at 14:29
  • Ok, well I looked into the matrixblocktypes table and found that the block type I want to locate has a value of 25 in the id column. Am I looking in the right column for the id? – Sandwich Aug 14 '18 at 14:34
  • Yeah that's the ID of your block type – Robin Schambach Aug 14 '18 at 14:41
  • Ok, cool. I have it figured out now. I had to modify things a bunch, but your code definitely set me on the right path. What's the proper procedure here—post my final code that works and mark it as the answer (while accrediting your post), or post my code as an answer, but mark your post as the answer? – Sandwich Aug 14 '18 at 14:59

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
    .leftJoin('{{%matrixblocks}} as matrixblocks', 'matrixblocks.ownerId = entries.id')
    .andWhere({'matrixblocks.typeId': blockIdNumberHere})
    .status(['enabled', 'disabled'])

{# 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 #}
  {% for entry in filteredEntries %}
      <a href="{{ entry.link }}" target="_blank">{{ entry.title }}</a>
  {% endfor %}

Some remarks:

  • To get the block IDs belonging to a specific matrix field, plug the Matrix field ID into this line: SELECT id, name FROM `craft_matrixblocktypes` WHERE fieldId = ___ ORDER BY `craft_matrixblocktypes`.`sortOrder` ASC`
  • The initial code, once working, spit out a line for each instance of a block, instead of one line per entry containing that block. That's why I had to filter the array down before outputting things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.