Bulk updating woocommerce variation prices efficienty-CodePudding

I'm trying to get prices from an external source and update my product prices in my woocomerce site in a daily basis.

My problem is I have a ton of variations (150 products, each product has 10-15 variations, over 2000 variations in total and growing) and when i try to update the price for each variation in a loop it takes so much time and uses over 3Gb of memory.

I have managed to make it a lot faster by using

update_post_meta($variation_id, '_sale_price', $price)

for changing the price instead of

$variation->set_sale_price($price) 
$variation->save()

but im not sure it is safe to bypass woocommerce api and update prices directly.

Am I doing it correctly? If not is there a better way to do this?

CodePudding user response：

I would look at https://actionscheduler.org/ which is bundled along with woocommerce in later versions. It can handle larger tasks in the background and runs the job(s) as resources permit.

You can schedule a repeating action scheduler job to query all the products (using wc_get_products(), https://github.com/woocommerce/woocommerce/wiki/wc_get_products-and-WC_Product_Query) to say every hour.

Then, as you loop through the $products, you can schedule a single action scheduler event for each simple product or variation. Each product update will get an AS event. You can see Action Scheduler events via the WooCommerce->Settings->Action Scheduler area.

This way you can continue to use the CRUD methods (like ->save() etc.)

Using action scheduler takes a bit of getting used to, but I use it now for every maintenance task that hogs lots of resources.

CodePudding user response：

I ended up doing the update_post_meta method but I ran into minor issues like when I remove sale from a product it still shows up in on sale products in my website.

I did some search and I found out it's because woocommerce uses a table called wc_product_meta_lookup for getting product data faster, and because its not getting updated when i'm changing prices, the onsale column remains the same. I tried using wc_update_product_lookup_tables() but it had no effect.

So I fixed it by updating the said column for each variation manually using wpdb, and then clearing woocommerce transients after I finished updating all products:

global $wpdb;
$wpdb->update( 'wp_wc_product_meta_lookup', ['onsale' => $is_on_sale], ['product_id'=> $variation_id]); 

delete_transient( 'wc_products_onsale' );