WooCommerce - Datamodel
Versie door Jeroen Strompf (overleg | bijdragen) op 4 okt 2022 om 13:26 (→Product data - Including sku & sku_oem)
The datamodel of WooCommerce and how to manipulate stuff through SQL. This article functions as a data snippet repository.
Orders & order lines
An impression. There are considerably more fields available, but this goes a long way. Also note the self-joins:
################################################################################ # Get quite some order data ################################################################################ # select ID, post_date, post_type, post_status, wp_woocommerce_order_items.order_item_id, wp_woocommerce_order_items.order_item_type, wp_woocommerce_order_items.order_item_name, itemmeta_01.meta_value as sellprice_ex_vat, itemmeta_02.meta_value as product_post_id, wp_postmeta.meta_value as sku from # wp_posts: Basic order stuff ######################################## # wp_posts join # wp_woocommerce_order_items: Various ######################################## # wp_woocommerce_order_items on wp_posts.ID = wp_woocommerce_order_items.order_id join # wp_woocommerce_order_items (1): sellprice ######################################## # # First of several self-joins # wp_woocommerce_order_itemmeta as itemmeta_01 on wp_woocommerce_order_items.order_item_id = itemmeta_01.order_item_id join # wp_woocommerce_order_items (2): product_id ######################################## # # Product_id is used for retrieving sku further on # wp_woocommerce_order_itemmeta as itemmeta_02 on wp_woocommerce_order_items.order_item_id = itemmeta_02.order_item_id join # wp_postmeta: sku ######################################## # # Finally: Here's the sku! # wp_postmeta on wp_postmeta.post_id = itemmeta_02.meta_value where post_type = "shop_order" and post_status = "wc-completed" and itemmeta_01.meta_key = "_line_subtotal" and itemmeta_02.meta_key = "_product_id" and wp_postmeta.meta_key = "_sku";
Revenue per product
################################################################################ # Calculate revenue per product ################################################################################ # select wp_postmeta.meta_value as sku, wp_woocommerce_order_items.order_item_name, sum(itemmeta_01.meta_value) as product_revenue_ex_vat from # This table is only used to select the right related data # wp_posts join wp_woocommerce_order_items on wp_posts.ID = wp_woocommerce_order_items.order_id join # Revenue # wp_woocommerce_order_itemmeta as itemmeta_01 on wp_woocommerce_order_items.order_item_id = itemmeta_01.order_item_id join # product-post-id # wp_woocommerce_order_itemmeta as itemmeta_02 on wp_woocommerce_order_items.order_item_id = itemmeta_02.order_item_id join # wp_postmeta: sku ######################################## # # Finally: Here's the sku! # wp_postmeta on wp_postmeta.post_id = itemmeta_02.meta_value where post_type = "shop_order" and post_status = "wc-completed" and itemmeta_01.meta_key = "_line_subtotal" and wp_postmeta.meta_key = "_sku" group by sku order by product_revenue_ex_vat desc;
Product data - Short variant
WooCommerce product information is mainly found in two places [1]:
- wp_posts
- wp_postmeta
Example: Select the key data from wp_posts and the SKUs from wp_postmeta:
select wp_posts.ID as post_id, wp_posts.post_title as post_title, wp_posts.post_name as slug, wp_posts.post_content as content, # =product description wp_postmeta.meta_value as sku from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id where post_type="product" and post_status="publish" and meta_key="_sku";
Product data - With sku & sku_oem
select wp_posts.ID as post_id, wp_posts.post_title as post_title, wp_postmeta.meta_value as sku, wp_postmeta_02.meta_value as sku_oem from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id join wp_postmeta as wp_postmeta_02 on wp_posts.ID = wp_postmeta_02.post_id where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" and wp_postmeta_02.meta_key="sku_oem";
Product data - Long variant
- Includes primary and secondary image, price + explanation, but without taxonomic information
- MISSING: Short product description of Yoast (in its own Yoast table)
# # Example: Retrieve all product data ################################################################################ # use wp_tmp; # # ################################################################################ # Select ################################################################################ # select wp_posts.id as post_id, wp_posts.post_title as post_title, wp_posts.post_name as slug, wp_posts.post_content as content, wp_posts.guid as guid, wp_postmeta.meta_value as sku, wp_postmeta_03.meta_value as thumbnail_path, wp_postmeta_05.meta_value as second_image_path, wp_postmeta_06.meta_value as price from wp_posts # Join 01 on wp_postmet: "sku" ############################################################ # left join wp_postmeta on wp_posts.id = wp_postmeta.post_id # Join 02 on wp_postmeta: "_thumbnail_id" ############################################################ # # "_thumbnail_id" on its turn, is a wp_postmeta.product_id # left join wp_postmeta as wp_postmeta_02 on wp_postmeta_02.post_id = wp_posts.id and wp_postmeta_02.meta_key = "_thumbnail_id" # Join 03 on wp_postmeta: thumbnail-path ############################################################ # # This join is different from the two below, as it is a # double join: It retrieves the meta_id for the thumbnail- # attachment # left join wp_postmeta as wp_postmeta_03 on wp_postmeta_03.post_id = wp_postmeta_02.meta_value and wp_postmeta_03.meta_key = "_wp_attached_file" # Join 04 on wp_postmeta: "_product_image_gallery" ############################################################ # # Retrieve the meta_value for "_product_image_gallery": # It is the post_id of everything related to the second # image # left join wp_postmeta as wp_postmeta_04 on wp_postmeta_04.post_id = wp_posts.id and wp_postmeta_04.meta_key = "_product_image_gallery" # Join 05 on wp_postmeta: "_product_image_gallery" ############################################################ # # Retrieve the meta_value for "_product_image_gallery": # It is the post_id of everything related to the second # image # left join wp_postmeta as wp_postmeta_05 on wp_postmeta_05.post_id = wp_postmeta_04.meta_value and wp_postmeta_05.meta_key = "_wp_attached_file" # Join 06 on wp_postmeta: "_price" ############################################################ # # "_price" and "_regular_price" seem always identical → Only # fetch the first one # left join wp_postmeta as wp_postmeta_06 on wp_postmeta_06.post_id = wp_posts.id and wp_postmeta_06.meta_key = "_price" # Where clause ############################################################ # where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" # Limit ############################################################ # limit 0, 10;
Shop-page widgets
The widgets/filters on the shop page are coded in table wp_options
in a few fields with serialised content:
select option_value from wp_options where option_name = "widget_woocommerce_product_categories" # Category-widget select option_value from wp_options where option_name = "widget_woocommerce_layered_nav" # Attribute-widgets
Order adresses
- Order IDs can be found in table
wp_posts
- Address details can be found in table
wp_postmeta
.
Example:
# Retrieve a list of all countries to which there were orders ################################################################################ # # ################################################################################ # Select database ################################################################################ # use knl; ################################################################################ # All countries to which there have been orders ################################################################################ # select distinct meta_value from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id where post_type = "shop_order" and meta_key like "%country";