MySQL query for wooCommerce to export products.

woocommerce-products.sql

SELECT product.ID as product_id, product.post_title as product_name, replace(product.post_content, '"', "'") as product_content, product_sku.meta_value as product_sku, product_price.meta_value as product_price, product_weight.meta_value as product_weight
FROM wp_posts as product
LEFT JOIN wp_postmeta as product_sku ON product.ID = product_sku.post_ID
LEFT JOIN wp_postmeta as product_price ON product.ID = product_price.post_ID
LEFT JOIN wp_postmeta as product_weight ON product.ID = product_weight.post_ID
WHERE (product.post_type = 'product' OR product.post_type = 'product_variation') AND product_sku.meta_key = '_sku' AND product_price.meta_key = '_price' AND product_weight.meta_key = '_weight'
ORDER BY product_id ASC

How can i get the product images?

How can i get the product images?

You can use

get_the_post_thumbnail($result->ID, 'medium', '')

I'm looking for something like this but also showing stock amount and category ?

Some left joins in your SQL statment causes slow down query. I rewrite your statement to get more better performance:
SELECT p.ID,
p.post_title 'Name',
p.post_content 'Description',
IF (meta.meta_key = '_sku', meta.meta_value, null) 'SKU',
IF (meta.meta_key = '_price', meta.meta_value, null) 'Price',
IF (meta.meta_key = '_weight', meta_value, null) 'Weight'
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS meta ON p.ID = meta.post_ID
WHERE (p.post_type = 'product' OR p.post_type = 'product_variation')
AND meta.meta_key IN ('_sku', '_price', '_weight')
GROUP BY p.ID

Sorry, this query was wrong. I made some modifications and including stock and categories:

SELECT p.ID,
p.post_title 'nome',
p.post_content 'descrição',
GROUP_CONCAT(cat.name SEPARATOR ' | ') 'Category',
MAX(CASE WHEN meta.meta_key = '_sku' THEN meta.meta_value END) 'SKU',
MAX(CASE WHEN meta.meta_key = '_price' THEN meta.meta_value END) 'Price',
MAX(CASE WHEN meta.meta_key = '_weight' THEN meta.meta_value END) 'Weight',
MAX(CASE WHEN meta.meta_key = '_stock' THEN meta.meta_value END) 'Stock'
FROM wp_posts AS p
JOIN wp_postmeta AS meta ON p.ID = meta.post_ID
LEFT JOIN
(
SELECT pp.id,
GROUP_CONCAT(t.name SEPARATOR ' > ') AS name
FROM wp_posts AS pp
JOIN wp_term_relationships tr ON pp.id = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
|| tt.parent = t.term_id
WHERE tt.taxonomy = 'product_cat'
GROUP BY pp.id, tt.term_id
) cat ON p.id = cat.id
WHERE (p.post_type = 'product' OR p.post_type = 'product_variation')
AND meta.meta_key IN ('_sku', '_price', '_weight', '_stock')
AND meta.meta_value is not null
GROUP BY p.ID

Cheers ;)

Is possible to obtain product_tag in one column like: tag1,tag2,tag3
So i can search in one columns.

Is it possible to show per variation the variation items?

How would I got about adding a specific product attribute to this query

How can I get the Product title, Product SKU and Product Price in a single sql query containing only one JOIN...?

@mglaman thanks

How would I got about adding a specific product attribute to this query

I would like to know this also.