SQL query for WooCommerce to get Products, Variations, with Stock Levels and Category-CodePudding

I have an SQL query that properly pulls Product Variations and stock levels, but I don't have the Product Category. How can I add that to this query? I want to pull the single most specific category instead of the parent category, ideally.

SELECT p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value 
FROM wp_posts p
INNER JOIN wp_postmeta pm ON pm.post_id = p.ID
WHERE p.post_type IN ('product_variation')
AND p.post_status = 'publish'
AND pm.meta_key IN (
'_stock')
ORDER BY p.post_title

Thanks for any help you can offer!

CodePudding user response：

you could try to add some extra inner joins

INNER JOIN wp_term_relationships AS tr ON ('p.ID' = tr.object_id)
INNER JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN wp_terms AS t ON (t.term_id = tt.term_id)

because the terms/taxonmy uses a pivot(-ish) table to store the terms information of each post.

Is there a reason why you're not using the default WP_Query to get all the products?

CodePudding user response：

The final query I used was:

SELECT p.post_parent, p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value, MAX(t.slug)
FROM wp_posts p
INNER JOIN wp_postmeta pm ON pm.post_id = p.ID
INNER JOIN wp_term_relationships AS tr ON (p.post_parent = tr.object_id)
INNER JOIN wp_term_taxonomy AS tt ON (tt.taxonomy = 'product_cat' AND tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN wp_terms AS t ON (t.term_id = tt.term_id)
WHERE p.post_type IN ('product_variation')
AND p.post_status = 'publish'
AND pm.meta_key IN (
'_stock')
AND pm.meta_value IS NOT NULL
GROUP BY p.post_parent, p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value
ORDER BY p.post_title

The only downside is that it may join with the Parent Category instead of the Child category. Ideally it always joins the lowest level child Category so it's most specific.