20180802 Part 2
Having issues with running aggregates e.g. SUM(column) on $ values stored in oim, as column meta_key is of type longText, so there seems to be an implicit cast to float. So we are getting massive decimal places e.g. 21.4999999999999005 for a value of 21.50
I notice that as text $ values are predominantly stored to 4dp in oim.meta_value so we will return them to that in our revised view:
round(n,4) didn’t work, so we will cast all values we expect as $ as decimals in a revised view:
alter view vOrderItemMetaDataV3 /* 20180808 0809 LSR -- Changed non tax totals to cast as DECIMAL(10,2) from (12,4) 20180808 0644 LSR -- Added in oi_name & oi_type 20180802 1627 LSR Woocommerce orders lines (items) have multiple (metadata) attributes. 20180802 1627 amendments: - cast expected $ values as decimal - will leave as null for now when non existant, but might cause totalling issues later */ as select order_id, oi.order_item_id, oi.order_item_name, oi.order_item_type -- ,max(cast(case when order_item_type = 'line_item' and meta_key = '_line_subtotal' then meta_value else null end as DECIMAL(12,2))) as 'oim->line_item->_line_subtotal' ,max(cast(case when order_item_type = 'fee' and meta_key = '_line_subtotal_tax' then meta_value else null end as DECIMAL(12,4))) as 'oim->fee->_line_subtotal_tax' ,max(cast(case when order_item_type = 'line_item' and meta_key = '_line_subtotal_tax' then meta_value else null end as DECIMAL(12,4))) as 'oim->line_item->_line_subtotal_tax' ,max(cast(case when order_item_type = 'fee' and meta_key = '_line_tax' then meta_value else null end as DECIMAL(12,4))) as 'oim->fee->_line_tax' ,max(cast(case when order_item_type = 'line_item' and meta_key = '_line_tax' then meta_value else null end as DECIMAL(12,4))) as 'oim->line_item->_line_tax' ,max(cast(case when order_item_type = 'shipping' and meta_key = '_line_tax' then meta_value else null end as DECIMAL(12,4))) as 'oim->shipping->_line_tax' ,max(cast(case when order_item_type = 'fee' and meta_key = '_line_tax_data' then meta_value else null end as DECIMAL(12,4))) as 'oim->fee->_line_tax_data' ,max(cast(case when order_item_type = 'line_item' and meta_key = '_line_tax_data' then meta_value else null end as DECIMAL(12,4))) as 'oim->line_item->_line_tax_data' ,max(cast(case when order_item_type = 'shipping' and meta_key = '_line_tax_data' then meta_value else null end as DECIMAL(12,4))) as 'oim->shipping->_line_tax_data' ,max(cast(case when order_item_type = 'fee' and meta_key = '_line_total' then meta_value else null end as DECIMAL(12,4))) as 'oim->fee->_line_total' ,max(cast(case when order_item_type = 'line_item' and meta_key = '_line_total' then meta_value else null end as DECIMAL(12,2))) as 'oim->line_item->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_measurement_data' then meta_value else null end) as 'oim->line_item->_measurement_data' ,max(case when order_item_type = 'line_item' and meta_key = '_product_id' then meta_value else null end) as 'oim->line_item->_product_id' ,max(case when order_item_type = 'line_item' and meta_key = '_qty' then meta_value else null end) as 'oim->line_item->_qty' ,max(case when order_item_type = 'line_item' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->line_item->_refunded_item_id' ,max(case when order_item_type = 'shipping' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->shipping->_refunded_item_id' ,max(case when order_item_type = 'fee' and meta_key = '_tax_class' then meta_value else null end) as 'oim->fee->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_tax_class' then meta_value else null end) as 'oim->line_item->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_variation_id' then meta_value else null end) as 'oim->line_item->_variation_id' ,max(case when order_item_type = 'tax' and meta_key = 'compound' then meta_value else null end) as 'oim->tax->compound' ,max(cast(case when order_item_type = 'shipping' and meta_key = 'cost' then meta_value else null end as DECIMAL(12,2))) as 'oim->shipping->cost' ,max(case when order_item_type = 'shipping' and meta_key = 'Items' then meta_value else null end) as 'oim->shipping->Items' ,max(case when order_item_type = 'tax' and meta_key = 'label' then meta_value else null end) as 'oim->tax->label' ,max(case when order_item_type = 'line_item' and meta_key = 'length' then meta_value else null end) as 'oim->line_item->length' ,max(case when order_item_type = 'shipping' and meta_key = 'method_id' then meta_value else null end) as 'oim->shipping->method_id' ,max(case when order_item_type = 'tax' and meta_key = 'rate_id' then meta_value else null end) as 'oim->tax->rate_id' ,max(case when order_item_type = 'line_item' and meta_key = 'Required Length (mm)' then meta_value else null end) as 'oim->line_item->Required Length (mm)' ,max(cast(case when order_item_type = 'tax' and meta_key = 'shipping_tax_amount' then meta_value else null end as DECIMAL(12,4))) as 'oim->tax->shipping_tax_amount' ,max(cast(case when order_item_type = 'tax' and meta_key = 'tax_amount' then meta_value else null end as DECIMAL(12,4))) as 'oim->tax->tax_amount' ,max(cast(case when order_item_type = 'shipping' and meta_key = 'taxes' then meta_value else null end as DECIMAL(12,4))) as 'oim->shipping->taxes' -- Modified ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key like 'Mill & drill 11mm%' then meta_value else null end) as 'oim->line_item->Milling for M6 Cap Screw' ,max(case when order_item_type = 'line_item' and meta_key like 'Drill tool hole%' then meta_value else null end) as 'oim->line_item->Drill tool hole' -- from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id group by order_id, order_item_id;
20180802 Part 1
Starting to use view vOrderItemMetaDataV3. I am used to SQL Server where I can encapsulate reserved word named columns or columns with special chars with ‘[xxx]’ but now I find out mySQL uses ‘back ticks which I’m not so find of for readability. But.. the annoyance of that vs the value in my views derived columns showing the order item type & the meta Key outweigh the inconvenience
Example view column name: `oim->line_item->_product_id`
20180801
Revisiting this view for understanding as has been a while and also for completeness after product add-on edits
Step 1: Check for new (unhandled) item meta_keys
Ran the query from 20171218 Step 4 below and we have some new records:
oim->line_item->End threading options M6 ($3.50) | |
oim->line_item->Mill & drill 11mm land for M6 Hex Cap screw | |
oim->line_item->Drill tool hole | |
oim->line_item->Drill tool hole ($1.95) | |
oim->line_item->End threading options M6 ($1.80) | |
oim->line_item->End threading options M6 ($0.95) |
Our addon generics in the view from 20171218 Step 3 below are:
-- Modified ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key like 'Milling options%' then meta_value else null end) as 'oim->line_item->Milling options' --
oim->line_item->End threading options M6 ($3.50) = OK | |
oim->line_item->Mill & drill 11mm land for M6 Hex Cap screw = New | |
oim->line_item->Drill tool hole = New | |
oim->line_item->Drill tool hole ($1.95) = New | |
oim->line_item->End threading options M6 ($1.80) = OK | |
oim->line_item->End threading options M6 ($0.95) = Ok |
Remedial Actions for ‘Mill & drill…’
- for value ‘Mill & drill...’ this is an edit in the product addons for what was ‘Milling options’. We currently only have 2 different meta_key values used:
- 211 records for: ‘Milling options’
- 4 records for ”Milling options ($1.95)’
- we will amend the old values to bring them in line with the new by this SQL (I have run checks and are happy it is correct)
update wp1_woocommerce_order_itemmeta oim set meta_key = 'Mill & drill 11mm land for M6 Hex Cap screw' where meta_key = 'Milling options'; = 211 rows affected update wp1_woocommerce_order_itemmeta oim set meta_key = 'Mill & drill 11mm land for M6 Hex Cap screw ($1.95)' where meta_key = 'Milling options ($1.95)'; = 4 rows affected
View changes
Our addons custom handling changes from:
-- Modified ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key like 'Milling options%' then meta_value else null end) as 'oim->line_item->Milling options' --
to:
-- Modified ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key like 'Mill & drill 11mm%' then meta_value else null end) as 'oim->line_item->Milling for M6 Cap Screw' ,max(case when order_item_type = 'line_item' and meta_key like 'Drill tool hole%' then meta_value else null end) as 'oim->line_item->Drill tool hole' --
And we create our revised view:
create view vOrderItemMetaDataV3 /* 20180802 1039 LSR Woocommerce orders lines (items) have multiple (metadata) attributes. */ as select order_id, oi.order_item_id -- ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal' then meta_value else null end) as 'oim->line_item->_line_subtotal' ,max(case when order_item_type = 'fee' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->fee->_line_subtotal_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->line_item->_line_subtotal_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax' then meta_value else null end) as 'oim->fee->_line_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax' then meta_value else null end) as 'oim->line_item->_line_tax' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax' then meta_value else null end) as 'oim->shipping->_line_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->fee->_line_tax_data' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->line_item->_line_tax_data' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->shipping->_line_tax_data' ,max(case when order_item_type = 'fee' and meta_key = '_line_total' then meta_value else null end) as 'oim->fee->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_line_total' then meta_value else null end) as 'oim->line_item->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_measurement_data' then meta_value else null end) as 'oim->line_item->_measurement_data' ,max(case when order_item_type = 'line_item' and meta_key = '_product_id' then meta_value else null end) as 'oim->line_item->_product_id' ,max(case when order_item_type = 'line_item' and meta_key = '_qty' then meta_value else null end) as 'oim->line_item->_qty' ,max(case when order_item_type = 'line_item' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->line_item->_refunded_item_id' ,max(case when order_item_type = 'shipping' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->shipping->_refunded_item_id' ,max(case when order_item_type = 'fee' and meta_key = '_tax_class' then meta_value else null end) as 'oim->fee->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_tax_class' then meta_value else null end) as 'oim->line_item->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_variation_id' then meta_value else null end) as 'oim->line_item->_variation_id' ,max(case when order_item_type = 'tax' and meta_key = 'compound' then meta_value else null end) as 'oim->tax->compound' ,max(case when order_item_type = 'shipping' and meta_key = 'cost' then meta_value else null end) as 'oim->shipping->cost' ,max(case when order_item_type = 'shipping' and meta_key = 'Items' then meta_value else null end) as 'oim->shipping->Items' ,max(case when order_item_type = 'tax' and meta_key = 'label' then meta_value else null end) as 'oim->tax->label' ,max(case when order_item_type = 'line_item' and meta_key = 'length' then meta_value else null end) as 'oim->line_item->length' ,max(case when order_item_type = 'shipping' and meta_key = 'method_id' then meta_value else null end) as 'oim->shipping->method_id' ,max(case when order_item_type = 'tax' and meta_key = 'rate_id' then meta_value else null end) as 'oim->tax->rate_id' ,max(case when order_item_type = 'line_item' and meta_key = 'Required Length (mm)' then meta_value else null end) as 'oim->line_item->Required Length (mm)' ,max(case when order_item_type = 'tax' and meta_key = 'shipping_tax_amount' then meta_value else null end) as 'oim->tax->shipping_tax_amount' ,max(case when order_item_type = 'tax' and meta_key = 'tax_amount' then meta_value else null end) as 'oim->tax->tax_amount' ,max(case when order_item_type = 'shipping' and meta_key = 'taxes' then meta_value else null end) as 'oim->shipping->taxes' -- Modified ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key like 'Mill & drill 11mm%' then meta_value else null end) as 'oim->line_item->Milling for M6 Cap Screw' ,max(case when order_item_type = 'line_item' and meta_key like 'Drill tool hole%' then meta_value else null end) as 'oim->line_item->Drill tool hole' -- from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id group by order_id, order_item_id;
xxx
20171218
Looking to create a view that flattens all order item meta data into separate columns.
We have discovered a flaw in the original view and this revision will address that.
The approach is the same in principle as we took to create a fattened view for Product metadata
Step 1: What are the combinations of order_items.order_item_type and order_itemmeta.meta_key in our target Woocommerce implementation
Analysis:
We have discovered that there are multiple types of entries in order_items:
select distinct order_item_type from wp1_woocommerce_order_items;
Result:
line_item shipping tax fee
And we also have distinct meta_key in order_itemmeta
select distinct meta_key from wp1_woocommerce_order_itemmeta oim order by meta_key;
Result:
_line_subtotal _line_subtotal_tax _line_tax _line_tax_data _line_total _measurement_data _product_id _qty _refunded_item_id _tax_class _variation_id compound cost Cutting options Cutting options ($1.75) End threading options M6 End threading options M6 ($0.95) End threading options M6 ($1.75) End threading options M6 ($1.80) End threading options M6 ($3.50) Items label length method_id Milling options Milling options ($1.95) rate_id Required Length (mm) shipping_tax_amount tax_amount taxes
Analysis result:
Our use case for the order_itemmeta flattened view expects only 1 row per order item, but the combination of the above 2 querys may result in more than 1, so we are now looking to flatten order_itemmeta per unique combinations of order_items.order_item_type and order_itemmeta.meta_key
select distinct order_item_type, meta_key from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id order by meta_key;
Result:
line_item | _line_subtotal |
line_item | _line_subtotal_tax |
fee | _line_subtotal_tax |
shipping | _line_tax |
line_item | _line_tax |
fee | _line_tax |
shipping | _line_tax_data |
line_item | _line_tax_data |
fee | _line_tax_data |
line_item | _line_total |
fee | _line_total |
line_item | _measurement_data |
line_item | _product_id |
line_item | _qty |
line_item | _refunded_item_id |
shipping | _refunded_item_id |
line_item | _tax_class |
fee | _tax_class |
line_item | _variation_id |
tax | compound |
shipping | cost |
line_item | Cutting options |
line_item | Cutting options ($1.75) |
line_item | End threading options M6 |
line_item | End threading options M6 ($0.95) |
line_item | End threading options M6 ($1.75) |
line_item | End threading options M6 ($1.80) |
line_item | End threading options M6 ($3.50) |
shipping | Items |
tax | label |
line_item | length |
shipping | method_id |
line_item | Milling options |
line_item | Milling options ($1.95) |
tax | rate_id |
line_item | Required Length (mm) |
tax | shipping_tax_amount |
tax | tax_amount |
shipping | taxes |
Points to revisit later:
- _refunded_item_id: is this where discounts or edited item line prices are hidden?
- Product addons addin is enabled, e.g. ‘ End threading options M6’ so we will revisit these later to cleanup and make more usable
Step 2: Generate SQL snippets to turn above combinations in unique columns:
select concat(',max(case when order_item_type = ''', order_item_type, ''' and meta_key = ''', meta_key, ''' then meta_value else null end) as ''oim->', order_item_type, '->', meta_key, '''') as flattening from ( select distinct order_item_type, meta_key from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id order by meta_key, order_item_type ) oim;
Result:
,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal' then meta_value else null end) as 'oim->line_item->_line_subtotal' ,max(case when order_item_type = 'fee' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->fee->_line_subtotal_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->line_item->_line_subtotal_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax' then meta_value else null end) as 'oim->fee->_line_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax' then meta_value else null end) as 'oim->line_item->_line_tax' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax' then meta_value else null end) as 'oim->shipping->_line_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->fee->_line_tax_data' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->line_item->_line_tax_data' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->shipping->_line_tax_data' ,max(case when order_item_type = 'fee' and meta_key = '_line_total' then meta_value else null end) as 'oim->fee->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_line_total' then meta_value else null end) as 'oim->line_item->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_measurement_data' then meta_value else null end) as 'oim->line_item->_measurement_data' ,max(case when order_item_type = 'line_item' and meta_key = '_product_id' then meta_value else null end) as 'oim->line_item->_product_id' ,max(case when order_item_type = 'line_item' and meta_key = '_qty' then meta_value else null end) as 'oim->line_item->_qty' ,max(case when order_item_type = 'line_item' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->line_item->_refunded_item_id' ,max(case when order_item_type = 'shipping' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->shipping->_refunded_item_id' ,max(case when order_item_type = 'fee' and meta_key = '_tax_class' then meta_value else null end) as 'oim->fee->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_tax_class' then meta_value else null end) as 'oim->line_item->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_variation_id' then meta_value else null end) as 'oim->line_item->_variation_id' ,max(case when order_item_type = 'tax' and meta_key = 'compound' then meta_value else null end) as 'oim->tax->compound' ,max(case when order_item_type = 'shipping' and meta_key = 'cost' then meta_value else null end) as 'oim->shipping->cost' ,max(case when order_item_type = 'line_item' and meta_key = 'Cutting options' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key = 'Cutting options ($1.75)' then meta_value else null end) as 'oim->line_item->Cutting options ($1.75)' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($0.95)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($0.95)' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($1.75)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($1.75)' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($1.80)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($1.80)' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($3.50)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($3.50)' ,max(case when order_item_type = 'shipping' and meta_key = 'Items' then meta_value else null end) as 'oim->shipping->Items' ,max(case when order_item_type = 'tax' and meta_key = 'label' then meta_value else null end) as 'oim->tax->label' ,max(case when order_item_type = 'line_item' and meta_key = 'length' then meta_value else null end) as 'oim->line_item->length' ,max(case when order_item_type = 'shipping' and meta_key = 'method_id' then meta_value else null end) as 'oim->shipping->method_id' ,max(case when order_item_type = 'line_item' and meta_key = 'Milling options' then meta_value else null end) as 'oim->line_item->Milling options' ,max(case when order_item_type = 'line_item' and meta_key = 'Milling options ($1.95)' then meta_value else null end) as 'oim->line_item->Milling options ($1.95)' ,max(case when order_item_type = 'tax' and meta_key = 'rate_id' then meta_value else null end) as 'oim->tax->rate_id' ,max(case when order_item_type = 'line_item' and meta_key = 'Required Length (mm)' then meta_value else null end) as 'oim->line_item->Required Length (mm)' ,max(case when order_item_type = 'tax' and meta_key = 'shipping_tax_amount' then meta_value else null end) as 'oim->tax->shipping_tax_amount' ,max(case when order_item_type = 'tax' and meta_key = 'tax_amount' then meta_value else null end) as 'oim->tax->tax_amount' ,max(case when order_item_type = 'shipping' and meta_key = 'taxes' then meta_value else null end) as 'oim->shipping->taxes'
Step 3: Create View
Product Addons special handling
The product addons we will deal with now as an initial pass, and see later if is acceptable
- We know there is only 1 product addon option able to be selected per addon in our setup
- This pass I will assume the additional $ data included in the meta key can be discarded (will revisit this decision later if a bad assumption)
So working with ‘End threading options M6’ as an example we will take these sql statements from the above generated set:
,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($0.95)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($0.95)' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($1.75)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($1.75)' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($1.80)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($1.80)' ,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 ($3.50)' then meta_value else null end) as 'oim->line_item->End threading options M6 ($3.50)'
And will substitute with these in the view we create below:
,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6'
We do this for the (currently) 3 sets of options ordered by customers:
- ‘Cutting options’
- ‘End threading options M6’
- ‘Milling options’
We’ll also move these statements to the bottom of the selected columns so they stand out
Test the select statement we will use in our view:
We start with this:
select oi.order_item_id -- -- Insert snippets from Step 2 here -- from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id group by order_item_id;
Creating this:
select oi.order_item_id -- ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal' then meta_value else null end) as 'oim->line_item->_line_subtotal' ,max(case when order_item_type = 'fee' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->fee->_line_subtotal_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->line_item->_line_subtotal_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax' then meta_value else null end) as 'oim->fee->_line_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax' then meta_value else null end) as 'oim->line_item->_line_tax' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax' then meta_value else null end) as 'oim->shipping->_line_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->fee->_line_tax_data' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->line_item->_line_tax_data' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->shipping->_line_tax_data' ,max(case when order_item_type = 'fee' and meta_key = '_line_total' then meta_value else null end) as 'oim->fee->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_line_total' then meta_value else null end) as 'oim->line_item->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_measurement_data' then meta_value else null end) as 'oim->line_item->_measurement_data' ,max(case when order_item_type = 'line_item' and meta_key = '_product_id' then meta_value else null end) as 'oim->line_item->_product_id' ,max(case when order_item_type = 'line_item' and meta_key = '_qty' then meta_value else null end) as 'oim->line_item->_qty' ,max(case when order_item_type = 'line_item' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->line_item->_refunded_item_id' ,max(case when order_item_type = 'shipping' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->shipping->_refunded_item_id' ,max(case when order_item_type = 'fee' and meta_key = '_tax_class' then meta_value else null end) as 'oim->fee->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_tax_class' then meta_value else null end) as 'oim->line_item->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_variation_id' then meta_value else null end) as 'oim->line_item->_variation_id' ,max(case when order_item_type = 'tax' and meta_key = 'compound' then meta_value else null end) as 'oim->tax->compound' ,max(case when order_item_type = 'shipping' and meta_key = 'cost' then meta_value else null end) as 'oim->shipping->cost' ,max(case when order_item_type = 'shipping' and meta_key = 'Items' then meta_value else null end) as 'oim->shipping->Items' ,max(case when order_item_type = 'tax' and meta_key = 'label' then meta_value else null end) as 'oim->tax->label' ,max(case when order_item_type = 'line_item' and meta_key = 'length' then meta_value else null end) as 'oim->line_item->length' ,max(case when order_item_type = 'shipping' and meta_key = 'method_id' then meta_value else null end) as 'oim->shipping->method_id' ,max(case when order_item_type = 'tax' and meta_key = 'rate_id' then meta_value else null end) as 'oim->tax->rate_id' ,max(case when order_item_type = 'line_item' and meta_key = 'Required Length (mm)' then meta_value else null end) as 'oim->line_item->Required Length (mm)' ,max(case when order_item_type = 'tax' and meta_key = 'shipping_tax_amount' then meta_value else null end) as 'oim->tax->shipping_tax_amount' ,max(case when order_item_type = 'tax' and meta_key = 'tax_amount' then meta_value else null end) as 'oim->tax->tax_amount' ,max(case when order_item_type = 'shipping' and meta_key = 'taxes' then meta_value else null end) as 'oim->shipping->taxes' -- Modified ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key like 'Milling options%' then meta_value else null end) as 'oim->line_item->Milling options' -- from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id group by order_item_id;
Yes it works.
Create the view
create view vOrderItemMetaDataV2 /* Author: LSR 20171218 Woocommerce orders lines (items) have multiple (metadata) attributes. */ as select oi.order_item_id -- ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal' then meta_value else null end) as 'oim->line_item->_line_subtotal' ,max(case when order_item_type = 'fee' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->fee->_line_subtotal_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_subtotal_tax' then meta_value else null end) as 'oim->line_item->_line_subtotal_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax' then meta_value else null end) as 'oim->fee->_line_tax' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax' then meta_value else null end) as 'oim->line_item->_line_tax' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax' then meta_value else null end) as 'oim->shipping->_line_tax' ,max(case when order_item_type = 'fee' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->fee->_line_tax_data' ,max(case when order_item_type = 'line_item' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->line_item->_line_tax_data' ,max(case when order_item_type = 'shipping' and meta_key = '_line_tax_data' then meta_value else null end) as 'oim->shipping->_line_tax_data' ,max(case when order_item_type = 'fee' and meta_key = '_line_total' then meta_value else null end) as 'oim->fee->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_line_total' then meta_value else null end) as 'oim->line_item->_line_total' ,max(case when order_item_type = 'line_item' and meta_key = '_measurement_data' then meta_value else null end) as 'oim->line_item->_measurement_data' ,max(case when order_item_type = 'line_item' and meta_key = '_product_id' then meta_value else null end) as 'oim->line_item->_product_id' ,max(case when order_item_type = 'line_item' and meta_key = '_qty' then meta_value else null end) as 'oim->line_item->_qty' ,max(case when order_item_type = 'line_item' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->line_item->_refunded_item_id' ,max(case when order_item_type = 'shipping' and meta_key = '_refunded_item_id' then meta_value else null end) as 'oim->shipping->_refunded_item_id' ,max(case when order_item_type = 'fee' and meta_key = '_tax_class' then meta_value else null end) as 'oim->fee->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_tax_class' then meta_value else null end) as 'oim->line_item->_tax_class' ,max(case when order_item_type = 'line_item' and meta_key = '_variation_id' then meta_value else null end) as 'oim->line_item->_variation_id' ,max(case when order_item_type = 'tax' and meta_key = 'compound' then meta_value else null end) as 'oim->tax->compound' ,max(case when order_item_type = 'shipping' and meta_key = 'cost' then meta_value else null end) as 'oim->shipping->cost' ,max(case when order_item_type = 'shipping' and meta_key = 'Items' then meta_value else null end) as 'oim->shipping->Items' ,max(case when order_item_type = 'tax' and meta_key = 'label' then meta_value else null end) as 'oim->tax->label' ,max(case when order_item_type = 'line_item' and meta_key = 'length' then meta_value else null end) as 'oim->line_item->length' ,max(case when order_item_type = 'shipping' and meta_key = 'method_id' then meta_value else null end) as 'oim->shipping->method_id' ,max(case when order_item_type = 'tax' and meta_key = 'rate_id' then meta_value else null end) as 'oim->tax->rate_id' ,max(case when order_item_type = 'line_item' and meta_key = 'Required Length (mm)' then meta_value else null end) as 'oim->line_item->Required Length (mm)' ,max(case when order_item_type = 'tax' and meta_key = 'shipping_tax_amount' then meta_value else null end) as 'oim->tax->shipping_tax_amount' ,max(case when order_item_type = 'tax' and meta_key = 'tax_amount' then meta_value else null end) as 'oim->tax->tax_amount' ,max(case when order_item_type = 'shipping' and meta_key = 'taxes' then meta_value else null end) as 'oim->shipping->taxes' -- Modified ,max(case when order_item_type = 'line_item' and meta_key like 'Cutting options%' then meta_value else null end) as 'oim->line_item->Cutting options' ,max(case when order_item_type = 'line_item' and meta_key like 'End threading options M6%' then meta_value else null end) as 'oim->line_item->End threading options M6' ,max(case when order_item_type = 'line_item' and meta_key like 'Milling options%' then meta_value else null end) as 'oim->line_item->Milling options' -- from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id group by order_item_id;
Done.
Product Addons: Check what meta values we get
select * from vOrderItemMetaDataV2 where `oim->line_item->Cutting options` is not null;
Result = fine for now
Step 4: Create our query to check for new metakeys popping in the future that are not in our view
Snapshot found combinations as union statements:
select distinct concat('union select ''oim->', order_item_type, '->', meta_key, '''') from ( select distinct order_item_type, meta_key from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id order by meta_key, order_item_type ) oim;
Result:
union select 'oim->line_item->_line_subtotal' union select 'oim->fee->_line_subtotal_tax' union select 'oim->line_item->_line_subtotal_tax' union select 'oim->fee->_line_tax' union select 'oim->line_item->_line_tax' union select 'oim->shipping->_line_tax' union select 'oim->fee->_line_tax_data' union select 'oim->line_item->_line_tax_data' union select 'oim->shipping->_line_tax_data' union select 'oim->fee->_line_total' union select 'oim->line_item->_line_total' union select 'oim->line_item->_measurement_data' union select 'oim->line_item->_product_id' union select 'oim->line_item->_qty' union select 'oim->line_item->_refunded_item_id' union select 'oim->shipping->_refunded_item_id' union select 'oim->fee->_tax_class' union select 'oim->line_item->_tax_class' union select 'oim->line_item->_variation_id' union select 'oim->tax->compound' union select 'oim->shipping->cost' union select 'oim->line_item->Cutting options' union select 'oim->line_item->Cutting options ($1.75)' union select 'oim->line_item->End threading options M6' union select 'oim->line_item->End threading options M6 ($0.95)' union select 'oim->line_item->End threading options M6 ($1.75)' union select 'oim->line_item->End threading options M6 ($1.80)' union select 'oim->line_item->End threading options M6 ($3.50)' union select 'oim->shipping->Items' union select 'oim->tax->label' union select 'oim->line_item->length' union select 'oim->shipping->method_id' union select 'oim->line_item->Milling options' union select 'oim->line_item->Milling options ($1.95)' union select 'oim->tax->rate_id' union select 'oim->line_item->Required Length (mm)' union select 'oim->tax->shipping_tax_amount' union select 'oim->tax->tax_amount' union select 'oim->shipping->taxes'
To use our union statements:
Starting with our template:
select * from ( select distinct concat('oim->', order_item_type, '->', meta_key) as meta_key from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id ) n left outer join ( select 'xyxyxyx' as snapshot_meta_key -- insert below previous snapshotted union statements -- ) t on t.snapshot_meta_key = n.meta_key where snapshot_meta_key IS NULL;
We create and run:
select * from ( select distinct concat('oim->', order_item_type, '->', meta_key) as meta_key from wp1_woocommerce_order_items oi join wp1_woocommerce_order_itemmeta oim on oim.order_item_id = oi.order_item_id ) n left outer join ( select 'xyxyxyx' as snapshot_meta_key -- insert below previous snapshotted union statements union select 'oim->line_item->_line_subtotal' union select 'oim->fee->_line_subtotal_tax' union select 'oim->line_item->_line_subtotal_tax' union select 'oim->fee->_line_tax' union select 'oim->line_item->_line_tax' union select 'oim->shipping->_line_tax' union select 'oim->fee->_line_tax_data' union select 'oim->line_item->_line_tax_data' union select 'oim->shipping->_line_tax_data' union select 'oim->fee->_line_total' union select 'oim->line_item->_line_total' union select 'oim->line_item->_measurement_data' union select 'oim->line_item->_product_id' union select 'oim->line_item->_qty' union select 'oim->line_item->_refunded_item_id' union select 'oim->shipping->_refunded_item_id' union select 'oim->fee->_tax_class' union select 'oim->line_item->_tax_class' union select 'oim->line_item->_variation_id' union select 'oim->tax->compound' union select 'oim->shipping->cost' union select 'oim->line_item->Cutting options' union select 'oim->line_item->Cutting options ($1.75)' union select 'oim->line_item->End threading options M6' union select 'oim->line_item->End threading options M6 ($0.95)' union select 'oim->line_item->End threading options M6 ($1.75)' union select 'oim->line_item->End threading options M6 ($1.80)' union select 'oim->line_item->End threading options M6 ($3.50)' union select 'oim->shipping->Items' union select 'oim->tax->label' union select 'oim->line_item->length' union select 'oim->shipping->method_id' union select 'oim->line_item->Milling options' union select 'oim->line_item->Milling options ($1.95)' union select 'oim->tax->rate_id' union select 'oim->line_item->Required Length (mm)' union select 'oim->tax->shipping_tax_amount' union select 'oim->tax->tax_amount' union select 'oim->shipping->taxes' -- ) t on t.snapshot_meta_key = n.meta_key where snapshot_meta_key IS NULL;
Result = 0 rows = success
BUT if we run this query in the future and get results then the view needs to be amended