Multichannel eCommerce has been coming up quite often for us lately. Here, a cloud ERP, plentymarkets for example, sits on top of our customer’s merchandise management system and distributes products, stocks and prices to sales platforms such as Amazon, ebay, Zalando, Rakuten and also our own online shop.
We currently had the requirement that our customer had maintained an EAN in the merchandise management system and this was also stored in the WooCommerce shop as a GTIN. The multi-channel service provider, in this case cloudstock, could do little with this. Their software is designed to match the stock and price of the articles via the WooCommerce article number (SKU).
Our customer now had the choice of either having the trainee write all EANs into the correct field, financing a reprogramming of cloudstock’s matching procedure or asking SQL superheroes like us for help. Since we work exclusively with top customers, the decision was made quickly:
Afterthoughts
With WordPress, everything is actually a post, as it was originally intended as a blogging CMS. Pages are actually only posts without taxonomy, i.e. without categories and keywords. WooCommerce products, on the other hand, are special posts (custom post types) with fields for price and stock added. All Post Types, Native and Custom, are stored in the wp_post table in the database. Additional information about the posts, SKU and GTIN of the products in this case, are stored in the table wp_postmeta. So there we find both the GTIN of Germanized as _ts_gtin and the WooCommerce-native article number as _sku.
To write the value of _ts_gtin to _sku, we do a so-called Selfjoin based on the post ID (post_id), since both source and target are in the same table. We only want to perform this operation on products and therefore limit ourselves to entries that have a _sku at all.
With such delicate operations, which could destroy the entire database, it is extremely important to develop and test the process in a sandbox. And before the process is then applied in the productive system, it must be ensured that the state before the execution of the SQL statement can be restored. Under certain circumstances by
- activate maintenance mode
- backup affected tables
- execute the statement
- result as expected?
- if not: restore affected tables
- deactivate maintenance mode
Such workflows are absolutely necessary for systems that are constantly changing. High-turnover online shops that continuously receive orders for example.
This works not only with WooCommerce, by the way. Many plugins, and even WordPress itself, often store post information in the metadata. And if you have a handle on manipulation at the database level, you can often complete tasks in minutes that would otherwise take days and thus cost a lot of money.
We hope we have been able to bring you a little closer to SQL in connection with WordPress metadata. Perhaps this article is the first step on your personal path to becoming a database wizard.
