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