5 edited title
| link

Filter and sort commerceCommerce products based on matrixMatrix blocks

4 Shortened the question, with less background insights
source | link

TL;DR: I would like to extend an existing ProductQuery with two innerJoins on a Matrix field (see the second code example) but am getting duplicate rowsI would like to extend an existing ProductQuery with two innerJoins on a Matrix field. How can I innerJoin on the subQuery?

This website displays products (bicycle tires) on multiple levels of pages, based onThe matrix blocks represent product groups, (≠ product typeby title; "contain"). As an example: Level 1: "Road" [categories] -> Level 2: "Criterium" [product group] -> Level 3: "Criterium Ultra" & "Criterium Pro" [products] -> Level 4: "Criterium Ultra Red" [variant]

These and sets the order of the product groups are. I am currently saved as matrix blocks per product categorygetting duplicate rows (e.g "Roadsee code in first answer), based. I think I might have to innerJoin on their base titlethe subQuery (e.g. "Critierium" | It would've been too much of an effort for the client to add each product as a relation and keep it updatedgenerated below). Additionally the sorting of product groups per category is done here. In hindsight I might've done things differently, but it's a bit too late now - and we have a solution.?

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

After this wePS: We group the above result by title and loop through the matrix blocks to ensure it's in the correct order. This is where my question comes in: How can I extend the query (eithertemplate using joins on the existing query or coding my own which then needs to be maintained) to do all of this in one step?

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 |group($base_products_query->subQuery equals NULL'title')?.

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

TL;DR: I would like to extend an existing ProductQuery with two innerJoins on a Matrix field (see the second code example) but am getting duplicate rows. How can I innerJoin on the subQuery?

This website displays products (bicycle tires) on multiple levels of pages, based on product groups, (≠ product type). As an example: Level 1: "Road" [categories] -> Level 2: "Criterium" [product group] -> Level 3: "Criterium Ultra" & "Criterium Pro" [products] -> Level 4: "Criterium Ultra Red" [variant]

These product groups are currently saved as matrix blocks per product category (e.g "Road), based on their base title (e.g. "Critierium" | It would've been too much of an effort for the client to add each product as a relation and keep it updated). Additionally the sorting of product groups per category is done here. In hindsight I might've done things differently, but it's a bit too late now - and we have a solution.

Now, we're currently using this code to get the products and it works. However, it's not sorted based on the matrix block order and not grouped, and thus not very efficient.

After this we group by title and loop through the matrix blocks to ensure it's in the correct order. This is where my question comes in: How can I extend the query (either using joins on the existing query or coding my own which then needs to be maintained) to do all of this in one step?

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

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.

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

3 added 194 characters in body
source | link

TL;DR: I would like to extend an existing ProductQuery with two innerJoins on a Matrix field (see the second code example) but am getting duplicate rows. How can I innerJoin on the subQuery?

This website displays products (bicycle tires) on multiple levels of pages, based on product groups, (≠ product type). As an example: Level 1: "Road" [categories] -> Level 2: "Criterium" [product group] -> Level 3: "Criterium Ultra" & "Criterium Pro" [products] -> Level 4: "Criterium Ultra Red" [variant]

This website displays products (bicycle tires) on multiple levels of pages, based on product groups, (≠ product type). As an example: Level 1: "Road" [categories] -> Level 2: "Criterium" [product group] -> Level 3: "Criterium Ultra" & "Criterium Pro" [products] -> Level 4: "Criterium Ultra Red" [variant]

TL;DR: I would like to extend an existing ProductQuery with two innerJoins on a Matrix field (see the second code example) but am getting duplicate rows. How can I innerJoin on the subQuery?

This website displays products (bicycle tires) on multiple levels of pages, based on product groups, (≠ product type). As an example: Level 1: "Road" [categories] -> Level 2: "Criterium" [product group] -> Level 3: "Criterium Ultra" & "Criterium Pro" [products] -> Level 4: "Criterium Ultra Red" [variant]

2 edited tags
| link
1
source | link