Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-03-18T04:38:36.177Z
Answers.LastActivityDate: 2022-03-18T04:38:36.177Z
Answers.UserDisplayName: August Miller
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T15:12:04.627Z
Answers.LastActivityDate: 2020-04-24T14:06:05.560Z
Answers.LastEditDate: 2020-04-24T14:06:05.560Z
Answers.UserDisplayName: Sandwich
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T14:26:38.750Z
Answers.Text: Any typos in there? I'm getting an Internal Server Error.
Answers.UserDisplayName: Sandwich
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T14:29:25.277Z
Answers.Text: 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
Answers.UserDisplayName: Robin Schambach
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T14:34:22.510Z
Answers.Text: 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?
Answers.UserDisplayName: Sandwich
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T14:41:19.470Z
Answers.Text: Yeah that's the ID of your block type
Answers.UserDisplayName: Robin Schambach
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T14:59:01.417Z
Answers.Text: 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?
Answers.UserDisplayName: Sandwich
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T15:01:13.740Z
Answers.Text: If you feel like your code adds something useful feel free to post it
Answers.UserDisplayName: Robin Schambach
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-08T04:58:47.723Z
Answers.Text: At some point it's changed from `matrixblocks.ownerId` to `matrixblocks.primaryOwnerId`
Answers.UserDisplayName: danbrellis
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-14T13:16:04.573Z
Answers.LastActivityDate: 2023-11-08T09:14:08.360Z
Answers.LastEditDate: 2023-11-08T09:14:08.360Z
Answers.UserDisplayName: Robin Schambach
Body: <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>
  
Tags: <matrix><mysql>
Title: Find entries that use a specific block in their Matrix field?
ThreadText: # 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
  
  ```twig
  {% 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`
  
  ```twig
  {% 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 `ownerId`s.
  
  > 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
  
  username_2'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 #}
  <ol>
    {% for entry in filteredEntries %}
      <li>
        <a href="{{ entry.link }}" target="_blank">{{ entry.title }}</a>
      </li>
    {% endfor %}
  </ol>
  
  ```
  
  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.
  
  
  ---
  
  