Cacher is the code snippet organizer for pro developers

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',
cats.cat as 'category:1',
cats.sub_cat as 'category:2',
if( isnull(vt_attr.name), pt_attr.name, vt_attr.name ) as 'pack'

from `boxcar_wp_aws`.`wp_4_posts` p 

# map the variations, if available
left join `boxcar_wp_aws`.`wp_4_posts` v on p.ID = v.post_parent and v.post_type = 'product_variation'

# only retrieving at public products
inner join `boxcar_wp_aws`.`wp_4_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 `boxcar_wp_aws`.`wp_4_postmeta` pm_sku on pm_sku.post_id = p.ID and pm_sku.meta_key = '_sku'
left join `boxcar_wp_aws`.`wp_4_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 `boxcar_wp_aws`.`wp_4_postmeta` pm_price on pm_price.post_id = p.ID and pm_price.meta_key = '_price'
left join `boxcar_wp_aws`.`wp_4_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 `boxcar_wp_aws`.`wp_4_postmeta` vm_attr on vm_attr.post_id = v.ID 
  and vm_attr.meta_key in ( 'attribute_pa_cards', 'attribute_pa_studio' , 'attribute_pa_gift')
left join `boxcar_wp_aws`.`wp_4_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 `boxcar_wp_aws`.`wp_4_posts` p
inner join `boxcar_wp_aws`.`wp_4_term_relationships` r on r.object_id = p.ID
inner join `boxcar_wp_aws`.`wp_4_term_taxonomy` tt on tt.term_taxonomy_id = r.term_taxonomy_id 
	and tt.taxonomy in ( 'pa_cards', 'pa_studio', 'pa_gift' )
inner join `boxcar_wp_aws`.`wp_4_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 `boxcar_wp_aws`.`wp_4_posts` p
inner join `boxcar_wp_aws`.`wp_4_term_relationships` r on r.object_id = p.ID
inner join `boxcar_wp_aws`.`wp_4_term_taxonomy` tt on tt.term_taxonomy_id = r.term_taxonomy_id
inner join `boxcar_wp_aws`.`wp_4_terms` t_cat on tt.parent = t_cat.term_id 
	and t_cat.name in ( 'Cards', 'Gift', 'Studio' )
inner join `boxcar_wp_aws`.`wp_4_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 `category:1`, `category:2`, `sku`

limit 0,10000
;