In order to facilitate the discussion:
I understand that the “flattening” of the information required for the product_attributes_lookup table can be compute demanding.
In the example site I shared with you, there is this table: Femme Fatale Fast Finder – Femme Fatale (femme-fatale.gr)
To prepare this table with conventional SQL required about 20 joins and it was taking 15 minutes of full compute time every night for an SQL server that had 64GB allocated for the example of the said shop (with the almost 400 attributes, and 7000 items among them) and it also computes stock availability, their image, the brand and product categories as you can imagine.
I am sure when using conventional php, the compute requirement is not much different thus, while as the “good coding” patterns and practices suggest, a REST API based async method is utilised etc. (thus the 2 hours of painful waiting)
Here however is refactored the same code that does the compute not in 15 minutes of full compute but in less than 10 seconds (for all nearly 9000 products) together with complementary info such as product images, generation of add to cart buttons, the info if the product is on sale and/or has professional discounts.
Generate Simple Products
Start time: 2024-03-03 16:25:35.071
End time: 2024-03-03 16:25:35.565
Generate Variations Vitals
Start time: 2024-03-03 16:25:35.566
End time: 2024-03-03 16:25:35.914
Append Variations in Product Vitals
Start time: 2024-03-03 16:25:35.915
End time: 2024-03-03 16:25:42.145
Total Execution Time: 7.078 seconds
If one needs also a three level category menu for the products and the brands of the products then this process adds 12 more seconds.
Generate Brands and Categories
Start time: 2024-03-03 13:28:28.284
End time: 2024-03-03 13:28:40.614
Generate Simple Products
Start time: 2024-03-03 13:28:40.615
End time: 2024-03-03 13:28:41.096
Generate Variations Vitals
Start time: 2024-03-03 13:28:41.097
End time: 2024-03-03 13:28:41.431
Append Variations in Product Vitals
Start time: 2024-03-03 13:28:41.432
End time: 2024-03-03 13:28:47.837
Total Execution Time: 19.557 seconds
They key here is to use different queries for simple products and variations.
Here is the code:
Part 1. Generate category hierarchies and brands (the 12 second process)
truncate table wp_pods_product_brands;
INSERT INTO wp_pods_product_brands (name, product_id, parent_product_id, effective_product_id, brand_name, product_stock, product_count)
SELECT t.name AS name,
case when p.id is null then
tr.object_id
else
p.id
end
as product_id,
tr.object_id AS parent_product_id,
tr.object_id AS effective_product_id,
t.slug as brand_name,
'0'as product_stock,
'0' as product_count
FROM wp_term_relationships AS tr
INNER JOIN wp_term_taxonomy AS x
ON (x.taxonomy='pa_brand_master'
AND x.term_taxonomy_id=tr.term_taxonomy_id)
INNER JOIN wp_terms AS t
ON t.term_id=x.term_id
JOIN wp_posts p on
(tr.object_id=p.post_parent or tr.object_id=p.id) and p.post_type like '%product%' and p.post_status='publish'
order by product_id, name desc
LIMIT 5000000;
TRUNCATE TABLE wp_pods_parent_product_cat_hierarchy;
INSERT INTO wp_pods_parent_product_cat_hierarchy
(name, product_id, top_category_id, top_category, second_level_category_id, second_level_category, third_level_category_id, third_level_category)
SELECT
p.ID AS name,
p.ID AS product_id,
tc.top_category_id,
tc.top_category,
sc.second_level_category_id,
sc.second_level_category,
thc.third_level_category_id,
thc.third_level_category
FROM
wp_posts p
JOIN (
SELECT
tr.object_id AS product_id,
MIN(tt.term_id) AS top_category_id,
MIN(t.name) AS top_category
FROM
wp_term_relationships tr
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE tt.taxonomy = 'product_cat' AND tt.parent = 0
GROUP BY tr.object_id
) AS tc ON p.ID = tc.product_id
LEFT JOIN (
SELECT
tr.object_id AS product_id,
MIN(tt.term_id) AS second_level_category_id,
MIN(t.name) AS second_level_category
FROM
wp_term_relationships tr
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE tt.taxonomy = 'product_cat' AND tt.parent IN (SELECT term_id FROM wp_term_taxonomy WHERE parent = 0)
GROUP BY tr.object_id
) AS sc ON p.ID = sc.product_id
LEFT JOIN (
SELECT
tr.object_id AS product_id,
MIN(tt.term_id) AS third_level_category_id,
MIN(t.name) AS third_level_category
FROM
wp_term_relationships tr
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE tt.taxonomy = 'product_cat' AND tt.parent IN (SELECT term_id FROM wp_term_taxonomy WHERE parent IN (SELECT term_id FROM wp_term_taxonomy WHERE parent = 0))
GROUP BY tr.object_id
) AS thc ON p.ID = thc.product_id
WHERE p.post_type IN ('product', 'product_variation') AND p.post_status = 'publish'
GROUP BY p.ID
ORDER BY p.ID;
Part 2. Compute first the simple products and then the Variations IN DIFFERENT TABLES WITH DIFFERENT QUERIES
TRUNCATE TABLE wp_pods_product_vitals;
INSERT INTO wp_pods_product_vitals (
image_url,
name,
product_id,
parent_id,
parent_name,
parent_sku,
category_id,
brand,
menu_id,
menu_category,
head_id,
head_category,
subcat_id,
sub_category,
sku,
gtin,
soft1_id,
profdiscount,
price,
product_url,
link_to_view,
link_to_cart,
link_to_fav,
short_description,
weight,
color,
stock
)
SELECT distinct
concat('https://femme-fatale.gr/wp-content/uploads/',pm2.meta_value)
AS image_url,
p.post_title as name,
p.ID as product_id,
p.id
AS parent_id,
p.post_title
AS parent_name,
pm4.meta_value
AS parent_sku,
tapc.top_category_id as category_id,
tat.name
AS brand,
tapc.top_category_id
as menu_id,
tapc.top_category
as menu_category,
tapc.second_level_category_id
as head_id,
tapc.second_level_category
as head_category,
tapc.third_level_category_id
as subcat_id,
tapc.third_level_category
as sub_category,
pm4.meta_value as sku,
pm0.meta_value
AS gtin,
pm9.meta_value as soft1_id,
CASE WHEN pm6.meta_value IS NOT NULL THEN 'NAI'
ELSE 'OXI'
END AS ProfDiscount,
CAST(REPLACE(pm3.meta_value, ',', '.') AS DECIMAL(12,2)) as price,
concat ("https://femme-fatale.gr/?p=", p.id) AS product_url,
Concat('<a href=https://femme-fatale.gr/?p=', p.id , '>', ("Αναλυτικ? περιγραφ?" COLLATE utf8mb3_bin) ,'</a>') as link_to_view,
case when pm5.meta_value>0 THEN
Concat('<div class="loop-button-wrap button-layout2"><a href="?add-to-cart=', p.id , '" data-quantity="1" class="button product_type_simple add_to_cart_button ajax_add_to_cart" data-product_id="', p.id, ' data-product_sku="', pm4.meta_value , '" rel="nofollow">', ("Αγορ?" COLLATE utf8mb3_bin) , '</a></div>')
else ''
END
as link_to_cart,
'''' as link_to_fav,
LEFT(p.post_excerpt, 160)
AS short_description,
pm10.meta_value
AS weight,
'' AS color,
pm5.meta_value
AS stock
FROM
wp_posts as p
LEFT JOIN wp_pods_parent_product_cat_hierarchy as tapc on p.ID = tapc.product_id
LEFT JOIN wp_postmeta AS pm0 ON p.id = pm0.post_id AND pm0.meta_key = 'hwp_product_gtin'
LEFT JOIN wp_postmeta AS pm1 ON p.id = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta AS pm2 ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
LEFT JOIN wp_postmeta AS pm3 ON p.id = pm3.post_id AND pm3.meta_key = '_price'
LEFT JOIN wp_postmeta AS pm4 ON p.id = pm4.post_id AND pm4.meta_key = '_sku'
LEFT JOIN wp_postmeta AS pm5 ON p.id = pm5.post_id AND pm5.meta_key = '_stock'
LEFT JOIN wp_postmeta AS pm6 ON p.id = pm6.post_id AND pm6.meta_key = 'woocommerce_ultimate_pricing_prices' AND pm6.meta_value LIKE '%customer_%'
LEFT JOIN wp_postmeta AS pm9 ON p.id = pm9.post_id AND pm9.meta_key = 'soft1_id'
LEFT JOIN wp_postmeta AS pm10 ON p.id= pm10.post_id AND pm10.meta_key = '_weight'
LEFT JOIN wp_pods_product_brands as tat on p.id = tat.product_id
WHERE p.post_type IN ('product')
AND p.post_status = 'publish'-- statement to ensure uniqueness of product ids
group by p.ID
ORDER BY p.id, tapc.top_category;
TRUNCATE TABLE wp_pods_variation_vitals;
INSERT INTO wp_pods_variation_vitals (product_id, parent_id, sku, gtin, price, stock, special_price, weight, imagepath, soft1_id)
SELECT DISTINCT
p.ID AS product_id,
p.post_parent AS parent_id,
pm.meta_value AS sku,
pm2.meta_value AS gtin,
pm3.meta_value AS price,
pm4.meta_value AS stock,
CASE
WHEN pm6.meta_value IS NOT NULL THEN 'NAI'
ELSE 'OXI'
END AS special_price,
pm5.meta_value AS weight,
pm8.meta_value AS imagepath,
pm9.meta_value AS soft1_id
FROM
wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON p.id = pm.post_id AND pm.meta_key = '_sku'
LEFT JOIN wp_postmeta AS pm2 ON p.id = pm2.post_id AND pm2.meta_key = 'hwp_var_gtin'
LEFT JOIN wp_postmeta AS pm3 ON p.id = pm3.post_id AND pm3.meta_key = '_price'
LEFT JOIN wp_postmeta AS pm4 ON p.id = pm4.post_id AND pm4.meta_key = '_stock'
LEFT JOIN wp_postmeta AS pm5 ON p.post_parent = pm5.post_id AND pm5.meta_key = '_weight'
LEFT JOIN wp_postmeta AS pm6 ON p.id = pm6.post_id AND pm6.meta_key = 'woocommerce_ultimate_pricing_prices' AND pm6.meta_value LIKE '%customer_%'
LEFT JOIN wp_postmeta AS pm7 ON p.ID = pm7.post_id AND pm7.meta_key = 'botiga_variation_gallery'
LEFT JOIN wp_postmeta AS pm8 ON substring_index(substring_index(pm7.meta_value, ':"', -1), '";', 1) = pm8.post_id AND pm8.meta_key = '_wp_attached_file'
LEFT JOIN wp_postmeta AS pm9 ON p.id = pm9.post_id AND pm9.meta_key = 'iconic_cffv_100545_soft1_id'
WHERE (p.post_type = 'product_variation' AND p.post_status = 'publish');
3. Insert the variations you just computed in a different table in the… final destination table.
INSERT INTO wp_pods_product_vitals (
image_url,
name,
product_id,
parent_id,
parent_name,
parent_sku,
category_id,
brand,
menu_id,
menu_category,
head_id,
head_category,
subcat_id,
sub_category,
sku,
gtin,
soft1_id,
profdiscount,
price,
product_url,
link_to_view,
link_to_cart,
link_to_fav,
short_description,
weight,
color,
stock
)
SELECT distinct
concat('https://femme-fatale.gr/wp-content/uploads/',vv.imagepath) AS image_url,
p.post_title as name,
p.ID as product_id,
vv.parent_id AS parent_id,
pmmpp.post_title as parent_name,
pmmpps.meta_value as parent_sku,
tapcv.top_category_id as category_id,
-- tr.object_id as product_id,
tatv.name as brand,
tapcv.top_category_id as menu_id,
tapcv.top_category as menu_category,
tapcv.second_level_category_id
as head_id,
tapcv.second_level_category
as head_category,
tapcv.third_level_category_id
as subcat_id,
tapcv.third_level_category
as sub_category,
vv.sku as sku,
vv.gtin
AS gtin,
vv.soft1_id as soft1_id,
vv.special_price AS ProfDiscount,
CAST(REPLACE(vv.price, ',', '.') AS DECIMAL(12,2)) as price,
concat ("https://femme-fatale.gr/?p=", p.post_parent)
AS product_url,
Concat('<a href=https://femme-fatale.gr/?p=', p.id , '>', ("Αναλυτικ? περιγραφ?" COLLATE utf8mb3_bin) ,'</a>') as link_to_view,
case when p.post_parent<>0 and vv.stock >0 THEN
Concat('<div class="loop-button-wrap button-layout2"><a href="?add-to-cart=', p.id , '" data-quantity="1" class="button product_type_simple add_to_cart_button ajax_add_to_cart" data-product_id="', p.id, ' data-product_sku="', vv.sku , '" rel="nofollow">', ("Αγορ?" COLLATE utf8mb3_bin) , '</a></div>')
else ''
END
as link_to_cart,
'''' as link_to_fav,
LEFT(pmmpp.post_excerpt, 160)
AS short_description,
vv.weight
AS weight,
trim(replace (replace(p.post_title, pmmpp.post_title, ''), '- ', '') )
AS color,
vv.stock
AS stock
FROM
wp_posts AS p
LEFT JOIN wp_posts pmmp on pmmp.ID=p.id
LEFT JOIN wp_posts pmmpp on pmmpp.ID=pmmp.post_parent
left join wp_postmeta AS pmmpps on pmmpp.ID=pmmpps.post_id and pmmpps.meta_key='_sku'
LEFT JOIN wp_pods_variation_vitals AS vv ON p.id = vv.product_id
LEFT JOIN wp_pods_product_brands as tatv on p.post_parent = tatv.parent_product_id
LEFT JOIN wp_pods_parent_product_cat_hierarchy as tapcv on p.post_parent = tapcv.product_id
WHERE p.post_type IN ('product_variation')
AND p.post_status = 'publish'
-- statement to ensure uniqueness of product ids
group by p.ID
ORDER BY p.id, tapcv.top_category;
And.. as if by magic something that used to take 1200 seconds (and tremendous RAM for the mySQL) is now done 1000 times faster and can be even supported in hosting environments with 2-4GB of RAM.
I know this obsession of “good programming” with “patterns and practices”, but waiting 2 hours of bloody waiting to generate the products attributes lookup table, while also having the dynamic update failing, is definitely not good programming! In any event this is the type of functional decomposition that you have to implement in your favourite patterns and practices, if we are to see some progress.
But our current 2 hour wait it totally unacceptable.
If the Automatic team will require further consulting our company (Oxford Metadata) we will be happy to oblige. If anything else we can make up our losses for the 100s of hours of debugging – notwithstanding for the loses of the poor shop owner who, for many months, his most valuable products where disappearing from view to the guests.
This concludes my contribution to this issue for the time being. You may contact via our profile here.
