sql:woocommerce-product

sql:woocommerce-product

select distinct
 
# uncomment these rows for debugging
# p.ID,
# v.ID,
 
if( isnull(vm_sku.meta_value), pm_sku.meta_value, vm_sku.meta_value ) as 'sku',
p.post_title,
 
# need to cast prices to decimal because the datatype is not enforced otherwise
if( isnull(vm_price.meta_value), cast( pm_price.meta_value as DECIMAL(6,2) ), cast( vm_price.meta_value as DECIMAL(6,2) ) ) as 'unitPrice',
if( isnull(vt_attr.name), pt_attr.name, vt_attr.name ) as 'pack'
 
from `wp_posts` p 
 
# map the variations, if available
left join `wp_posts` v on p.ID = v.post_parent and v.post_type = 'product_variation'
 
# only retrieving at public products
inner join `wp_postmeta` pm_vis on pm_vis.post_id = p.ID and pm_vis.meta_key = '_visibility'
 
# sku of the variation else the product
left join `wp_postmeta` pm_sku on pm_sku.post_id = p.ID and pm_sku.meta_key = '_sku'
left join `wp_postmeta` vm_sku on vm_sku.post_id = v.ID and vm_sku.meta_key = '_sku'
 
# pricing of the variation else the product
left join `wp_postmeta` pm_price on pm_price.post_id = p.ID and pm_price.meta_key = '_price'
left join `wp_postmeta` vm_price on vm_price.post_id = v.ID and vm_price.meta_key = '_price'
 
# attribute terms of variable products are mapped via custom posts...not sure why...
left join `wp_postmeta` vm_attr on vm_attr.post_id = v.ID 

left join `wp_terms` vt_attr on vt_attr.slug = vm_attr.meta_value 
 
# attributes of single products are mapped via taxonomy
left join 
(
select p.ID, t.name 
from `wp_posts` p
inner join `wp_term_relationships` r on r.object_id = p.ID
inner join `wp_term_taxonomy` tt on tt.term_taxonomy_id = r.term_taxonomy_id 

inner join `wp_terms` t on tt.term_id = t.term_id 
) as pt_attr on pt_attr.ID = p.ID
 
# return the taxonomy terms assuming there is a child product category with a Card, Studio, or Gift parent
left join
(
select p.ID, t_cat.name as 'cat', t_sub_cat.name as 'sub_cat' 
from `wp_posts` p
inner join `wp_term_relationships` r on r.object_id = p.ID
inner join `wp_term_taxonomy` tt on tt.term_taxonomy_id = r.term_taxonomy_id
inner join `wp_terms` t_cat on tt.parent = t_cat.term_id 

inner join `wp_terms` t_sub_cat on t_sub_cat.term_id = tt.term_id
) as cats on cats.ID = p.ID
 
where p.post_type = 'product'
 
# only visible products
and pm_vis.meta_value = 'visible'
 
#only published products
and p.post_status = 'publish'
 
order by `sku`
 
limit 0,10000