Woocommerce 2.6.x MySQL Query / View for Product attributes

Starting to get my head around the way Woocommerce MySQL is setup.

This is an exercise in creating MYSQL reusable views to flattern Woocommerce Product attributes for use in subsequent queries

How does WordPress/Woocommerce store entity attributes

My observation is that usually WordPress plugins don’t amend the database, they usually use existing tables, with a key-value pair holding entity attributes. To facilitate this a primary entity table has a child metadata table.  For example posts table has child table postmeta.  Each Post entity attribute is stored as a individual key-value pair record in postmet table.

Whilst Woocommerce does add tables to the database for the Woocommerce plugin itself, it still follows the above pattern to a large degree.  Both in use of standard WordPress posts & postmeta tables for product & order information, and in the way it structures & uses some of it’s own custom tables e.g. order_items & order_Itemsmeta

Note our WordPress table prefix as per the queries below is wp1_ rather than the default wp_

Products are Posts of specific types

Products are entries in table Posts of specific types.  Here’s the post types from a Woocommerce online shop that has been running for a while

Query:

select distinct post_type from wp1_posts po;

Result:

attachment
page
post
product
product_variation
revision
shop_order

Result observations:

  • Great, so we can restrict our query to wp1_posts.post_type = ‘product’ & ‘product_variation’

Products Have Attributes stored as key value pairs in postmeta

So lets see what all the available attributes keys are per postmeta.meta_key when posts.post_type = ‘product’ or  ‘product_variation’

Query:

select distinct post_type, meta_key
from wp1_posts po
join wp1_postmeta pm on pm.post_id = po.id
where post_type like 'product%'
order by post_type, meta_key;

