Export Jigoshop Products

jigoshop-export.sql

SELECT
	p.post_title AS title,
	p.post_content AS description,
	p.post_excerpt AS excerpt,
	p.ID AS jigoshop_id,
	IFNULL(pmp.meta_value, "") AS price,
	IFNULL(pmp.meta_value, "") AS original_price,
	IFNULL(pmw.meta_value, "") AS weight,
	IFNULL(pms.meta_value, "") AS inventory,
	IFNULL(pmsk.meta_value, "") AS sku,
	IFNULL(seot.meta_value, "") AS seo_title,
	IFNULL(seod.meta_value, "") AS seo_description,
	IFNULL(seof.meta_value, "") AS seo_keyword,
	IFNULL(ab.meta_value, "") AS ab_excerpt,
	CONCAT('http://website.com/wp-content/uploads/', imi.meta_value) AS featured_image,
	(SELECT GROUP_CONCAT(t.name SEPARATOR ', ') 
		FROM wp_terms t
		LEFT JOIN wp_term_taxonomy tt on t.term_id = tt.term_id
		LEFT JOIN wp_term_relationships tr on tr.term_taxonomy_id = tt.term_taxonomy_id
		WHERE tt.taxonomy = 'product_cat' and p.ID = tr.object_id
	) AS "categories"
FROM wp_posts p
LEFT JOIN wp_postmeta pmp ON p.ID = pmp.post_id AND pmp.meta_key = 'regular_price'
LEFT JOIN wp_postmeta pmw ON p.ID = pmw.post_id AND pmw.meta_key = 'weight'
LEFT JOIN wp_postmeta pms ON p.ID = pms.post_id AND pms.meta_key = 'stock'
LEFT JOIN wp_postmeta pmsk ON p.ID = pmsk.post_id AND pmsk.meta_key = 'sku'
LEFT JOIN wp_postmeta seot ON p.ID = seot.post_id AND seot.meta_key = '_yoast_wpseo_title'
LEFT JOIN wp_postmeta seod ON p.ID = seod.post_id AND seod.meta_key = '_yoast_wpseo_metadesc'
LEFT JOIN wp_postmeta seof ON p.ID = seof.post_id AND seof.meta_key = '_yoast_wpseo_focuskw'
LEFT JOIN wp_postmeta ab ON p.ID = ab.post_id AND ab.meta_key = 'ab_excerpt'

LEFT JOIN wp_postmeta imm ON p.id = imm.post_id AND imm.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta imi ON imm.meta_value = imi.post_id AND imi.meta_key = "_wp_attached_file"

WHERE post_type = 'product'
AND post_status = 'publish'