Question 3226 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

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

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search