Filter and sort Commerce products based on Matrix blocks

Question

We have a website that uses Craft CMS v3.1.28 and Commerce v2.1.6.1. The below logic is implemented within a plugin and we're prepared to do custom implementations aka. prepared to go deep!

I would like to extend an existing ProductQuery with two innerJoins on a Matrix field. The matrix blocks represent product groups (by title; "contain") and sets the order of the product groups. I am currently getting duplicate rows (see code in first answer). I think I might have to innerJoin on the subQuery (generated below)..?

Now, we're currently using the code below to get the products and it works. However, it's not very efficient, since we then have to loop through the matrix and match by title to get the right order.

// $base_product_titles is the array of titles from the matrix blocks  
// this query is obviously done per category
// additionally we have some filters ($types and $variant_criteria)

$base_products_query = Product::find()
        ->relatedTo($category)
        ->title($base_product_titles)
        ->type($types)
        ->hasVariant($variant_criteria)
        ->orderBy('type')
        ->with([
            'usage',
            'casing',
            'series',
            'defaultVariant',
            'variants',
            'variants.tire_size',
            [
                'variants.images',
                [ 'withTransforms' => ['product_overview']]
            ]
        ]);

PS: We group the above result by title in the template using |group('title').

I would appreciate any guidance!

Kind regards, Meinhard

Answer 1

I've tried the following, but it "obviously" gives me duplicate rows and my SQL and Craft/Yii knowledge isn't good enough to solve it. It's basically its own subquery with no relation to the "filters" in the query above, except for the title. I'm thinking that I could innerJoin on the initial subquery (which is the query/result from above) but don't know how ($base_products_query->subQuery equals NULL)?

$base_products_query->innerJoin('`craft_matrixblocks`', "`craft_matrixblocks`.`ownerId` = '".$category->id."' AND `craft_matrixcontent_product_sorting`.`elementId` = `craft_matrixblocks`.`id`");    
$base_products_query->innerJoin('`craft_matrixcontent_product_sorting`', '`content`.`title` = `craft_matrixcontent_product_sorting`.`field_product_product_title`);
$base_products_query->orderBy('craft_matrixblocks.sortOrder');