-
-
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 |
I'm also looking for Sql that includes categories.
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
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 ;)
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.
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.
I'm looking for something like this but also showing stock amount and category ?