Querying for an exact match on a Matrix block field with multiple types

Question

I'm trying to query against a specific field value in a Matrix field with multiple block types, and I've tried multiple approaches with no success. It either returns 0 results, or all blocks regardless of type.

The use case is that Snipcart is doing a callback with the ordered items and their skus. I got the different product variants in a Matrix fields, and every variant has its own sku. The field have two block types. I want to find the correct product variant and reduce the available stock.

The simplest approach is this:

$criteria      = craft()->elements->getCriteria(ElementType::MatrixBlock);
$criteria->sku = 'JW';

I figured out that it won't join the content table if you don't specify a fieldId, and you also need a typeId.

$criteria      = craft()->elements->getCriteria(ElementType::MatrixBlock);
$criteria->fieldId = $fieldId;
$criteria->typeId = $typeId;
$criteria->sku = 'JW';

What happens then is that it's seem to do the correct query, but with a AND query for the field on both block types (even though I have defined a typeId):

system.db.CDbCommand.query(SELECT `elements`.`id`, `matrixblocks`.`fieldId`, `matrixblocks`.`ownerId`, `matrixblocks`.`ownerLocale`, `matrixblocks`.`typeId`, `matrixblocks`.`sortOrder`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_matrixcontent_colorsandtextures` `content` ON content.elementId = elements.id
JOIN `craft_matrixblocks` `matrixblocks` ON matrixblocks.id = elements.id
WHERE (((((((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.archived = 0)) AND (elements.enabled = 1)) AND (elements_i18n.enabled = 1)) AND (content.field_texture_sku=:contentfield_texture_sku1)) AND (content.field_color_sku=:contentfield_color_sku1)) AND (matrixblocks.fieldId=:matrixblocksfieldId1)
GROUP BY `elements`.`id`
ORDER BY `matrixblocks`.`sortOrder` LIMIT 100. Bound with :locale='en_us', :contentfield_texture_sku1='JW', :contentfield_color_sku1='JW', :matrixblocksfieldId1='15')

The key here is the fact that my typeId isn't being applied, and this part:

(content.field_texture_sku=:contentfield_texture_sku1)) AND (content.field_color_sku=:contentfield_color_sku1))

I would like to query for a match independent of what type it is, as the blocks can be either or. Is there a way I can turn that into a OR query?

Answer 1

When I understand Brandon correctly in this answer Query for blocks across Block Types using the new field parameter?, it's not possible to set up what you want with a criteria model.

What you can do though is to add additional custom query conditions to the dbCommand after running the criteria model through the buildElementsQuery function.

public function getMatrixBlocks($matrixFieldId, $fieldValue)
{
    $criteria = craft()->elements->getCriteria(ElementType::MatrixBlock);
    $criteria->fieldId = $matrixFieldId;

    $dbCommand = craft()->elements->buildElementsQuery($criteria);

    $params = array(
        ':myFieldHandle' => $fieldValue,
    );

    $conditions = array('or',
        'content.field_myBlockTypeA_myFieldHandle=:myFieldHandle',
        'content.field_myBlockTypeB_myFieldHandle=:myFieldHandle',
    );

    $dbCommand->andWhere($conditions, $params);

    return $dbCommand->queryAll();
}