Get stock by custom meta field on all Woocommerce variable products

Question

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.

Answer 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