Forum

Thread tagged as: Question, Shop

perch_shop_product_variants() - display variants as individual products

Hi Drew, I see that perch_shop_product_variants() has been added in 1.0.9. Does this make it possible to display variants in a list as if they were individual products?

I tested the function by creating a new product and generating variants for it. The output is just that main product. Are we at the point where we get variants to display as individual products in a product list?

Thanks!

Olympia Kyriakides

Olympia Kyriakides 1 points

  • 4 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

What problem were you seeing with the output?

Looking again, I'm actually not getting anything output with perch_shop_product_variants

I've tried to pass in some options, nothing.

I'm using the default template, and also added <perch:variants> tags to see if that would make a difference.

I have a product set up and variants generated.

Time    Δ   Debug Message
0.1354  0   [32] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID ORDER BY pr.routeOrder ASC, p.pagePath ASC
0.1367  0.0013  Matched page: /shop, so not using routes.
0.1368  0.0001  Using master page: /templates/pages/shop/index.php
0.1448  0.008   [1] SELECT * FROM perch2_members_sessions WHERE sessionID='faa4b1caf0d88bec0a4c09208044ac1b4be089b2' AND sessionHttpFootprint='1d17425e9d1cd11d5d857bd3d1b870de6e97430c' AND sessionExpires>'2016-10-14 12:21:33' LIMIT 1
0.1458  0.001   User is logged in
0.1522  0.0064  [1] SELECT * FROM perch2_pages WHERE pagePath='/shop' LIMIT 1
0.1666  0.0144  [1] SELECT * FROM perch2_shop_cart WHERE cartID=1357
0.1968  0.0303  [1] SELECT * FROM perch2_shop_cart WHERE cartID=1357
0.1977  0.0008  [1] SELECT regionID, regionTemplate, regionPage, regionRev AS rev FROM perch2_content_regions WHERE regionKey='Header' AND (regionPage='/shop' OR regionPage='*')
0.1982  0.0005  [1] SELECT * FROM ( SELECT idx.itemID, c.regionID, idx.pageID, c.itemJSON, idx2.indexValue as sortval FROM perch2_content_index idx JOIN perch2_content_items c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.regionID=c.regionID JOIN perch2_content_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE ((idx.regionID=34 AND idx.itemRev=73)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, pageID, itemJSON, sortval ORDER BY sortval ASC LIMIT 0, 1
0.3278  0.1296  [5] Using template: /templates/content/header.html
0.3354  0.0076  Using cart from cache.
0.3371  0.0017  [54] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
0.3632  0.0261  Using template: /templates/shop/cart/cart_dropdown.html
0.3698  0.0066  [1] SELECT regionID, regionTemplate, regionPage, regionRev AS rev FROM perch2_content_regions WHERE regionKey='account_link' AND (regionPage='/shop' OR regionPage='*')
0.3705  0.0006  [1] SELECT * FROM ( SELECT idx.itemID, c.regionID, idx.pageID, c.itemJSON, idx2.indexValue as sortval FROM perch2_content_index idx JOIN perch2_content_items c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.regionID=c.regionID JOIN perch2_content_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE ((idx.regionID=105 AND idx.itemRev=51)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, pageID, itemJSON, sortval ORDER BY sortval ASC LIMIT 0, 1
0.3945  0.0241  [1] Using template: /templates/content/account_link.html
0.3998  0.0052  [1] SELECT * FROM perch2_shop_products WHERE productSlug='Variant-Test-BLK-BLK' AND productDeleted IS NULL LIMIT 1
0.4009  0.0012  [65] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='status' AND idx.indexValue='1')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
0.4648  0.0639  [48] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='parentID' AND idx.indexValue='')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
0.5403  0.0754  [nil] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' AND idx.itemID IN ('1', '13', '14', '15', '16', '17', '18', '19', '30', '36', '40', '41', '42', '43', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99') AND idx.itemID NOT IN ('1', '14', '18', '19', '30', '36', '40', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93') WHERE 1=1 AND ((idx.indexKey='parentID' AND idx.indexValue='')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC
0.5606  0.0203  Using template: /templates/shop/products/variant_list.html
0.561   0.0005  [1] SELECT collectionID, collectionTemplate FROM perch2_collections WHERE collectionKey='shop_page_title_img'
0.5841  0.023   [1] SELECT * FROM ( SELECT idx.itemID, ci.collectionID, ci.itemJSON, idx2.indexValue as sortval FROM perch2_collection_index idx JOIN perch2_collection_items ci ON idx.itemID=ci.itemID AND idx.itemRev=ci.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_revisions cr ON idx.itemID=cr.itemID AND idx.itemRev=cr.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE (idx.collectionID=13) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, itemJSON, sortval ORDER BY sortval ASC LIMIT 0, 1
0.6697  0.0856  [1] Using template: /templates/content/image.html
0.6709  0.0012  [21] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='_category' AND idx.indexValue LIKE 'products/handbags%' OR idx.indexKey='_category' AND idx.indexValue='products/handbags'))
0.7215  0.0506  [65] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='status' AND idx.indexValue='1')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
0.7237  0.0022  [48] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='parentID' AND idx.indexValue='')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
0.7253  0.0016  [9] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' AND idx.itemID IN ('1', '14', '40', '41', '42', '43', '52', '49', '50', '51', '53', '54', '55', '56', '60', '84', '90', '93', '57', '58', '59') AND idx.itemID IN ('1', '13', '14', '15', '16', '17', '18', '19', '30', '36', '40', '41', '42', '43', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99') AND idx.itemID IN ('1', '14', '18', '19', '30', '36', '40', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93') WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC
1.0674  0.3421  [9] Using template: /templates/shop/products/list.html
1.0686  0.0012  [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=40 AND productDeleted IS NULL ORDER BY productOrder ASC
1.0727  0.0042  [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=40 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
1.0853  0.0126  [4] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=4 AND valueDeleted IS NULL ORDER BY valueOrder ASC
1.086   0.0006  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='40' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='4' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
1.1019  0.016   [3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=93 AND productDeleted IS NULL ORDER BY productOrder ASC
1.1032  0.0013  [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=93 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
1.1047  0.0015  [3] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
1.105   0.0003  [3] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='93' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
1.1084  0.0034  [17] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='_category' AND idx.indexValue LIKE 'products/wallets%' OR idx.indexKey='_category' AND idx.indexValue='products/wallets'))
1.1395  0.031   [65] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='status' AND idx.indexValue='1')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
1.1423  0.0028  [48] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='parentID' AND idx.indexValue='')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
1.1443  0.002   [10] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' AND idx.itemID IN ('18', '19', '30', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '91', '92') AND idx.itemID IN ('1', '13', '14', '15', '16', '17', '18', '19', '30', '36', '40', '41', '42', '43', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99') AND idx.itemID IN ('1', '14', '18', '19', '30', '36', '40', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93') WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC
1.2707  0.1264  [10] Using template: /templates/shop/products/list.html
1.2864  0.0157  [17] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='_category' AND idx.indexValue LIKE 'products/accessories%' OR idx.indexKey='_category' AND idx.indexValue='products/accessories'))
1.3289  0.0425  [65] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='status' AND idx.indexValue='1')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
1.3308  0.0019  [48] SELECT DISTINCT idx.itemID FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' AND ((idx.indexKey='parentID' AND idx.indexValue='')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
1.3322  0.0015  [11] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' AND idx.itemID IN ('36', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '85', '86', '87', '88', '89') AND idx.itemID IN ('1', '13', '14', '15', '16', '17', '18', '19', '30', '36', '40', '41', '42', '43', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99') AND idx.itemID IN ('1', '14', '18', '19', '30', '36', '40', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93') WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC
1.4256  0.0933  [11] Using template: /templates/shop/products/list.html
1.4431  0.0175  [13] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/shop' OR regionPage='*' ORDER BY regionPage DESC
1.4736  0.0305  Using template: /templates/search/search-form.html
1.4782  0.0045  Request time: 1.4781
1.4782  0   Process time: 1.4771
1.4782  0   Memory: 7.0199
Drew McLellan

Drew McLellan 2638 points
Perch Support

Can you show me the function cal you're using?

<?php perch_shop_product_variants(); ?>

I've also tried passing in the main product id and slugs, as well as:
<?php perch_shop_product_variants('', array('template' => 'products/variant_list.html', 'variants' => true)); ?>
<?php perch_shop_product_variants('Variant-Test-BLK-BLK', array('template' => 'products/variant_list.html', 'variants' => true)); ?>

Drew McLellan

Drew McLellan 2638 points
Perch Support

You need to pass in the product slug:

perch_shop_product_variants('my-nice-product');

Okay :) I've got that working now, thank you!

Is there a way to display a list of all variants (cross-product) a la perch_shop_products ?

Drew McLellan

Drew McLellan 2638 points
Perch Support

Not currently. When would that be useful?

I have a shop that has about 30 SKUs (variations) but only 5 "parent" products.
To make the shop look more robust, I'd like to display all of the SKUs in the main product listing.

Currently, I'm achieving this by setting up each SKU/variation as it's own product. Doing this with parent products and variations would be more manageable, especially as the shop expands.

Thanks Drew!

Drew McLellan

Drew McLellan 2638 points
Perch Support

At the moment we filter out variants from product listings (variants are just products with a parent). I think we could probably add an option to include variants. Would you need to be able to exclude parent products when variants exist and not when they don't? That might be a bit more expensive.

:) Excluding parents would definitely be useful! And, yes, probably more important is including parents when variants don't exist.
I could see a simpler "include variants" option working though--maybe set up a parent as the "baseline" variant (i.e. color: black, and then add other colors as variants and display all).
I would also need to configure the detailed product view so that the appropriate info is there and the inappropriate info is not there for the parent vs. variants. At this point I've not had much success getting variants to look like their own products. I see how they are meant to supplement, not replace a product listing. The more I get into variants the less I'm sure about this approach. What do you think?