WooCommerce - Datamodel

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;

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";