Per https://stackoverflow.com/questions/36965352/woocommerce-finding-the-products-in-database Update 2020 Products are located mainly in the following tables: wp_posts table with post_type like product (or product_variation), wp_postmeta table with post_id as relational index (the product ID). wp_wc_product_meta_lookup table with product_id as relational index (the post ID) | Allow fast queries on specific product data (since WooCommerce 3.7) wp_wc_order_product_lookuptable with product_id as relational index (the post ID) | Allow fast queries to retrieve products on orders […]
Category: WooCommerce
Have some basic custom inventory pages outside WordPress for managing stock / cost of goods on a basic Woocomerce installation Need to be able to select products by categories / parent categories Products are in [posts] Categories are in [term_taxonomy] where [taxonomy] = ‘product_cat’ self linked table for parent / child categories [parent] = [term_taxonomy_id] […]
Bolt sizes: select reverse(substring_index(reverse(pm_sku), ‘.’, 1)) from vProductMetaData where (pm_sku like ‘BO.%’ or pm_sku like ‘SCW.%’);
Musings on how to calculate close to actual cost of sales in Woocommerce 2.6.14 using FIFO (If I manage to achieve it per order line then I can aggregate up for reporting totals etc, so initially focus on per order item) I have a custom inwards goods table, for initial thoughts I will ignore credits/returns, […]
SELECT table_schema as Database, table_name AS Table, round(((data_length + index_length) / 1024 / 1024), 2) Size in MB FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;
Note: No pricing changes here: select * from wp1_woocommerce_order_items where order_id = 7934; select * from wp1_woocommerce_order_itemmeta where order_item_id = 32168; update wp1_woocommerce_order_itemmetax set meta_value = ‘3373’ where meta_id = 237358 and meta_key = ‘_product_id’; update wp1_woocommerce_order_itemsx set order_item_name = ‘E3D Style Heat Break Throat M7-M6 / 22mm / 1.75mm / All Metal’ where order_item_id […]
select * from vOrderItemMetaDataV3 where order_id = 7509; insert into wp1_woocommerce_order_itemmeta (order_item_id, meta_key, meta_value) values (29897, ‘length’, ‘1000’) ; select * from wp1_woocommerce_order_itemmeta where order_item_id = 29897;
— 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 […]
update wp1_postmeta pm join ( select id, pmd.post_title, pmd.pm_price, pm_regular_price from vProductMetaData pmd join vProducts vp on vp.wp_post_id = pmd.id where coalesce(pmd.pm_price,0 ) <> coalesce (pmd.pm_regular_price,0) and product_type = ‘simple’ ) t on t.id = pm.post_id set pm.meta_value = pm_price where pm.meta_key = ‘_regular_price’;
20200512 Warning: This page is just a StreamOfConsciousness(tm) Issue: managing 1000 sku’s is hard work vProduct sales shows totals for %complete% orders we want to be able to set stock levels whilst picking orders we do not differentiate in woocommerce states orders that are picked vs unpicked.. BUT when picking, and stock checking, I want […]