Oxford Metadata Ltd

Νο @joy0114 ! They are needed indeed. But if you already have them optimized with index mysql plugin you will get this error during the upgrade to 9.9.

What I did was to revert my keys to normal in index mysql, then upgraded woocommerce, and then re-applied them, just to ensure max compatibility with woo (you never know… )

In our case it was the Gravity SMTP plugin

Gravity SMTP – WordPress SMTP Plugin

Thank you Jeff.

I take the opportunity to two issues that I am having.

I am using (all) three layers of your plugin. Global plugins, post type and per Plugin Filter.

Say I have one filter like this:
https://femme-fatale.gr/cart/*/
https://femme-fatale.gr/checkout/*/
https://femme-fatale.gr/checkout/order-received/(*)
https://femme-fatale.gr/checkout/thankyou

My aim was to have plugins like Paypal Payments, Table Rate shipping and generally plugins that are only of use to the checkout page to be globaly disabled and enabled only there.

Sadly this is not working.

Something similar applies to the product categories/archives.

I have this rule

https://femme-fatale.gr/brand_master/*/
https://femme-fatale.gr/product-category/*
https://femme-fatale.gr/product-category/*/
https://femme-fatale.gr/product-category/(*)

(all possible combinations for wildcard in the product categories) and again plugin selection does not seem to work. I have tried also the wildcard option (Fuzzy Url Matching) and it does not work either.

I wonder if this is because in our site we do not have (and for a good reason) a trailing slash in our urls. Google actually penalises them, considers them as redirects that impact the technical SEO.

Please advise.

Also Modify the lines where $orderedby is used to include an isset check:

<p class="woocommerce-result-count" <?php echo ( ! isset( $orderedby ) || empty( $orderedby ) || 1 === intval( $total ) ) ? '' : 'role="alert" aria-relevant="all" data-is-sorted-by="true"'; ?>>

And

$orderedby_placeholder = ( ! isset( $orderedby ) || empty( $orderedby ) ) ? '%2$s' : '<span class="screen-reader-text">%2$s</span>';

If you’re loading this template using wc_get_template or a similar function, make sure to pass $orderedby as part of the data array.

$orderedby = ''; // Set this based on your sorting logic.

wc_get_template( 'loop/result-count.php', array(
    'total'     => $total,
    'per_page'  => $per_page,
    'current'   => $current,
    'orderedby' => $orderedby,
) );

(Thanks to chatgpt o1-preview).

• This reply was modified 1 year, 6 months ago by Oxford Metadata Ltd.

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.

Thank you very much @shameemreza for your reply.

It looks like the development of this feature is in the right hands 🙂

When I find the time I will write a feature request, but, sadly, it is common for features coming out of the blue to be have an unfortunate fate. I cannot re-tell the story, re-explain etc. You are the owner of this feature, you now know the story, and please take it further with your team; they know you better and trust you a lot more than me.

I will just add two anecdotes.

1. The other day, I was sharing with the shop owner some of my dialogues with the guys at Fibosearch. He came back saying that “if there are so many “ifs” and “buts” enough to confuse Oxford educated computer scientists, how my girls (who do the data entry) who have barely finished High-school can know what to do without mistake?”

2. In here you will find a chart with the positive impact of moving the site to Woocommerce. Being like 10times faster than the previous (Magento) in March 2022, and also optimising the product catalogue (moving the simple products to variations in May 2023 (March-April 2023 there is a steep drop due to price increases of the products by about 15%)). The caption explains it all.

What I try to say is that what you are doing it matters – a lot actually. Sadly, it matters most to people who will remain unaware/oblivious of the impact of your choices to them (e.g. you can imagine that the owners of eshops are not happy with their staff when products “disappear”).

Anyway, these are nice stories to tell, and if I was Woocommerce, I would have publicized such case studies – success stories so to speak, as a means of compensating my partners who add value to my product, trying to make them even better.

Keep well – thanking you once more,

Dimitris.

• This reply was modified 2 years, 1 month ago by Oxford Metadata Ltd. Reason: spelling
• This reply was modified 2 years, 1 month ago by Oxford Metadata Ltd. Reason: further corrections
• This reply was modified 2 years, 1 month ago by Oxford Metadata Ltd. Reason: famous last words :)