<p>Compelled to provide a third answer here to help avoid the unnecessary complexity of working with SQL joins—and the headache that comes with having to match Craft's own criteria when fetching rows from element tables—especially when it comes to screening by status, drafts, and revisions!</p>
<p>You can always pre-flight a cheap query and use the results to narrow your more expensive primary query. For example:</p>
<pre><code>{# Grab the Matrix field definition so we can use its ID #}
{# (This is important if you need to filter by the blocks’ content, not just their types) #}
{% matrixField = craft.app.fields.getFieldByHandle('myMatrixField') %}

{# Set up an initial query for Matrix Blocks that match the criteria, and load just their owners’ IDs: #}
{% set candidateBlockOwnerIds = craft.matrixBlocks({
  fieldId: matrixField.id,
  type: 'buttons',
  select: ['ownerId'],
}).column() %}

{# Follow that up with your main query, using the `ownerId` values returned from the first one: #}
{% set entries = craft.entries({
  id: candidateBlockOwnerIds,
  anotherEntryField: 'some additional constraint',
}).all() %}
</code></pre>
<p>If you need to filter by more specific criteria (i.e. Button blocks with a <code>label</code> field value including the text <code>Read</code> or <code>Listen</code>), you can provide any normal params to the Matrix Block query, as though you were loading them with the intention of displaying the information—but you'll still just get back the requisite <code>ownerId</code>s.</p>
<blockquote>
<p> It's worth noting that this <em>can</em> begin to slow down with really large datasets. A few hundred blocks are probably fine—as you get into thousands or tens of thousands of blocks, some optimizations may be required; Postgres even puts a hard cap on the number of tokens in a query (~65K)!</p>
</blockquote>

CC BY-SA 4.0
2022-03-18T04:38:36.177Z
2022-03-18T04:38:36.177Z
August Miller
<p>Robin's answer set me on the right path. Here's the final code that got me the results I was looking for:</p>

<pre><code>{#
  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}} as 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 #}
&lt;ol&gt;
  {% for entry in filteredEntries %}
    &lt;li&gt;
      &lt;a href="{{ entry.link }}" target="_blank"&gt;{{ entry.title }}&lt;/a&gt;
    &lt;/li&gt;
  {% endfor %}
&lt;/ol&gt;
</code></pre>

<p>Some remarks:</p>

<ul>
<li>To get the block IDs belonging to a specific matrix field, plug the Matrix field ID into this line: <code>SELECT id, name FROM `craft_matrixblocktypes` WHERE fieldId = ___ ORDER BY `craft_matrixblocktypes`.`sortOrder` ASC`</code></li>
<li>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.</li>
</ul>

CC BY-SA 4.0
2018-08-14T15:12:04.627Z
2020-04-24T14:06:05.560Z
2020-04-24T14:06:05.560Z
Sandwich
<p>You can join your matrix table and check for elements with a certain type and your owner id</p>
<h2>Craft 3</h2>
<pre class="lang-twig prettyprint-override"><code>{% set entries = craft
    .entries()
    .section(['your-section'])
    .leftJoin('{{%matrixblocks}} as matrixblocks', 'matrixblocks.ownerId = entries.id')
    .andWhere({'matrixblocks.typeId': 1}) 
    .all()
%}
</code></pre>
<p>replace the correct section and the correct type id of your blocktype</p>
<h2>Edit Craft 4</h2>
<p><code>ownerId</code> changed to <code>primaryOwnerId</code></p>
<pre class="lang-twig prettyprint-override"><code>{% set entries = craft
    .entries()
    .section(['your-section'])
    .leftJoin('{{%matrixblocks}} as matrixblocks', 'matrixblocks.primaryOwnerId = entries.id')
    .andWhere({'matrixblocks.typeId': 1}) 
    .all()
%}
</code></pre>

CC BY-SA 4.0
2018-08-14T14:26:38.750Z
Any typos in there? I'm getting an Internal Server Error.
Sandwich
CC BY-SA 4.0
2018-08-14T14:29:25.277Z
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
CC BY-SA 4.0
2018-08-14T14:34:22.510Z
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
CC BY-SA 4.0
2018-08-14T14:41:19.470Z
Yeah that's the ID of your block type
Robin Schambach
CC BY-SA 4.0
2018-08-14T14:59:01.417Z
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
CC BY-SA 4.0
2018-08-14T15:01:13.740Z
If you feel like your code adds something useful feel free to post it
Robin Schambach
CC BY-SA 4.0
2023-11-08T04:58:47.723Z
At some point it's changed from `matrixblocks.ownerId` to `matrixblocks.primaryOwnerId`
danbrellis
CC BY-SA 4.0
2018-08-14T13:16:04.573Z
2023-11-08T09:14:08.360Z
2023-11-08T09:14:08.360Z
Robin Schambach
<p>Suppose a Mtrix field named <code>MyMatrixField</code> has three blocks called <code>Text</code>, <code>Images</code>, and <code>Buttons</code>.</p>

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

<p>Something like:</p>

<pre><code>SELECT section, title FROM `craft_entries` WHERE `field_MyMatrixField_Buttons` IS NOT NULL
</code></pre>

<matrix><mysql>
Find entries that use a specific block in their Matrix field?
# Find entries that use a specific block in their Matrix field?

(Asked by: username_0 on 2018-08-14)

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

**Tags:** matrix, mysql

---

## 3 answers

### Answer by username_2 on 2018-08-14. Score: 4 | Accepted answer

You can join your matrix table and check for elements with a certain type and your owner id
Craft 3
{% 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
Edit Craft 4
ownerId changed to primaryOwnerId
{% set entries = craft
    .entries()
    .section(['your-section'])
    .leftJoin('{{%matrixblocks}} as matrixblocks', 'matrixblocks.primaryOwnerId = entries.id')
    .andWhere({'matrixblocks.typeId': 1}) 
    .all()
%}

> **Comments:**
> **username_0**: Any typos in there? I'm getting an Internal Server Error.  
> **username_2**: 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  
> **username_0**: 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?  
> **username_2**: Yeah that's the ID of your block type  
> **username_0**: 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?  
> **username_2**: If you feel like your code adds something useful feel free to post it  
> **username_3**: At some point it's changed from `matrixblocks.ownerId` to `matrixblocks.primaryOwnerId`  

---

### Answer by username_1 on 2022-03-18. Score: 3

Compelled to provide a third answer here to help avoid the unnecessary complexity of working with SQL joins—and the headache that comes with having to match Craft's own criteria when fetching rows from element tables—especially when it comes to screening by status, drafts, and revisions!
You can always pre-flight a cheap query and use the results to narrow your more expensive primary query. For example:
{# Grab the Matrix field definition so we can use its ID #}
{# (This is important if you need to filter by the blocks’ content, not just their types) #}
{% matrixField = craft.app.fields.getFieldByHandle('myMatrixField') %}

{# Set up an initial query for Matrix Blocks that match the criteria, and load just their owners’ IDs: #}
{% set candidateBlockOwnerIds = craft.matrixBlocks({
  fieldId: matrixField.id,
  type: 'buttons',
  select: ['ownerId'],
}).column() %}

{# Follow that up with your main query, using the `ownerId` values returned from the first one: #}
{% set entries = craft.entries({
  id: candidateBlockOwnerIds,
  anotherEntryField: 'some additional constraint',
}).all() %}

If you need to filter by more specific criteria (i.e. Button blocks with a label field value including the text Read or Listen), you can provide any normal params to the Matrix Block query, as though you were loading them with the intention of displaying the information—but you'll still just get back the requisite ownerIds.

 It's worth noting that this can begin to slow down with really large datasets. A few hundred blocks are probably fine—as you get into thousands or tens of thousands of blocks, some optimizations may be required; Postgres even puts a hard cap on the number of tokens in a query (~65K)!


---

### Answer by username_0 on 2018-08-14. Score: 1

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}} as 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 #}

  {% for entry in filteredEntries %}
    
      {{ entry.title }}
    
  {% 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.


---

