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 (&#36;1.95)'
where meta_key = 'Milling options (&#36;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 (&#36;1.75)
End threading options M6
End threading options M6 (&#36;0.95)
End threading options M6 (&#36;1.75)
End threading options M6 (&#36;1.80)
End threading options M6 (&#36;3.50)
Items
label
length
method_id
Milling options
Milling options (&#36;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 (&#36;1.75)' then meta_value else null end) as 'oim->line_item->Cutting options (&#36;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 (&#36;0.95)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;0.95)'
,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 (&#36;1.75)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;1.75)'
,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 (&#36;1.80)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;1.80)'
,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 (&#36;3.50)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;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 (&#36;1.95)' then meta_value else null end) as 'oim->line_item->Milling options (&#36;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 (&#36;0.95)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;0.95)'
,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 (&#36;1.75)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;1.75)'
,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 (&#36;1.80)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;1.80)'
,max(case when order_item_type = 'line_item' and meta_key = 'End threading options M6 (&#36;3.50)' then meta_value else null end) as 'oim->line_item->End threading options M6 (&#36;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 (&#36;1.75)'
union select 'oim->line_item->End threading options M6'
union select 'oim->line_item->End threading options M6 (&#36;0.95)'
union select 'oim->line_item->End threading options M6 (&#36;1.75)'
union select 'oim->line_item->End threading options M6 (&#36;1.80)'
union select 'oim->line_item->End threading options M6 (&#36;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 (&#36;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 (&#36;1.75)'
 union select 'oim->line_item->End threading options M6'
 union select 'oim->line_item->End threading options M6 (&#36;0.95)'
 union select 'oim->line_item->End threading options M6 (&#36;1.75)'
 union select 'oim->line_item->End threading options M6 (&#36;1.80)'
 union select 'oim->line_item->End threading options M6 (&#36;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 (&#36;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

SmugTrafficker

Woocommerce -> MySQL -> vOrderItemMetaData

20180802 Part 2

20180802 Part 1

20180801

Step 1: Check for new (unhandled) item meta_keys

Remedial Actions for ‘Mill & drill…’

View changes

20171218

Step 1: What are the combinations of order_items.order_item_type and order_itemmeta.meta_key in our target Woocommerce implementation

Analysis:

Analysis result:

Points to revisit later:

Step 2: Generate SQL snippets to turn above combinations in unique columns:

Step 3: Create View

Product Addons special handling

Test the select statement we will use in our view:

Create the view

Product Addons: Check what meta values we get

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: