We have a website that uses Craft CMS v3.1.28 and Commerce v126.96.36.199. 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