Woocommerce MySQL queries

Just a WIP dump

See private post for Host reminder

select po.ID, po.post_title, po.post_name
 ,max(case when meta_key = '_sku' then meta_value else null end) as psku
 ,max(case when meta_key = '_weight' then meta_value else null end) as pweight
 ,max(case when meta_key = '_length' then meta_value else null end) as plength
 ,max(case when meta_key = '_width' then meta_value else null end) as pwidth
 ,max(case when meta_key = '_height' then meta_value else null end) as pheight
-- ,count(po.meta_id) as check

from wp1_posts po
left outer join wp1_postmeta pm
 on pm.post_id = po.ID
where post_type = 'product'
group by po.ID, po.post_title, po.post_name
order by psku

Order line items for invoices

• Is all line items & all found line item meta values as at 20170417 with my custom attributes last (..as m4mxxxx)

select * from wp1_woocommerce_order_items oi
join (
 select order_item_id
 ,max(case when meta_key = '_qty' then meta_value end) as _qty
 ,max(case when meta_key = '_tax_class' then meta_value end) as _tax_class
 ,max(case when meta_key = '_product_id' then meta_value end) as _product_id
 ,max(case when meta_key = '_variation_id' then meta_value end) as _variation_id
 ,max(case when meta_key = '_line_subtotal' then meta_value end) as _line_subtotal
 ,max(case when meta_key = '_line_total' then meta_value end) as _line_total
 ,max(case when meta_key = '_line_subtotal_tax' then meta_value end) as _line_subtotal_tax
 ,max(case when meta_key = '_line_tax' then meta_value end) as _line_tax
 ,max(case when meta_key = '_line_tax_data' then meta_value end) as _line_tax_data
 ,max(case when meta_key = '_measurement_data' then meta_value end) as _measurement_data
 ,max(case when meta_key = 'method_id' then meta_value end) as method_id
 ,max(case when meta_key = 'cost' then meta_value end) as cost
 ,max(case when meta_key = 'taxes' then meta_value end) as taxes
 ,max(case when meta_key = 'rate_id' then meta_value end) as rate_id
 ,max(case when meta_key = 'label' then meta_value end) as label
 ,max(case when meta_key = 'compound' then meta_value end) as compound
 ,max(case when meta_key = 'tax_amount' then meta_value end) as tax_amount
 ,max(case when meta_key = 'shipping_tax_amount' then meta_value end) as shipping_tax_amount
 ,max(case when meta_key = 'Items' then meta_value end) as Items
 ,max(case when meta_key = 'length' then meta_value end) as length
 ,max(case when meta_key = 'Required Length (mm)' then meta_value end) as m4mRequiredLength
 ,max(case when meta_key = 'Cutting options' then meta_value end) as m4mCuttingOptions
 ,max(case when meta_key = 'End threading options M6' then meta_value end) as m4mEndThreadingOptions
 ,max(case when meta_key = 'Milling options' then meta_value end) as m4mMillingOptions
 from wp1_woocommerce_order_itemmeta oim
 group by order_item_id
 ) oim on oim.order_item_id = oi.order_item_id

where order_id = 907
order by oi.order_item_id;

xxx

20170417 Getting my order lines for paste into Excel invoice template

select sku
 ,coalesce(inv_title, order_item_name)
 ,null
 ,oim_length
 ,null
 ,oim__qty
 ,unitPrice
