xx
alter view vProductMetaData as /* Author: LSR 20180402 Woocommerce products are stored as Posts Products have multiple attributes. - Each attribute has it's own record in table ..postmeta - so if a product has 5 attributes = at least 5 records in this table for that product - Custom attributes generated by custom product options/plugins also generate additional records in this table This View flatterns those attributes 1 one record per product: - returns 1 record per post_type = 'product' - each attribute is in its own column Assumes integrity in that an attribute as identified by meta_key exists only once per parent Product */ select po.ID, po.post_title ,max(case when meta_key = '_area' then meta_value else null end) as pm_area ,max(case when meta_key = '_backorders' then meta_value else null end) as pm_backorders ,max(case when meta_key = '_crosssell_ids' then meta_value else null end) as pm_crosssell_ids ,max(case when meta_key = '_default_attributes' then meta_value else null end) as pm_default_attributes ,max(case when meta_key = '_downloadable' then meta_value else null end) as pm_downloadable ,max(case when meta_key = '_edit_last' then meta_value else null end) as pm_edit_last ,max(case when meta_key = '_edit_lock' then meta_value else null end) as pm_edit_lock ,max(case when meta_key = '_featured' then meta_value else null end) as pm_featured ,max(case when meta_key = '_height' then meta_value else null end) as pm_height ,max(case when meta_key = '_length' then meta_value else null end) as pm_length ,max(case when meta_key = '_m4m_cost' then meta_value else null end) as pm_m4m_cost ,max(case when meta_key = '_m4m_ok' then meta_value else null end) as pm_m4m_ok ,max(case when meta_key = '_m4m_pnp_cost' then meta_value else null end) as pm_m4m_pnp_cost ,max(case when meta_key = '_m4m_purchase_note' then meta_value else null end) as pm_m4m_purchase_note ,max(case when meta_key = '_m4m_qty_bought' then meta_value else null end) as pm_m4m_qty_bought ,max(case when meta_key = '_m4m_qty_inv' then meta_value else null end) as pm_m4m_qty_inv ,max(case when meta_key = '_m4m_repl_cost' then meta_value else null end) as pm_m4m_repl_cost ,max(case when meta_key = '_m4m_shrinkage' then meta_value else null end) as pm_m4m_shrinkage ,max(case when meta_key = '_m4m_stocktake20180331' then meta_value else null end) as pm_m4m_stocktake20180331 ,max(case when meta_key = '_manage_stock' then meta_value else null end) as pm_manage_stock ,max(case when meta_key = '_max_price_variation_id' then meta_value else null end) as pm_max_price_variation_id ,max(case when meta_key = '_max_regular_price_variation_id' then meta_value else null end) as pm_max_regular_price_variation_id ,max(case when meta_key = '_max_sale_price_variation_id' then meta_value else null end) as pm_max_sale_price_variation_id ,max(case when meta_key = '_max_variation_price' then meta_value else null end) as pm_max_variation_price ,max(case when meta_key = '_max_variation_regular_price' then meta_value else null end) as pm_max_variation_regular_price ,max(case when meta_key = '_max_variation_sale_price' then meta_value else null end) as pm_max_variation_sale_price ,max(case when meta_key = '_min_price_variation_id' then meta_value else null end) as pm_min_price_variation_id ,max(case when meta_key = '_min_regular_price_variation_id' then meta_value else null end) as pm_min_regular_price_variation_id ,max(case when meta_key = '_min_sale_price_variation_id' then meta_value else null end) as pm_min_sale_price_variation_id ,max(case when meta_key = '_min_variation_price' then meta_value else null end) as pm_min_variation_price ,max(case when meta_key = '_min_variation_regular_price' then meta_value else null end) as pm_min_variation_regular_price ,max(case when meta_key = '_min_variation_sale_price' then meta_value else null end) as pm_min_variation_sale_price ,max(case when meta_key = '_price' then meta_value else null end) as pm_price ,max(case when meta_key = '_product_addons' then meta_value else null end) as pm_product_addons ,max(case when meta_key = '_product_addons_exclude_global' then meta_value else null end) as pm_product_addons_exclude_global ,max(case when meta_key = '_product_attributes' then meta_value else null end) as pm_product_attributes ,max(case when meta_key = '_product_image_gallery' then meta_value else null end) as pm_product_image_gallery ,max(case when meta_key = '_product_version' then meta_value else null end) as pm_product_version ,max(case when meta_key = '_psad_featured_order' then meta_value else null end) as pm_psad_featured_order ,max(case when meta_key = '_psad_onsale_order' then meta_value else null end) as pm_psad_onsale_order ,max(case when meta_key = '_purchase_note' then meta_value else null end) as pm_purchase_note ,max(case when meta_key = '_regular_price' then meta_value else null end) as pm_regular_price ,max(case when meta_key = '_sale_price' then meta_value else null end) as pm_sale_price ,max(case when meta_key = '_sale_price_dates_from' then meta_value else null end) as pm_sale_price_dates_from ,max(case when meta_key = '_sale_price_dates_to' then meta_value else null end) as pm_sale_price_dates_to ,max(case when meta_key = '_sku' then meta_value else null end) as pm_sku ,max(case when meta_key = '_sold_individually' then meta_value else null end) as pm_sold_individually ,max(case when meta_key = '_stock' then meta_value else null end) as pm_stock ,max(case when meta_key = '_stock_status' then meta_value else null end) as pm_stock_status ,max(case when meta_key = '_tax_class' then meta_value else null end) as pm_tax_class ,max(case when meta_key = '_tax_status' then meta_value else null end) as pm_tax_status ,max(case when meta_key = '_thumbnail_id' then meta_value else null end) as pm_thumbnail_id ,max(case when meta_key = '_upsell_ids' then meta_value else null end) as pm_upsell_ids ,max(case when meta_key = '_virtual' then meta_value else null end) as pm_virtual ,max(case when meta_key = '_visibility' then meta_value else null end) as pm_visibility ,max(case when meta_key = '_volume' then meta_value else null end) as pm_volume ,max(case when meta_key = '_wc_average_rating' then meta_value else null end) as pm_wc_average_rating ,max(case when meta_key = '_wc_measurement_price_calculator_min_price' then meta_value else null end) as pm_wc_measurement_price_calculator_min_price ,max(case when meta_key = '_wc_price_calculator' then meta_value else null end) as pm_wc_price_calculator ,max(case when meta_key = '_wc_price_calculator_pricing_rules' then meta_value else null end) as pm_wc_price_calculator_pricing_rules ,max(case when meta_key = '_wc_rating_count' then meta_value else null end) as pm_wc_rating_count ,max(case when meta_key = '_weight' then meta_value else null end) as pm_weight ,max(case when meta_key = '_width' then meta_value else null end) as pm_width ,max(case when meta_key = '_woocommerce_add_fees_product' then meta_value else null end) as pm_woocommerce_add_fees_product ,max(case when meta_key = '_wp_old_slug' then meta_value else null end) as pm_wp_old_slug ,max(case when meta_key = 'total_sales' then meta_value else null end) as pmtotal_sales from wp1_posts po left join wp1_postmeta pm on pm.post_id = po.ID where post_type = 'product' group by po.ID
xx