Database model - Product data

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

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

  • Field sku is stored in table wp_postmeta with key _sku
  • Field sku_oem is stored in table wp_postmeta with key sku_oem
  • Since we need to include two things from the same table, a self-join (?) is needed: Twice the same relation but under different names - Confusing stuff
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 for now everything related to a relation, is located at the same spot.
#
# 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_postmete: "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";

Product data - Retrieve all unique meta_keys

For situations where I can't remember the exact meta_key that I'm looking for: 

################################################################################
# Retrieve all distinct meta_keys for products
################################################################################
#
select distinct
   meta_key
from
   wp_postmeta
join
   wp_posts
   on
   wp_postmeta.post_id = wp_posts.ID
where
   wp_posts.post_type="product";

Product data - All products without EAN

################################################################################
# Retrieve all products without EAN
################################################################################
#
select
   ID,
   wp_postmeta_sku.meta_value as sku,
   post_title
from
   wp_posts
left join
   wp_postmeta
   on
   wp_postmeta.post_id = wp_posts.ID
   and
   wp_postmeta.meta_key like "%ean"
join
   wp_postmeta as wp_postmeta_sku
   on
   wp_postmeta_sku.post_id = wp_posts.ID
   and
   wp_postmeta_sku.meta_key like "%sku"
where
   wp_posts.post_type="product"
   and
   (
      wp_postmeta.meta_value is null
      or
      wp_postmeta.meta_value = ""
   );

Product data - All content

Retrieve all content to check translations. So no figures or numbers. Only text. The resulting query is suprisingly small: 

################################################################################
# Retrieve all product-related content
################################################################################
#
select
   wp_posts.ID,
   wp_posts.post_name,
   wp_posts.post_title,
   wp_posts.post_content,
   wp_posts.post_excerpt,
   wp_postmeta_post_tools.meta_value as post_tools,
   wp_postmeta_focuskw.meta_value as yoast_focuskw
#
#    wp_postmeta.meta_key,
#    wp_postmeta.meta_value
#
from
   wp_posts
join
   wp_postmeta as wp_postmeta_focuskw
   on
   wp_posts.ID = wp_postmeta_focuskw.post_id
join
   wp_postmeta as wp_postmeta_post_tools
   on
   wp_posts.ID = wp_postmeta_post_tools.post_id
#
# join
#    wp_postmeta
#    on
#    wp_posts.ID = wp_postmeta.post_id
#
where
   wp_posts.post_type="product"
   and
   wp_posts.post_status="publish"
   and
   wp_postmeta_focuskw.meta_key="_yoast_wpseo_focuskw"
   and
   wp_postmeta_post_tools.meta_key="post_tools";
Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Database_model_-_Product_data&oldid=36971"