WooCommerce - Datamodel
Versie door Jeroen Strompf (overleg | bijdragen) op 10 sep 2022 om 10:47
The datamodel of WooCommerce and how to manipulate stuff through SQL.
WooCommerce-orders & order lines
Een impressie. Er zijn aanzienlijk meer velden beschikbaar, maar dit komt een heel eind. Let ook op de 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";
WooCommerce - 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;
WooCommerce-producten - Korte variant
WooCommerce-productinformatie vind je voornamelijk op twee plekken [1]:
- wp_posts
- wp_postmeta
Voorbeeld: Selecteer de belangrijkste gegevens uit wp_posts en de SKU's uit 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";
WooCommerce-producten - With sku & sku_oem
Like in the paragraph before, but with field sku_oem:
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_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.post_id
where
post_type="product"
and
post_status="publish"
and
wp_postmeta.meta_key="_sku"
and
wp_postmeta_02.meta_key="sku_oem";
WooCommerce-producten - Uitgebreide variant
- Inclusief primaire en secundaire afbeelding, prijs + uitleg, maar zonder taxonomische informatie
- MISSING: Korte productomschrijving van Yoast (zit in een eigen Yoast-tabel)
#
# 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;
WooCommerce Shop-page widgets
De widgets/filters op de shop page, worden gecodeerd in tabel wp_options in een paar velden met 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
WooCommerce order-adressen
- Order-ID's vind je in tabel
wp_posts - Adresgegevens vind je in tabel
wp_postmeta.
Voorbeeld:
# 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";