Matrix query fails when using multiple fields

Question

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

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!

Answer 1

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!