I am building a woocomerce export plugin using python by database migartion i have written.
`
"""
SELECT
-- Basic Product Information from wp_posts
p.post_title AS post_title,
p.post_name AS post_name,
p.post_parent AS post_parent,
p.ID AS ID,
p.post_content AS post_content,
p.post_excerpt AS post_excerpt,
p.post_status AS post_status,
p.post_password AS post_password,
p.menu_order AS menu_order,
p.post_date AS post_date,
p.post_author AS post_author,
p.comment_status AS comment_status,
-- Fetching Product Metadata from wp_postmeta
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_sku') AS sku,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_parent_sku') AS parent_sku,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_children') AS children,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_regular_price') AS regular_price,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_sale_price' ) AS sale_price,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_weight' ) AS weight,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_length' ) AS length,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_width' ) AS width,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_height' ) AS height,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_tax_class' ) AS tax_class,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_visibility' ) AS visibility,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_stock_status' ) AS stock_status,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_backorders' ) AS backorders,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_sold_individually' ) AS sold_individually,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_downloadable' ) AS downloadable,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_virtual' ) AS `virtual`,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_stock' ) AS stock,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_product_url' ) AS product_url,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_button_text' ) AS button_text,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_manage_stock' ) AS manage_stock,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_tax_status' ) AS tax_status,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_upsell_ids' ) AS upsell_ids,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_crosssell_ids' ) AS crosssell_ids,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_purchase_note' ) AS purchase_note,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_sale_price_dates_from' ) AS sale_price_dates_from,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_sale_price_dates_to' ) AS sale_price_dates_to,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_download_limit' ) AS download_limit,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_download_expiry' ) AS download_expiry,
(SELECT pm.meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND pm.meta_key = '_low_stock_amount' ) AS low_stock_amount,
-- Categories and Tags from wp_terms
GROUP_CONCAT(DISTINCT t_cat.name SEPARATOR ', ') AS categories,
GROUP_CONCAT(DISTINCT t_tag.name SEPARATOR ', ') AS tags,
-- Fetching Featured and Gallery Images with Metadata
GROUP_CONCAT(
DISTINCT CONCAT(
i.guid,
' ! alt : ', IFNULL(im_alt.meta_value, ''),
' ! title : ', IFNULL(im_title.meta_value, ''),
' ! desc : ', IFNULL(im_desc.meta_value, ''),
' ! caption : ', IFNULL(im_caption.meta_value, '')
) SEPARATOR ' | '
) AS images
FROM
wp_posts p
-- Join Taxonomies for Categories and Tags
LEFT JOIN
wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN
wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN
wp_terms t_cat ON tt.term_id = t_cat.term_id AND tt.taxonomy = 'product_cat'
LEFT JOIN
wp_terms t_tag ON tt.term_id = t_tag.term_id AND tt.taxonomy = 'product_tag'
-- Join for Product Images
LEFT JOIN
wp_postmeta pm ON p.ID = pm.post_id AND pm.meta_key = '_thumbnail_id' -- Featured Image
LEFT JOIN
wp_posts i ON i.ID = pm.meta_value AND i.post_type = 'attachment' -- Get image URL from the attachments table
-- Join for Image Metadata (alt, title, description, caption)
LEFT JOIN
wp_postmeta im_alt ON im_alt.post_id = i.ID AND im_alt.meta_key = '_wp_attachment_image_alt'
LEFT JOIN
wp_postmeta im_title ON im_title.post_id = i.ID AND im_title.meta_key = '_wp_attachment_image_title'
LEFT JOIN
wp_postmeta im_desc ON im_desc.post_id = i.ID AND im_desc.meta_key = '_wp_attachment_image_desc'
LEFT JOIN
wp_postmeta im_caption ON im_caption.post_id = i.ID AND im_caption.meta_key = '_wp_attachment_image_caption'
-- Fetch Gallery Images (where post_parent is the product's ID)
LEFT JOIN
wp_posts g ON g.post_parent = p.ID AND g.post_type = 'attachment' -- Gallery Images
LEFT JOIN
wp_postmeta gm_alt ON gm_alt.post_id = g.ID AND gm_alt.meta_key = '_wp_attachment_image_alt'
LEFT JOIN
wp_postmeta gm_title ON gm_title.post_id = g.ID AND gm_title.meta_key = '_wp_attachment_image_title'
LEFT JOIN
wp_postmeta gm_desc ON gm_desc.post_id = g.ID AND gm_desc.meta_key = '_wp_attachment_image_desc'
LEFT JOIN
wp_postmeta gm_caption ON gm_caption.post_id = g.ID AND gm_caption.meta_key = '_wp_attachment_image_caption'
WHERE
p.post_type = 'product'
AND p.post_status = 'publish'
GROUP BY
p.ID;
"""
i need to add the category details of the product to it how can i achive that
product Category details should be in tree like structure like “Electronics|Electronics > HeadPhone”
How can i read the catgory details from postgress database and create the product export struchure