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!
2
Answers
The final query I used was:
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.
you could try to add some extra inner joins
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?