Display each woocommerce variations stock quantity in wp-admin column

Question

All my woocommerce products are variable products. Each of them have the same variation 'male' and 'female'.

I'm trying to add a column for each of them to my products overview in wp-admin both showing the stock quantity for that variation.

I've included following code which displays the sum of 'male' and 'female'. Is there a way of only querying one of them?

function add_qty_admin( $column ) {
    if (!isset($columns['total_qty']))
    $columns['total_qty'] = "Totaal in voorraad";
    return $columns;
}
add_filter( 'manage_posts_columns', 'add_qty_admin' );

function admin_post_data_row($column_name, $post_id)
{
global $wpdb;
switch($column_name)
{
    case 'total_qty':
        $query = "SELECT sum(meta_value)
                  FROM $wpdb->posts AS p, $wpdb->postmeta AS s
                  WHERE p.post_parent = %d
                  AND p.post_type = 'product_variation'
                  AND p.post_status = 'publish'
                  AND p.id = s.post_id
                  AND s.meta_key = '_stock'";

        $product_qty = $wpdb->get_var($wpdb->prepare($query,$post_id));
        if ($product_qty) echo $product_qty;
        break;

    default:
        break;
}
}
add_action( 'manage_posts_custom_column', 'admin_post_data_row', 10, 2);

Your question is not clear as "Male" and "female" can't be variations, but instead are product attributes (or terms of a product attribute). So 2 possible cases: Case 1) Each one of them is a product attribute for variation… Case 2) Or both are terms of one product attribute for variation (in this case which one? You need to give the product attribute taxonomy involved for "Male" and "Female")… So clarify your question as nobody can help actually. — LoicTheAztec

LoicTheAztec LoicTheAztec · Accepted Answer · 2019-06-03T16:14:06

Supposing that you product attribute is "Gender" (so the taxonomy is "pa_gender") and has 2 terms "Male" and "Female", the following code will add a custom column in admin product list with the total quantity for variations with "Male" term and the total quantity for variations with "Female".

// Add a custom column to admin product list
add_filter( 'manage_edit-product_columns', 'product_variations_total_quantity_column', 10, 1 );
function product_variations_total_quantity_column( $columns ) {
    $columns['gender_qty'] = __("Stock totals", "woocommerce");

    return $columns;
}

// Display the data for this cutom column on admin product list
add_action( 'manage_product_posts_custom_column', 'product_variations_total_quantity_values', 10, 2 );
function product_variations_total_quantity_values( $column, $post_id ) {
    if( $column === 'gender_qty' ) {
        // Define the product attribute taxonomy (always start with "pa_")
        $taxonomy = 'pa_gender';

        echo '<table><tr>
            <td>M: '   . get_gender_qty( $post_id, 'male', $taxonomy )   . '</td>
            <td>F: ' . get_gender_qty( $post_id, 'female', $taxonomy ) . '</td>
        </tr></table>';
    }
}

// Get the total quantity of variations with a specific product attribute term slug
function get_gender_qty( $post_id, $term_slug, $taxonomy ) {
    global $wpdb;

    return (int) $wpdb->get_var($wpdb->prepare("
        SELECT sum(pm.meta_value)
        FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}postmeta pm ON p.id = pm.post_id
        INNER JOIN {$wpdb->prefix}postmeta pm2 ON p.id = pm2.post_id
        WHERE p.post_type = 'product_variation'
        AND p.post_status = 'publish'
        AND p.post_parent = %d
        AND pm.meta_key = '_stock'
        AND pm2.meta_key = '%s'
        AND pm2.meta_value = '%s'
    ", $post_id, 'attribute_'.$taxonomy, $term_slug ) );
}

Code goes in functions.php file of your active child theme (or active theme). Tested and works.

Display each woocommerce variations stock quantity in wp-admin column

1 Answers