LocoLoic wrote:Hi all,
We're setting up a store that sells a lot of products that come in different colours. We've set this up by having the top-level product, then setting up a 'colour' attribute, and then creating the individual colours for that product line, along with associated images. All good so far.
The problem is this:
1) We now need to generate all possible combinations in order to list volume of stock of each colour. Is it possible to do this without going into each and every product we sell, and clicking on the 'generate all combinations' button?
2) We're going to perform a full audit of our stock prior to migrating to our new site. Ideally, I'd like us to populate a spredsheet in the warehouse, then import this to all our products, and sub-products. Has someone created a SQL script for this that I can pinch, or shall I just develop my own?
Thanks in advance.
It's possible to generate the XML in SQL. Assuming you have your legacy data imported/accessible in the db and you have already populated the following Attribute tables:
ProductAttribute
ProductAttributeValue
Product_ProductAttribute_Mapping
I split my queries into 3 cases:
1 combination (color only)
2+ combinations (size and color)
Grouped Products using combinations of simple products
I migrated my attribute names exactly so they can be matched on later, or you could temporarily set the table to allow index inserts and match on them (as I did for master products).
I concatenated my legacy attribute IDs into a CSV string so they could be easily parsed with STRING_SPLIT, which is available from SQL 2016 on. This made figuring out 2+ combinations a little easier later on.
2+ combinations gets fairly complex quickly, but below should get you going in the right direction for 1 combo.
INSERT INTO ProductAttributeCombination (
--Id,
ProductId,
AttributesXml,
StockQuantity,
AllowOutOfStockOrders,
Sku,
ManufacturerPartNumber,
Gtin,
OverriddenPrice,
NotifyAdminForQuantityBelow,
PictureId
)
SELECT DISTINCT
--Id
--ProductId (master)
nop.ProductId,
--AttributesXml
(SELECT Id AS "@ID",
(SELECT Id AS "ProductAttributeValue/Value"
FROM ProductAttributeValue val
WHERE ProductAttributeMappingId = map.Id
AND legacyOptions.optionDescrip = val.[Name]
FOR XML PATH (''), TYPE)
FROM Product_ProductAttribute_Mapping map
WHERE map.Id = nop.Id
FOR XML PATH ('ProductAttribute'), root ('Attributes')),
--StockQuantity
legacyStock.inventory,
--AllowOutOfStockOrders
1,
--Sku
legacyStock.actualSKU,
--ManufacturerPartNumber
null,
--Gtin
null,
--OverriddenPrice
null,
--NotifyAdminForQuantityBelow
0,
--PictureId
0
FROM [YOUR_STOCK_TABLE] legacyStock
INNER JOIN dbo.Product_ProductAttribute_Mapping nop
ON legacyStock.idProduct = nop.ProductId
INNER JOIN [YOUR_OPTION_TABLE] legacyOptions
ON legacyOptions.idOption IN (
SELECT value
FROM STRING_SPLIT(legacyStock.optionsCSV, ',')
)
--limit to 1 option combo only (no commas exist)
WHERE CHARINDEX(',', legacyStock.OptionsCSV) = 0
--testing
--AND legacyStock.idProduct = 2047;