The WooCommerce API was very slow for retrieving stock information for variations so I went directly to the database and it was super fast!
A client of mine sells tights for children. Customers asked her for a “Shop by Age” page to help find in stock products in a certain size. This would be very helpful when stock is low. Without such a page customers have to view each product, choose click the size dropdown and then see whether it is in stock. Tedious and may have resulted in lost customers.
The right way … was very slow
I knew that most WooCommerce data is stored in the wp_postmeta table so retrieving the stock information for all the variations of each product would require a lot of database queries. This would break a site if the stock info was calculated on each page load so I decided to write a standalone script to gather the info and store the results for a “Shop by Age” page to retrieve.
My initial attempts used the WP_Query to get a list of product IDs and then WooCommerce API to get variation information for each product (calling get_variation_attributes() and then get_available_variations()). This was very slow. For one product it took about 8 seconds and over 60 queries. Although the shop only had 53 products, this wasn’t going to work.
Reluctant move to direct database queries
I asked for advice on the Advanced WooCommerce group on Facebook. I described the slow query times while using the API. It was suggested that I directly query the database. I wanted to avoid this as it would be vulnerable to future changes in how WooCommerce data is stored but it looked like the only good option.
I examined the wp_postmeta table contents and found relevant rows and did a few queries in phpMyAdmin to retrieve the info in as few queries as possible. I only needed to run 2 queries for each product. I must note that the site uses Attributes and each product only has a single variation (Size) based on this attribute. The code here will not work for other sites without modification and may need extra queries to get the stock information for multiple variations.
During development I had a timer running on the script and saw it drop to 0.2 seconds with this method. I was only examining a single product while working on the code, but when I changed it to process all products the time barely changed!! Even with the non-generic code, it was infinitely better than using the super slow API. I expect that generic code would also be very fast.
I stored the collected data in the wp_postmeta table with post ID 1. I knew that this post ID did not exist (it would have been the sample ‘Hello world!‘ post) so I knew that it was safe from being accidentally deleted.
Shortcode to call another shortcode
I wrote a
[ size-in-stock ] shortcode to call the
[ products ] shortcode to display the products. The shortcode retrieves the list of IDs from wp_postmeta and then passes them to the
[ products ] shortcode.
While reading the documentation about attributes I saw that attributes made available a new URL. For example, the “0-6 months” variation (with ‘0-6-months‘ slug) is available at: http://www.slugsandsnails.ie/pa_size/0-6-months/.
While that does list all products available in that size, it does not check the stock level so that brings us back to the initial problem where customers had to go into each product and select the size to find out whether that size is in stock. Phew, my work wasn’t wasted.