Get active subscriptions count for a defined product and user in Woocommerce

Question

I'm able to know whether a user has an active subscription for a given product:

$subscribed = WC_Subscriptions_Manager::user_has_subscription($userId, $productId, $status);

But I'm not managing to figure out how many subscriptions to this product that user has… (Woocommerce Subscriptions plugin now supports a user buying multiple subscriptions).

Anyone know how to do this?

Answer 1

This custom function will give you the count of active subscriptions in a very light SQL query, for a defined used ID and a specific subscription product ID:

function get_user_active_subscriptions_count( $product_id, $user_id = null ) {
    global $wpdb;

    // if the user_id is not set in function argument we get the current user ID
    if( null == $user_id )
        $user_id = get_current_user_id();

    // return the active subscriptions for a define user and a defined product ID
    return $wpdb->get_var("
        SELECT COUNT(p.ID)
        FROM {$wpdb->prefix}posts as p
        LEFT JOIN {$wpdb->prefix}posts AS p2 ON p.post_parent = p2.ID
        LEFT JOIN {$wpdb->prefix}postmeta AS pm ON p2.ID = pm.post_id
        LEFT JOIN {$wpdb->prefix}woocommerce_order_items AS woi ON pm.post_id = woi.order_id
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woim ON woi.order_item_id = woim.order_item_id
        WHERE p.post_type LIKE 'shop_subscription' AND p.post_status LIKE 'wc-active'
        AND p2.post_type LIKE 'shop_order' AND woi.order_item_type LIKE 'line_item'
        AND pm.meta_key LIKE '_customer_user' AND pm.meta_value = '$user_id'
        AND woim.meta_key = '_product_id'
        AND woim.meta_value = '$product_id'
    ");
}

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

---

Usage for a defined product ID 9 and:

1. A user ID as a dynamic variable $user_id :

   echo '

   Subscription count: ' . get_user_active_subscriptions_count( '9', $user_id ) . '

   ';

2. A defined user ID (Here user ID is 15):

   echo '

   Subscription count: ' . get_user_active_subscriptions_count( '9', '15' ) . '

   ';

3. The current user ID:

   echo '

   Subscription count: ' . get_user_active_subscriptions_count( '9' ) . '

   ';

The product ID can also be dynamic using a variable instead of an integer (for the product ID)

---

Update: To get the total count of a product subscription in active subscriptions for a defined used ID (in a very light SQL query):

function get_user_active_product_subscriptions_count( $product_id, $user_id = null ) {
    global $wpdb;

    // if the user_id is not set in function argument we get the current user ID
    if( null == $user_id )
        $user_id = get_current_user_id();

    // return the active subscriptions for a define user and a defined product ID
    return $wpdb->get_var("
        SELECT sum(woim2.meta_value)
        FROM {$wpdb->prefix}posts as p
        LEFT JOIN {$wpdb->prefix}posts AS p2 ON p.post_parent = p2.ID
        LEFT JOIN {$wpdb->prefix}postmeta AS pm ON p2.ID = pm.post_id
        LEFT JOIN {$wpdb->prefix}woocommerce_order_items AS woi ON pm.post_id = woi.order_id
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woim ON woi.order_item_id = woim.order_item_id
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woim2 ON woim.order_item_id = woim2.order_item_id
        WHERE p.post_type LIKE 'shop_subscription' AND p.post_status LIKE 'wc-active'
        AND p2.post_type LIKE 'shop_order' AND woi.order_item_type LIKE 'line_item'
        AND pm.meta_key LIKE '_customer_user' AND pm.meta_value = '$user_id'
        AND woim.meta_key = '_product_id' AND woim.meta_value = '$product_id'
        AND woim2.meta_key = '_qty'
    ");
}

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

The usage is the same than above, but the result will be different as you will get the sum of the defined product subscription in active subscriptions (for a defined user ID (So it will sum the quantities of all items corresponding to the defined product ID in the subscriptions made by a user ID)…

Answer 2

Meanwhile, I think that this works. I have no idea how efficient it is, but the answer seems to be right – given a particular user and particular product, this will return the quantity of subscriptions the user has for this product. (Make sure to provide the Wordpress user_id in the $user_id variable, and the Woocommerce product id in the $product_id variable.)

$allSubscriptions = WC_Subscriptions_Manager::get_users_subscriptions($user_id);
$item_quantity = 0;
foreach ($allSubscriptions as $subscription){
    if (($subscription['status'] == 'active' || $subscription['status'] == 'pending-cancel') == false) continue;
    $order = wc_get_order($subscription['order_id']);
    // Iterating through each line-item in the order
    foreach ($order->get_items() as $item_id => $item_data) {
        if ($item_data->get_product()->get_id() != $product_id) continue;
        $item_quantity += $item_data->get_quantity();
    }
}
echo 'Quantity: ' . $item_quantity . '<br>';