WooCommerce - Datamodel: verschil tussen versies
Regel 412: | Regel 412: | ||
limit | limit | ||
0, 10; | 0, 10; | ||
+ | </pre> | ||
+ | |||
+ | == Product data - Figuring out missing images == | ||
+ | |||
+ | Missing data from gMC was imported into table <code>missing_image</code>. How this was connected to existing data: | ||
+ | |||
+ | <pre> | ||
+ | # | ||
+ | # Missing images - Overview, diagnosis, etc. | ||
+ | ################################################################################ | ||
+ | # | ||
+ | # | ||
+ | ################################################################################ | ||
+ | # Select db | ||
+ | ################################################################################ | ||
+ | # | ||
+ | use tmp; | ||
+ | |||
+ | |||
+ | ################################################################################ | ||
+ | # Retrieve all info | ||
+ | ################################################################################ | ||
+ | # | ||
+ | select | ||
+ | missing_image.title as gmc_title, | ||
+ | missing_image.image_link as gmc_image_url, | ||
+ | missing_image.file_name as gmc_file_name, | ||
+ | wp_posts.ID as wp_post_id, | ||
+ | concat("https://example.com/",wp_posts.post_name) as wp_product_url, | ||
+ | wp_postmeta_sku.meta_value as wp_sku, | ||
+ | wp_postmeta_sku_oem.meta_value as wp_sku_oem, | ||
+ | concat("https://example.com/wp-content/uploads/",wp_postmeta_thumbnail_path.meta_value) as wp_image_url | ||
+ | from | ||
+ | missing_image | ||
+ | join | ||
+ | wp_posts | ||
+ | on | ||
+ | missing_image.title = wp_posts.post_title | ||
+ | join | ||
+ | wp_postmeta as wp_postmeta_sku | ||
+ | on | ||
+ | wp_posts.ID = wp_postmeta_sku.post_id | ||
+ | join | ||
+ | wp_postmeta as wp_postmeta_sku_oem | ||
+ | on | ||
+ | wp_posts.ID = wp_postmeta_sku_oem.post_id | ||
+ | join | ||
+ | # | ||
+ | # Retrieve _thumbnail_id | ||
+ | # | ||
+ | wp_postmeta as wp_postmeta_thumbnail_id | ||
+ | on | ||
+ | wp_posts.ID = wp_postmeta_thumbnail_id.post_id | ||
+ | join | ||
+ | # | ||
+ | # Retrieve _thumbnail-path | ||
+ | # | ||
+ | wp_postmeta as wp_postmeta_thumbnail_path | ||
+ | on | ||
+ | wp_postmeta_thumbnail_path.post_id = wp_postmeta_thumbnail_id.meta_value | ||
+ | where | ||
+ | wp_postmeta_sku.meta_key = "_sku" | ||
+ | and | ||
+ | wp_postmeta_sku_oem.meta_key = "sku_oem" | ||
+ | and | ||
+ | wp_postmeta_thumbnail_id.meta_key = "_thumbnail_id" | ||
+ | and | ||
+ | wp_postmeta_thumbnail_path.meta_key = "_wp_attached_file"; | ||
</pre> | </pre> | ||
Versie van 20 dec 2022 12:06
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 - With sku, sku_oem & prices
In this case, both _price
and _regular_price
contains the same price. However, I think only one of these fields is actually used (I don't know which one right now).
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, wp_postmeta_03.meta_value as price 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 join wp_postmeta as wp_postmeta_03 on wp_posts.ID = wp_postmeta_03.post_id where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" and wp_postmeta_02.meta_key="sku_oem" and wp_postmeta_02.meta_value="1824" and ( wp_postmeta_03.meta_key="_price" or wp_postmeta_03.meta_key="_regular_price" );
With these two price-related fields both included in the output:
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, wp_postmeta_03.meta_value as price, wp_postmeta_04.meta_value as price_regular 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 join wp_postmeta as wp_postmeta_03 on wp_posts.ID = wp_postmeta_03.post_id join wp_postmeta as wp_postmeta_04 on wp_posts.ID = wp_postmeta_04.post_id where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" and wp_postmeta_02.meta_key="sku_oem" and wp_postmeta_03.meta_key="_price" and wp_postmeta_04.meta_key="_regular_price";
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)
- Note that most of the constraints on the self-joins, are included as relations, not as WHERE-clauses. This might be less confusing.
# # 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;
Product data - Figuring out missing images
Missing data from gMC was imported into table missing_image
. How this was connected to existing data:
# # Missing images - Overview, diagnosis, etc. ################################################################################ # # ################################################################################ # Select db ################################################################################ # use tmp; ################################################################################ # Retrieve all info ################################################################################ # select missing_image.title as gmc_title, missing_image.image_link as gmc_image_url, missing_image.file_name as gmc_file_name, wp_posts.ID as wp_post_id, concat("https://example.com/",wp_posts.post_name) as wp_product_url, wp_postmeta_sku.meta_value as wp_sku, wp_postmeta_sku_oem.meta_value as wp_sku_oem, concat("https://example.com/wp-content/uploads/",wp_postmeta_thumbnail_path.meta_value) as wp_image_url from missing_image join wp_posts on missing_image.title = wp_posts.post_title join wp_postmeta as wp_postmeta_sku on wp_posts.ID = wp_postmeta_sku.post_id join wp_postmeta as wp_postmeta_sku_oem on wp_posts.ID = wp_postmeta_sku_oem.post_id join # # Retrieve _thumbnail_id # wp_postmeta as wp_postmeta_thumbnail_id on wp_posts.ID = wp_postmeta_thumbnail_id.post_id join # # Retrieve _thumbnail-path # wp_postmeta as wp_postmeta_thumbnail_path on wp_postmeta_thumbnail_path.post_id = wp_postmeta_thumbnail_id.meta_value where wp_postmeta_sku.meta_key = "_sku" and wp_postmeta_sku_oem.meta_key = "sku_oem" and wp_postmeta_thumbnail_id.meta_key = "_thumbnail_id" and wp_postmeta_thumbnail_path.meta_key = "_wp_attached_file";
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";