Answers.Body: <p>For anyone who may come to this in future, I've found a workaround that achieves the same thing:</p>
  <ol>
  <li>Retrieve data from each matrix block separately</li>
  <li>do any filtering needed on each block separately</li>
  <li>merge the two resulting arrays, and use the 'sort' filter <a href="https://twig.symfony.com/doc/3.x/filters/sort.html" rel="nofollow noreferrer">https://twig.symfony.com/doc/3.x/filters/sort.html</a></li>
  </ol>
  <p>Resulting code is as below:</p>
  <pre><code>    {# Get data from first matrix block #}
      {% set contentBuilderHeadings = craft.matrixBlocks()
          .field(['contentBuilder'])
          .type(['mainHeading'])
          .all()
      %}
  
      {# do some filtering on the first set of data  #}
      {%
          set navHeadings = contentBuilderHeadings
          |filter(block =&gt; block.isNavLink )
      %}
  
      {# get data from the second matrix block  #}
      {%
          set pageNavLinks = craft.matrixBlocks()
          .field('pageNavLink')
          .type('navLinkSettings')
          .all()
      %}
  
      {# Merge and sort the two sets of data  #}
      {%
          set navLinks = navHeadings
          |merge(pageNavLinks)
          |sort((a, b) =&gt; a.navLinkOrder &lt;=&gt; b.navLinkOrder)
      %}
  </code></pre>
  <p>This results in a single array which can be looped and outputted in html, which is sorted by the <code>navLinkOrder</code> field as specified in the <code>sort</code> filter</p>
  <p>I'm still not clear on why selecting from the two matrix blocks in the same query had such unexpected results, so any explanations on that are still welcome!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-03T11:24:45.800Z
Answers.LastActivityDate: 2021-09-03T11:24:45.800Z
Answers.UserDisplayName: Roxy Walsh
Body: <p>I am on Craft3.5 and trying to build a matrix block query where I would like to return results from two different matrix blocks.  The docs seem to suggest that this is possible.</p>
  <p>This is the code I have currently (in my twig template)</p>
  <pre><code>    {% set headingsAndNavPages = craft.matrixBlocks()
          .field(['contentBuilder', 'pageNavLink'])
          .type(['mainHeading', 'navLinkSettings'])
      %}
      {%
          set navItems = headingsAndNavPages
          .orderBy('navLinkOrder')
          .all()
          |filter(block =&gt; block.isNavLink )
      %}
  </code></pre>
  <p>The first matrix block has the handle <code>contentBuilder</code> and contains a type with handle <code>mainHeading</code></p>
  <p>The second matrix block has the handle <code>pageNavLink</code> and contains a type with handle <code>navLinkSettings</code></p>
  <p>The <code>mainHeading</code> and <code>pageNavLink</code> of the two matrix blocks contain fields with identical handles - see screenshot below showing them side-by-side</p>
  <p><a href="https://i.sstatic.net/YZ1XG.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/YZ1XG.png" alt="two matrix blocks side by side" /></a></p>
  <p>With the query exactly as above, I get an 'Unknown Column' exception:</p>
  <pre><code>SQLSTATE[42S22]: Column not found: 1054 Unknown column 'navLinkOrder' in 'order clause'
  The SQL being executed was: SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `matrixblocks`.`fieldId`, `matrixblocks`.`ownerId`, `matrixblocks`.`typeId`, `matrixblocks`.`sortOrder`
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`
  FROM `elements` `elements`
  INNER JOIN `matrixblocks` `matrixblocks` ON `matrixblocks`.`id` = `elements`.`id`
  INNER JOIN `elements` `owners` ON `owners`.`id` = `matrixblocks`.`ownerId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
  WHERE (`matrixblocks`.`fieldId` IN ('1', '55')) AND (`matrixblocks`.`typeId` IN ('1', '13')) AND (`owners`.`draftId` IS NULL) AND (`owners`.`revisionId` IS NULL) AND (`elements`.`archived`=FALSE) AND ((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
  ORDER BY `navLinkOrder`) `subquery`
  INNER JOIN `matrixblocks` `matrixblocks` ON `matrixblocks`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
  ORDER BY `navLinkOrder`
  </code></pre>
  <p>However, where I'm stumped, is if I remove <em>either</em> field name, it works perfectly - no exceptions, and the expected content (from whichever matrix block remains) shows on the page - eg if I change the second line to either <code>.field(['contentBuilder'])</code> or <code>.field(['pageNavLink'])</code></p>
  <p>So I'm a bit puzzled as to how the field apparently exists on each one individually, but not when they are combined?</p>
  <p>I've also tried removing the <code>orderBy()</code> part of the query so the second part is</p>
  <pre><code>    {%
          set navItems = headingsAndNavPages
          .all()
          |filter(block =&gt; block.isNavLink )
      %}
  </code></pre>
  <p>However, when I do this, none of the items are displayed on the page  - but I can see them if there is no OrderBy query, provided that I only use one of the two fields.</p>
  <p>Something does appear to be returned, but it doesn't seem to be in the correct format, or the expected data is missing, or something - I'm rendering an <code>&lt;li&gt;</code> for every navItem, and I've got an empty <code>&lt;li&gt;</code> tag rendered, but the actual content is not displayed.</p>
  <p>I also found something about needing to include the <code>id</code> field in the first part of the query, in order for content to be shown, and that the matrix block id can be found in the URL when editing the field.<br />
  This confused me a bit as I can see the content without adding the id (provided I only use one field at at time), but I tried it anyway - both adding a single id, and both ids, eg</p>
  <pre><code>.id(1)
  .id([1, 55])
  .id(55)
  </code></pre>
  <p>However, I get nothing back at all with any variant of this, not even the empty <code>&lt;li&gt;</code> tag is rendered</p>
  <p>As far as I can tell, the issue seems to be caused by including multiple matrix blocks in the <code>field</code> part of the query.  I'm guessing that the data returned in this instance is somehow formatted or nested differently, so the field used in the OrderBy can't be found, and the fields I'm trying to render in my html are also not as expected.</p>
  <p>But I'm not really sure where to go next - I'm struggling even with how to debug this, as I'm not sure how to even print the results in a readable format - I know you can use {{dump(var)}}, but this seems to print a lot of irrelevant information so I'm struggling to get anything useful from this.</p>
  <p>Apologies if I'm missing something obvious here - I've never really used craft before and I'm struggling to get to grips with it.  I'm more used to Laravel and creating queries / writing PHP in controllers, so everything feels a bit alien!</p>
  <p>If anyone can point me in the right direction either to accomplish what I'm trying to do with the two matrix fields, or even some suggestions on how to debug this, that would be most appreciated!</p>
  
Tags: <craft3><matrix>
Title: Matrix query fails when using multiple fields
ThreadText: # Matrix query fails when using multiple fields
  
  (Asked by: username_0 on 2021-09-03)
  
  I am on Craft3.5 and trying to build a matrix block query where I would like to return results from two different matrix blocks. The docs seem to suggest that this is possible.
  
  This is the code I have currently (in my twig template)
  
  ```
      {% set headingsAndNavPages = craft.matrixBlocks()
          .field(['contentBuilder', 'pageNavLink'])
          .type(['mainHeading', 'navLinkSettings'])
      %}
      {%
          set navItems = headingsAndNavPages
          .orderBy('navLinkOrder')
          .all()
          |filter(block => block.isNavLink )
      %}
  
  ```
  
  The first matrix block has the handle `contentBuilder` and contains a type with handle `mainHeading`
  
  The second matrix block has the handle `pageNavLink` and contains a type with handle `navLinkSettings`
  
  The `mainHeading` and `pageNavLink` of the two matrix blocks contain fields with identical handles - see screenshot below showing them side-by-side
  
  [![two matrix blocks side by side](https://i.sstatic.net/YZ1XG.png)](https://i.sstatic.net/YZ1XG.png)
  
  With the query exactly as above, I get an 'Unknown Column' exception:
  
  ```
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'navLinkOrder' in 'order clause'
  The SQL being executed was: SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `matrixblocks`.`fieldId`, `matrixblocks`.`ownerId`, `matrixblocks`.`typeId`, `matrixblocks`.`sortOrder`
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`
  FROM `elements` `elements`
  INNER JOIN `matrixblocks` `matrixblocks` ON `matrixblocks`.`id` = `elements`.`id`
  INNER JOIN `elements` `owners` ON `owners`.`id` = `matrixblocks`.`ownerId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
  WHERE (`matrixblocks`.`fieldId` IN ('1', '55')) AND (`matrixblocks`.`typeId` IN ('1', '13')) AND (`owners`.`draftId` IS NULL) AND (`owners`.`revisionId` IS NULL) AND (`elements`.`archived`=FALSE) AND ((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
  ORDER BY `navLinkOrder`) `subquery`
  INNER JOIN `matrixblocks` `matrixblocks` ON `matrixblocks`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
  ORDER BY `navLinkOrder`
  
  ```
  
  However, where I'm stumped, is if I remove *either* field name, it works perfectly - no exceptions, and the expected content (from whichever matrix block remains) shows on the page - eg if I change the second line to either `.field(['contentBuilder'])` or `.field(['pageNavLink'])`
  
  So I'm a bit puzzled as to how the field apparently exists on each one individually, but not when they are combined?
  
  I've also tried removing the `orderBy()` part of the query so the second part is
  
  ```
      {%
          set navItems = headingsAndNavPages
          .all()
          |filter(block => block.isNavLink )
      %}
  
  ```
  
  However, when I do this, none of the items are displayed on the page - but I can see them if there is no OrderBy query, provided that I only use one of the two fields.
  
  Something does appear to be returned, but it doesn't seem to be in the correct format, or the expected data is missing, or something - I'm rendering an `<li>` for every navItem, and I've got an empty `<li>` tag rendered, but the actual content is not displayed.
  
  I also found something about needing to include the `id` field in the first part of the query, in order for content to be shown, and that the matrix block id can be found in the URL when editing the field.  
  This confused me a bit as I can see the content without adding the id (provided I only use one field at at time), but I tried it anyway - both adding a single id, and both ids, eg
  
  ```
  .id(1)
  .id([1, 55])
  .id(55)
  
  ```
  
  However, I get nothing back at all with any variant of this, not even the empty `<li>` tag is rendered
  
  As far as I can tell, the issue seems to be caused by including multiple matrix blocks in the `field` part of the query. I'm guessing that the data returned in this instance is somehow formatted or nested differently, so the field used in the OrderBy can't be found, and the fields I'm trying to render in my html are also not as expected.
  
  But I'm not really sure where to go next - I'm struggling even with how to debug this, as I'm not sure how to even print the results in a readable format - I know you can use {{dump(var)}}, but this seems to print a lot of irrelevant information so I'm struggling to get anything useful from this.
  
  Apologies if I'm missing something obvious here - I've never really used craft before and I'm struggling to get to grips with it. I'm more used to Laravel and creating queries / writing PHP in controllers, so everything feels a bit alien!
  
  If anyone can point me in the right direction either to accomplish what I'm trying to do with the two matrix fields, or even some suggestions on how to debug this, that would be most appreciated!
  
  **Tags:** craft3, matrix
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2021-09-03. Score: 2
  
  For anyone who may come to this in future, I've found a workaround that achieves the same thing:
  
  1. Retrieve data from each matrix block separately
  2. do any filtering needed on each block separately
  3. merge the two resulting arrays, and use the 'sort' filter <https://twig.symfony.com/doc/3.x/filters/sort.html>
  
  Resulting code is as below:
  
  ```
      {# Get data from first matrix block #}
      {% set contentBuilderHeadings = craft.matrixBlocks()
          .field(['contentBuilder'])
          .type(['mainHeading'])
          .all()
      %}
  
      {# do some filtering on the first set of data  #}
      {%
          set navHeadings = contentBuilderHeadings
          |filter(block => block.isNavLink )
      %}
  
      {# get data from the second matrix block  #}
      {%
          set pageNavLinks = craft.matrixBlocks()
          .field('pageNavLink')
          .type('navLinkSettings')
          .all()
      %}
  
      {# Merge and sort the two sets of data  #}
      {%
          set navLinks = navHeadings
          |merge(pageNavLinks)
          |sort((a, b) => a.navLinkOrder <=> b.navLinkOrder)
      %}
  
  ```
  
  This results in a single array which can be looped and outputted in html, which is sorted by the `navLinkOrder` field as specified in the `sort` filter
  
  I'm still not clear on why selecting from the two matrix blocks in the same query had such unexpected results, so any explanations on that are still welcome!
  
  
  ---
  
  