-
-
Save mglaman/8406244 to your computer and use it in GitHub Desktop.
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 |
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.IDCheers ;)
thanks for the code but how about query products with their images attached? is it possible?
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.IDSorry, 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.IDCheers ;)
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.
Pretty late to party, but wanted to know!
Is there any way we can figure out the product's post date (Created date) and the stock quantity associated?
Basically I am thinking of building a separate dashboard from where I can control the stock quantity of products that are older than X days.