1

In WooCommerce, I have a custom meta field "Brand" on my products, and each product is assigned with one of the Brands.
Now I want to get total stock for each brand from all products and I have only variable products, so I think I may have to query through Post type product_variation.

I don't want to run a WordPress meta query for this as I have a lot Brands and I do not want to run too many loops.
As I do not have SQL skills, I am not able to implement this correctly and I will really appreciate your help.

1

The following function will give you an array with the calculated stock quantity from all product variations for each custom meta field "Brand" value, using WPDB Class (a SQL query):

global $wpdb;

$results = $wpdb->get_results( "
    SELECT pm.meta_value as brand, SUM(pm2.meta_value) as stock
    FROM {$wpdb->prefix}postmeta pm
    INNER JOIN {$wpdb->prefix}posts p
        ON pm.post_id = p.post_parent
    INNER JOIN {$wpdb->prefix}postmeta as pm2
        ON p.ID = pm2.post_id
    WHERE p.post_status = 'publish'
    AND p.post_type = 'product_variation'
    AND p.post_parent > 0
    AND pm.meta_key = 'Brand'
    AND pm2.meta_key = '_stock'
    AND pm2.meta_value != ''
    AND pm2.meta_value != 'NULL'
    GROUP BY pm.meta_value
");

// Display (example)
echo '<ul class="stock-by-brand">';

foreach ($results as $result ) {
    echo '<li><strong>' . $result->brand . '</strong>: ' . $result->stock . '</li>';
}

echo '<ul>';

Tested and works

  • Perfect solution. Thank you so much! – wplearner May 7 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.