1

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

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');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.