from (
 select *
 ,case
 when oim__qty is not null
 and oim__line_subtotal is not null
 then round(oim__line_subtotal/oim__qty,2)
 when oim_cost is not null
 then oim_cost
 when oim_tax_amount is not null
 then oim_tax_amount + coalesce(oim_shipping_tax_amount, 0)
 else null
 end as unitprice
 from wp1_woocommerce_order_items oi
 join (
 select order_item_id as oim_order_item_id
 -- most of these are not used in this query
 ,max(case when meta_key = '_qty' then meta_value end) as oim__qty
 ,max(case when meta_key = '_tax_class' then meta_value end) as oim__tax_class
 ,max(case when meta_key = '_product_id' then meta_value end) as oim__product_id
 ,max(case when meta_key = '_variation_id' then meta_value end) as oim__variation_id
 ,max(case when meta_key = '_line_subtotal' then meta_value end) as oim__line_subtotal
 ,max(case when meta_key = '_line_total' then meta_value end) as oim__line_total
 ,max(case when meta_key = '_line_subtotal_tax' then meta_value end) as oim__line_subtotal_tax
 ,max(case when meta_key = '_line_tax' then meta_value end) as oim__line_tax
 ,max(case when meta_key = '_line_tax_data' then meta_value end) as oim__line_tax_data
 ,max(case when meta_key = '_measurement_data' then meta_value end) as oim__measurement_data
 ,max(case when meta_key = 'method_id' then meta_value end) as oim_method_id
 ,max(case when meta_key = 'cost' then meta_value end) as oim_cost
 ,max(case when meta_key = 'taxes' then meta_value end) as oim_taxes
 ,max(case when meta_key = 'rate_id' then meta_value end) as oim_rate_id
 ,max(case when meta_key = 'label' then meta_value end) as oim_label
 ,max(case when meta_key = 'compound' then meta_value end) as oim_compound
 ,max(case when meta_key = 'tax_amount' then meta_value end) as oim_tax_amount
 ,max(case when meta_key = 'shipping_tax_amount' then meta_value end) as oim_shipping_tax_amount
 ,max(case when meta_key = 'Items' then meta_value end) as oim_Items
 -- m4m custom product variations / addons
 ,max(case when meta_key = 'length' then meta_value end) as oim_length
 ,max(case when meta_key = 'Required Length (mm)' then meta_value end) as oim_m4mRequiredLength
 ,max(case when meta_key = 'Cutting options' then meta_value end) as oim_m4mCuttingOptions
 ,max(case when meta_key = 'End threading options M6' then meta_value end) as oim_m4mEndThreadingOptions
 ,max(case when meta_key = 'Milling options' then meta_value end) as oim_m4mMillingOptions
 from wp1_woocommerce_order_itemmeta oim
 group by order_item_id
 ) oim on oim_order_item_id = oi.order_item_id
 -- Get inventory ID & inventory title
 left join (
 select po.ID as inv_id
 ,max(post_title) as inv_title
 ,max(case when meta_key = '_sku' then meta_value end) as sku
 from wp1_posts po
 join wp1_postmeta pm
 on pm.post_id = po.ID
 where pm.meta_key = '_sku'
 group by po.id
 ) inv on inv.inv_id = oim.oim__product_id 
 -- ************************ 
 where order_id = 907
 -- ************************ 
 order by oi.order_item_id
 ) oi

Actual output (but SKU & Product names edited)

20170725 Turning the above into Reusable Views

create view vOrderItemMetaData
as
 /*
    Author: LSR 20170725
    Woocommerce order line items have multiple (metadata) attributes.
    - Each attribute has it's own record in table ..order_itemmeta
    - so if a line item has 5 attributes = 5 records in this table for that line item
    - Custom attributes generated by custom produst options (I.e. product variations)
      also generate additional records in this table
    This View flatterns those attributes per order line item:
    - returns 1 record per line item select
    - each attribute is in its own column
    I am always a little uneasy about using max in this manner.  In the instance there is a duplicate
    attribute we won't know..
 */
 select order_item_id as oim_order_item_id
 -- most of these are not used in this query
 ,max(case when meta_key = '_qty' then meta_value end) as oim__qty
 ,max(case when meta_key = '_tax_class' then meta_value end) as oim__tax_class
 ,max(case when meta_key = '_product_id' then meta_value end) as oim__product_id
 ,max(case when meta_key = '_variation_id' then meta_value end) as oim__variation_id
 ,max(case when meta_key = '_line_subtotal' then meta_value end) as oim__line_subtotal
 ,max(case when meta_key = '_line_total' then meta_value end) as oim__line_total
 ,max(case when meta_key = '_line_subtotal_tax' then meta_value end) as oim__line_subtotal_tax
 ,max(case when meta_key = '_line_tax' then meta_value end) as oim__line_tax
 ,max(case when meta_key = '_line_tax_data' then meta_value end) as oim__line_tax_data
 ,max(case when meta_key = '_measurement_data' then meta_value end) as oim__measurement_data
 ,max(case when meta_key = 'method_id' then meta_value end) as oim_method_id
 ,max(case when meta_key = 'cost' then meta_value end) as oim_cost
 ,max(case when meta_key = 'taxes' then meta_value end) as oim_taxes
 ,max(case when meta_key = 'rate_id' then meta_value end) as oim_rate_id
 ,max(case when meta_key = 'label' then meta_value end) as oim_label
 ,max(case when meta_key = 'compound' then meta_value end) as oim_compound
 ,max(case when meta_key = 'tax_amount' then meta_value end) as oim_tax_amount
 ,max(case when meta_key = 'shipping_tax_amount' then meta_value end) as oim_shipping_tax_amount
 ,max(case when meta_key = 'Items' then meta_value end) as oim_Items
 -- m4m custom product variations / addons
 ,max(case when meta_key = 'length' then meta_value end) as oim_length
 ,max(case when meta_key = 'Required Length (mm)' then meta_value end) as oim_m4mRequiredLength
 ,max(case when meta_key = 'Cutting options' then meta_value end) as oim_m4mCuttingOptions
 ,max(case when meta_key = 'End threading options M6' then meta_value end) as oim_m4mEndThreadingOptions
 ,max(case when meta_key = 'Milling options' then meta_value end) as oim_m4mMillingOptions
 from wp1_woocommerce_order_itemmeta oim
 group by order_item_id;

Using above to get Invoice detail

xxx