Category: Woocommerce MySQL Views

Woocommerce DB checks

— DB Checks — select * from vProducts where 1 = 1 and product_type = ‘simple’ and pm_manage_stock != ‘no’ and (pm_stock_status != ‘instock’ and coalesce(pm_stock, 0) > 0); select count(*) as chk from wp1_posts where post_status not in (select post_status from m4m_refOrderStates) and post_type = ‘shop_order’; select id, pmd.post_title, pmd.pm_price, pm_regular_price from vProductMetaData pmd […]

Woocommerce -> MySQL -> vVariationMetaData vProductVariation

20181220 Product variations (attribute = length only currently) alter view vVariationMetaData as — 20181220 LSR — Product Variations meta data — Note is selected metadata only (not quite all) select po.post_parent, pm.post_id ,max(case when meta_key = ‘attribute_length’ then meta_value else null end) as pvm_attribute_length ,max(case when meta_key = ‘_sku’ then meta_value else null end) as […]

Woocommerce – Editing Orders

Paypal orders are sorta locked xx — wp1_postmeta update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping’; update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping_tax’; update wp1_postmeta set meta_value = ‘10.36’ where post_id = 3387 and meta_key = ‘_order_total’; — wp1_woocommerce_order_itemmeta update wp1_woocommerce_order_itemmeta oim […]

Woocommerce -> MySql -> vTaxonomyChained vTaxonomyPaths vProductCatsSummary

Product categories have parents… Products can be in more than 1 category xx create view vProductCatsSummary as select ID ,count(*) as cat_cnt ,min(tt.term_taxonomy_id) as cat_min ,max(tt.term_taxonomy_id) as cat_max ,( select group_concat( tex.name separator ‘ | ‘ ) from wp1_term_relationships trx JOIN wp1_term_taxonomy ttx ON trx.term_taxonomy_id = ttx.term_taxonomy_id JOIN wp1_terms tex ON ttx.term_id = tex.term_id where […]