Result (fresh dump as at 20200101:

product _area
product _backorders
product _crosssell_ids
product _default_attributes
product _downloadable
product _edit_last
product _edit_lock
product _featured
product _height
product _length
product _m4m_cost
product _m4m_ok
product _m4m_pnp_cost
product _m4m_purchase_note
product _m4m_qty_bought
product _m4m_qty_inv
product _m4m_repl_cost
product _m4m_shrinkage
product _m4m_stocktake20180331
product _manage_stock
product _max_price_variation_id
product _max_regular_price_variation_id
product _max_sale_price_variation_id
product _max_variation_price
product _max_variation_regular_price
product _max_variation_sale_price
product _min_price_variation_id
product _min_regular_price_variation_id
product _min_sale_price_variation_id
product _min_variation_price
product _min_variation_regular_price
product _min_variation_sale_price
product _oembed_01af85412e09643bd0ed3ae8d29ba883
product _oembed_0af7cec940a848e74fe612f4964a4d20
product _oembed_18c968bcd1ee02d1506b7786d283d419
product _oembed_38f4ad83955337a761f32f8351fd5c39
product _oembed_6250677cd9fcc7c8ede57b5453fa9095
product _oembed_72ee9ecb4f44a131e410b72f429e440f
product _oembed_83927576f5ad5cdf345791f0d9c61f90
product _oembed_94a5ae9b40da89ba4029f3af17a5ecf5
product _oembed_b818d48b3a2877078b7f924e5c544cc4
product _oembed_dc82f770976699958229a9c9aaaccc54
product _oembed_e879bb3d74f78549bfd18a396b4514c3
product _oembed_time_18c968bcd1ee02d1506b7786d283d419
product _oembed_time_6250677cd9fcc7c8ede57b5453fa9095
product _oembed_time_72ee9ecb4f44a131e410b72f429e440f
product _oembed_time_83927576f5ad5cdf345791f0d9c61f90
product _oembed_time_b818d48b3a2877078b7f924e5c544cc4
product _price
product _product_addons
product _product_addons_exclude_global
product _product_attributes
product _product_image_gallery
product _product_version
product _psad_featured_order
product _psad_onsale_order
product _purchase_note
product _regular_price
product _sale_price
product _sale_price_dates_from
product _sale_price_dates_to
product _sku
product _sold_individually
product _stock
product _stock_status
product _tax_class
product _tax_status
product _thumbnail_id
product _upsell_ids
product _virtual
product _visibility
product _volume
product _wc_average_rating
product _wc_measurement_price_calculator_min_price
product _wc_price_calculator
product _wc_price_calculator_pricing_rules
product _wc_rating_count
product _weight
product _width
product _woocommerce_add_fees_product
product _wp_desired_post_slug
product _wp_old_date
product _wp_old_slug
product metaKey
product total_sales
product_variation _download_expiry
product_variation _download_limit
product_variation _downloadable
product_variation _downloadable_files
product_variation _height
product_variation _length
product_variation _manage_stock
product_variation _price
product_variation _regular_price
product_variation _sale_price
product_variation _sale_price_dates_from
product_variation _sale_price_dates_to
product_variation _sku
product_variation _stock_status
product_variation _thumbnail_id
product_variation _variation_description
product_variation _virtual
product_variation _weight
product_variation _width
product_variation _woocommerce_add_fees_product
product_variation attribute_length 


Result observations:


    

  • Available/used attributes change over time, as edits are made, and as plugins are installed/used.  So two installations will very likely not return the same list due to:

      

    • different Woocommerce versions
      • 

    • different plugins
      • 

    • different plugin versions
      • 

    • variation in how products are created & maintained
      • 

    • custom user product attributes
      • 

    

    • 

  • In the results below I see some left overs from Plugins we tried and removed (e.g. _wc_measurement_price_calculator_min_price).. maybe a future cleanup job beckons..

      

    • ToDo: Cleanup orphaned attributes
      • 

    

    • 

  • The result list is all current attributes across all entered products. It is the superset in that not every product has every attribute.
    • 

  • In this store we also have custom user product attributes we have previously added which we have clearly identified via the prefix _m4m.  Depending on how you originally named your custom attributes they may or may not be as easily identifiable:

    _m4m_cost
_m4m_ok
_m4m_pnp_cost
_m4m_purchase_note
_m4m_qty_bought
_m4m_qty_inv
_m4m_repl_cost
_m4m_shrinkage
    

    • 



Proof of concept: Flattern attributes into 1 record per product


Now I want a view that returns all the above but as 1 row per product


For proof of concept lets do a select for just 2 product attributes first e.g. height & length and we will limit it to the first 10 products found.  By using max(case when…) statements to find the the value for each attribute, and grouping (aggregating) by product.ID we can we can output 1 row per product


Warning: This approach makes the assumption that per product there is sufficient integrity that an attribute appears only once per product:


    

  • ToDo: Create query to check product attributes occur only once per product
    • 



Query:


select po.ID
 ,max(case when meta_key = '_height' then meta_value else null end) as _height
 ,max(case when meta_key = '_length' then meta_value else null end) as _length
from wp1_posts po
left outer join wp1_postmeta pm
 on pm.post_id = po.ID
where post_type = 'product'
group by po.ID
limit 10;


Result:


ID	_height	_length
35		
37	20	40
44	5	12
46	5	12
50	5	11
51	25	25
54	28	28
55	5	11
57	20	20
59	5	11


In the 10 results above we can see some products where there is neither _height nor _length attribute, but we still get the Product ID. There are multiple possible reasons:


    

  • This could be that the product is missing sizing which is one of the query’s I will be using this view for later
    • 

  • This could be a product that has variations and the variations hold the sizing
    • 

  • This could be a grouped product
    • 





ToDo: Find where product type is stored, e.g. Simple, grouped

Initial Answer: Buried in Taxomomy per https://stackoverflow.com/questions/41253518/sql-query-to-check-product-type-in-woocommerce


Create View: Flattern attributes into 1 record per product


Proof of concept looks good, so lets create a reusable view with all product attributes.  And while we are at it we will rename our attribute columns so they have no leading underscore


We need a max(case…) statement for each available attribute which we will construct using a SQL query:


Query:


select concat(',max(case when meta_key = ''', meta_key, ''' then meta_value else null end) as pm', meta_key)
from (
	select distinct meta_key
	from wp1_posts po
	join wp1_postmeta pm
	 on pm.post_id = po.ID
	where post_type = 'product'
	order by meta_key
    ) pm;


Result (I haven’t pasted in all 70 result rows for brevity):


,max(case when meta_key = '_area' then meta_value else null end) as pm_area
,max(case when meta_key = '_backorders' then meta_value else null end) as pm_backorders
,max(case when meta_key = '_crosssell_ids' then meta_value else null end) as pm_crosssell_ids
,max(case when meta_key = '_default_attributes' then meta_value else null end) as pm_default_attributes
,max(case when meta_key = '_downloadable' then meta_value else null end) as pm_downloadable
,max(case when meta_key = '_edit_last' then meta_value else null end) as pm_edit_last
,max(case when meta_key = '_edit_lock' then meta_value else null end) as pm_edit_lock
,max(case when meta_key = '_featured' then meta_value else null end) as pm_featured
...
,max(case when meta_key = '_wp_old_slug' then meta_value else null end) as pm_wp_old_slug
,max(case when meta_key = 'total_sales' then meta_value else null end) as pmtotal_sales


We now have enough to create the product attribute view


View create SQL:


create view vProductMetaData
as
	 /*
		Author: LSR 20170726
		Woocommerce products are stored as Posts
        Products have multiple attributes.
		- Each attribute has it's own record in table ..postmeta
		- so if a product has 5 attributes = at least 5 records in this table for that product
		- Custom attributes generated by custom product options/plugins
		  also generate additional records in this table
		This View flatterns those attributes 1 one record per product:
		- returns 1 record per line item select
		- each attribute is in its own column
        
        Assumes integrity in that per product an attribute as identified by meta_key exists
        only once per Product
	 */
select po.ID
	,max(case when meta_key = '_area' then meta_value else null end) as pm_area
	,max(case when meta_key = '_backorders' then meta_value else null end) as pm_backorders
	,max(case when meta_key = '_crosssell_ids' then meta_value else null end) as pm_crosssell_ids
	,max(case when meta_key = '_default_attributes' then meta_value else null end) as pm_default_attributes
	,max(case when meta_key = '_downloadable' then meta_value else null end) as pm_downloadable
	,max(case when meta_key = '_edit_last' then meta_value else null end) as pm_edit_last
	,max(case when meta_key = '_edit_lock' then meta_value else null end) as pm_edit_lock
	,max(case when meta_key = '_featured' then meta_value else null end) as pm_featured
	,max(case when meta_key = '_height' then meta_value else null end) as pm_height
	,max(case when meta_key = '_length' then meta_value else null end) as pm_length
	,max(case when meta_key = '_m4m_cost' then meta_value else null end) as pm_m4m_cost
	,max(case when meta_key = '_m4m_ok' then meta_value else null end) as pm_m4m_ok
	,max(case when meta_key = '_m4m_pnp_cost' then meta_value else null end) as pm_m4m_pnp_cost
	,max(case when meta_key = '_m4m_purchase_note' then meta_value else null end) as pm_m4m_purchase_note
	,max(case when meta_key = '_m4m_qty_bought' then meta_value else null end) as pm_m4m_qty_bought
	,max(case when meta_key = '_m4m_qty_inv' then meta_value else null end) as pm_m4m_qty_inv
	,max(case when meta_key = '_m4m_repl_cost' then meta_value else null end) as pm_m4m_repl_cost
	,max(case when meta_key = '_m4m_shrinkage' then meta_value else null end) as pm_m4m_shrinkage
	,max(case when meta_key = '_manage_stock' then meta_value else null end) as pm_manage_stock
	,max(case when meta_key = '_max_price_variation_id' then meta_value else null end) as pm_max_price_variation_id
	,max(case when meta_key = '_max_regular_price_variation_id' then meta_value else null end) as pm_max_regular_price_variation_id
	,max(case when meta_key = '_max_sale_price_variation_id' then meta_value else null end) as pm_max_sale_price_variation_id
	,max(case when meta_key = '_max_variation_price' then meta_value else null end) as pm_max_variation_price
	,max(case when meta_key = '_max_variation_regular_price' then meta_value else null end) as pm_max_variation_regular_price
	,max(case when meta_key = '_max_variation_sale_price' then meta_value else null end) as pm_max_variation_sale_price
	,max(case when meta_key = '_min_price_variation_id' then meta_value else null end) as pm_min_price_variation_id
	,max(case when meta_key = '_min_regular_price_variation_id' then meta_value else null end) as pm_min_regular_price_variation_id
	,max(case when meta_key = '_min_sale_price_variation_id' then meta_value else null end) as pm_min_sale_price_variation_id
	,max(case when meta_key = '_min_variation_price' then meta_value else null end) as pm_min_variation_price
	,max(case when meta_key = '_min_variation_regular_price' then meta_value else null end) as pm_min_variation_regular_price
	,max(case when meta_key = '_min_variation_sale_price' then meta_value else null end) as pm_min_variation_sale_price
	,max(case when meta_key = '_price' then meta_value else null end) as pm_price
	,max(case when meta_key = '_product_addons' then meta_value else null end) as pm_product_addons
	,max(case when meta_key = '_product_addons_exclude_global' then meta_value else null end) as pm_product_addons_exclude_global
	,max(case when meta_key = '_product_attributes' then meta_value else null end) as pm_product_attributes
	,max(case when meta_key = '_product_image_gallery' then meta_value else null end) as pm_product_image_gallery
	,max(case when meta_key = '_product_version' then meta_value else null end) as pm_product_version
	,max(case when meta_key = '_psad_featured_order' then meta_value else null end) as pm_psad_featured_order
	,max(case when meta_key = '_psad_onsale_order' then meta_value else null end) as pm_psad_onsale_order
	,max(case when meta_key = '_purchase_note' then meta_value else null end) as pm_purchase_note
	,max(case when meta_key = '_regular_price' then meta_value else null end) as pm_regular_price
	,max(case when meta_key = '_sale_price' then meta_value else null end) as pm_sale_price
	,max(case when meta_key = '_sale_price_dates_from' then meta_value else null end) as pm_sale_price_dates_from
	,max(case when meta_key = '_sale_price_dates_to' then meta_value else null end) as pm_sale_price_dates_to
	,max(case when meta_key = '_sku' then meta_value else null end) as pm_sku
	,max(case when meta_key = '_sold_individually' then meta_value else null end) as pm_sold_individually
	,max(case when meta_key = '_stock' then meta_value else null end) as pm_stock
	,max(case when meta_key = '_stock_status' then meta_value else null end) as pm_stock_status
	,max(case when meta_key = '_tax_class' then meta_value else null end) as pm_tax_class
	,max(case when meta_key = '_tax_status' then meta_value else null end) as pm_tax_status
	,max(case when meta_key = '_thumbnail_id' then meta_value else null end) as pm_thumbnail_id
	,max(case when meta_key = '_upsell_ids' then meta_value else null end) as pm_upsell_ids
	,max(case when meta_key = '_virtual' then meta_value else null end) as pm_virtual
	,max(case when meta_key = '_visibility' then meta_value else null end) as pm_visibility
	,max(case when meta_key = '_volume' then meta_value else null end) as pm_volume
	,max(case when meta_key = '_wc_average_rating' then meta_value else null end) as pm_wc_average_rating
	,max(case when meta_key = '_wc_measurement_price_calculator_min_price' then meta_value else null end) as pm_wc_measurement_price_calculator_min_price
	,max(case when meta_key = '_wc_price_calculator' then meta_value else null end) as pm_wc_price_calculator
	,max(case when meta_key = '_wc_price_calculator_pricing_rules' then meta_value else null end) as pm_wc_price_calculator_pricing_rules
	,max(case when meta_key = '_wc_rating_count' then meta_value else null end) as pm_wc_rating_count
	,max(case when meta_key = '_weight' then meta_value else null end) as pm_weight
	,max(case when meta_key = '_width' then meta_value else null end) as pm_width
	,max(case when meta_key = '_woocommerce_add_fees_product' then meta_value else null end) as pm_woocommerce_add_fees_product
	,max(case when meta_key = '_wp_old_slug' then meta_value else null end) as pm_wp_old_slug
	,max(case when meta_key = 'total_sales' then meta_value else null end) as pmtotal_sales
from wp1_posts po
left join wp1_postmeta pm
 on pm.post_id = po.ID
where post_type = 'product'
group by po.ID;


Future check for new attributes


The view created above snapshots the used attribute keys at this point in time.  As we have already stated this can change over time.  I am interested in creating a query now to compare the list of product attribute keys found today with those in the database in the future.  I will assume attribute keys can both appear and disappear.


Every time we run this query we will do a full outer join compare between the product attribute keys found in the live database vs those we snapshot today.


Initial query: get all today’s product attributes into a usable fixed list


Query:


select distinct concat('union select ''', meta_key, '''')
	from wp1_posts po
	join wp1_postmeta pm
	 on pm.post_id = po.ID
	where post_type in ('product', 'product_variation')
	order by meta_key;


Result:


union select '_area'
union select '_backorders'
union select '_crosssell_ids'
union select '_default_attributes'
union select '_download_expiry'
union select '_download_limit'
union select '_downloadable'
union select '_downloadable_files'
union select '_edit_last'
union select '_edit_lock'
union select '_featured'
union select '_height'
union select '_length'
union select '_m4m_cost'
union select '_m4m_ok'
union select '_m4m_pnp_cost'
union select '_m4m_purchase_note'
union select '_m4m_qty_bought'
union select '_m4m_qty_inv'
union select '_m4m_repl_cost'
union select '_m4m_shrinkage'
union select '_manage_stock'
union select '_max_price_variation_id'
union select '_max_regular_price_variation_id'
union select '_max_sale_price_variation_id'
union select '_max_variation_price'
union select '_max_variation_regular_price'
union select '_max_variation_sale_price'
union select '_min_price_variation_id'
union select '_min_regular_price_variation_id'
union select '_min_sale_price_variation_id'
union select '_min_variation_price'
union select '_min_variation_regular_price'
union select '_min_variation_sale_price'
union select '_price'
union select '_product_addons'
union select '_product_addons_exclude_global'
union select '_product_attributes'
union select '_product_image_gallery'
union select '_product_version'
union select '_psad_featured_order'
union select '_psad_onsale_order'
union select '_purchase_note'
union select '_regular_price'
union select '_sale_price'
union select '_sale_price_dates_from'
union select '_sale_price_dates_to'
union select '_sku'
union select '_sold_individually'
union select '_stock'
union select '_stock_status'
union select '_tax_class'
union select '_tax_status'
union select '_thumbnail_id'
union select '_upsell_ids'
union select '_variation_description'
union select '_virtual'
union select '_visibility'
union select '_volume'
union select '_wc_average_rating'
union select '_wc_measurement_price_calculator_min_price'
union select '_wc_price_calculator'
union select '_wc_price_calculator_pricing_rules'
union select '_wc_rating_count'
union select '_weight'
union select '_width'
union select '_woocommerce_add_fees_product'
union select '_wp_old_slug'
union select 'attribute_length'
union select 'total_sales'


Now we use the above result in our query


    

  • Note we edit the first select from the results list above to remove the first ‘union statement & add a result column name ‘snapshot_meta_key’
    • 



select * from (
	select distinct meta_key
	from wp1_posts po
	join wp1_postmeta pm
	 on pm.post_id = po.ID
	where post_type in ('product', 'product_variation')
	order by meta_key
    ) n
left outer join (
	select 
			'_area' as snapshot_meta_key
		union select '_backorders'
		union select '_crosssell_ids'
		union select '_default_attributes'
		union select '_download_expiry'
		union select '_download_limit'
		union select '_downloadable'
		union select '_downloadable_files'
		union select '_edit_last'
		union select '_edit_lock'
		union select '_featured'
		union select '_height'
		union select '_length'
		union select '_m4m_cost'
		union select '_m4m_ok'
		union select '_m4m_pnp_cost'
		union select '_m4m_purchase_note'
		union select '_m4m_qty_bought'
		union select '_m4m_qty_inv'
		union select '_m4m_repl_cost'
		union select '_m4m_shrinkage'
		union select '_manage_stock'
		union select '_max_price_variation_id'
		union select '_max_regular_price_variation_id'
		union select '_max_sale_price_variation_id'
		union select '_max_variation_price'
		union select '_max_variation_regular_price'
		union select '_max_variation_sale_price'
		union select '_min_price_variation_id'
		union select '_min_regular_price_variation_id'
		union select '_min_sale_price_variation_id'
		union select '_min_variation_price'
		union select '_min_variation_regular_price'
		union select '_min_variation_sale_price'
		union select '_price'
		union select '_product_addons'
		union select '_product_addons_exclude_global'
		union select '_product_attributes'
		union select '_product_image_gallery'
		union select '_product_version'
		union select '_psad_featured_order'
		union select '_psad_onsale_order'
		union select '_purchase_note'
		union select '_regular_price'
		union select '_sale_price'
		union select '_sale_price_dates_from'
		union select '_sale_price_dates_to'
		union select '_sku'
		union select '_sold_individually'
		union select '_stock'
		union select '_stock_status'
		union select '_tax_class'
		union select '_tax_status'
		union select '_thumbnail_id'
		union select '_upsell_ids'
		union select '_variation_description'
		union select '_virtual'
		union select '_visibility'
		union select '_volume'
		union select '_wc_average_rating'
		union select '_wc_measurement_price_calculator_min_price'
		union select '_wc_price_calculator'
		union select '_wc_price_calculator_pricing_rules'
		union select '_wc_rating_count'
		union select '_weight'
		union select '_width'
		union select '_woocommerce_add_fees_product'
		union select '_wp_old_slug'
		union select 'attribute_length'
		union select 'total_sales'
	) t
    on t.snapshot_meta_key = n.meta_key
where snapshot_meta_key IS NULL;


Thats it!


Note to prove the above query works now, whilst Live = our snapshot I simply comment out any of the union vaues in the query above and run it.  It will return the comment values in the result list


And now an an example query using this new view.  I want to know products that do have shipping dimensions populated


	
	
	






			
Updated: January 1, 2020 at 7:43 am