Answers.Body: <p>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)?</p>
  
  <pre><code>$base_products_query-&gt;innerJoin('`craft_matrixblocks`', "`craft_matrixblocks`.`ownerId` = '".$category-&gt;id."' AND `craft_matrixcontent_product_sorting`.`elementId` = `craft_matrixblocks`.`id`");    
  $base_products_query-&gt;innerJoin('`craft_matrixcontent_product_sorting`', '`content`.`title` = `craft_matrixcontent_product_sorting`.`field_product_product_title`);
  $base_products_query-&gt;orderBy('craft_matrixblocks.sortOrder');
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-09T13:58:00.343Z
Answers.LastActivityDate: 2019-07-09T13:58:00.343Z
Answers.UserDisplayName: Meinhard Meintjes
Body: <p>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!</p>
  
  <p><strong>I would like to extend an existing ProductQuery with two innerJoins on a Matrix field</strong>. 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)..?</p>
  
  <p>Now, we're currently using the code below to get the products and it works. However, it's not very efficient, <strong>since we then have to loop through the matrix and match by title to get the right order</strong>. </p>
  
  <pre><code>// $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()
          -&gt;relatedTo($category)
          -&gt;title($base_product_titles)
          -&gt;type($types)
          -&gt;hasVariant($variant_criteria)
          -&gt;orderBy('type')
          -&gt;with([
              'usage',
              'casing',
              'series',
              'defaultVariant',
              'variants',
              'variants.tire_size',
              [
                  'variants.images',
                  [ 'withTransforms' =&gt; ['product_overview']]
              ]
          ]);
  </code></pre>
  
  <p>PS: We group the above result by title in the template using |group('title').</p>
  
  <p>I would appreciate any guidance!</p>
  
  <p>Kind regards,
  Meinhard</p>
  
Tags: <plugin-development><matrix><plugin-craftcommerce2><query><products>
Title: Filter and sort Commerce products based on Matrix blocks
ThreadText: # Filter and sort Commerce products based on Matrix blocks
  
  (Asked by: username_0 on 2019-06-14)
  
  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,
  username_0
  
  **Tags:** plugin-development, matrix, plugin-craftcommerce2, query, products
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2019-07-09. Score: 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');
  
  ```
  
  
  ---
  
  