Count paying subscribers correctly from benecaster_tokens

Free Intermediate Since v1.0.0

The tokens table mixes three populations: paying subscribers, free-tier bridge members, and direct followers. Counting paying subscribers accurately requires excluding both non-paying populations simultaneously — one filter targets followers by token type, a second targets free-tier bridge members by their tier mapping. Applying only one of the two filters silently miscounts. Inside the plugin, use the built-in repository method; the SQL pattern in this recipe is intended for external scripts that query the database directly.

Code

<?php
global $wpdb;

$tokens   = $wpdb->prefix . 'benecaster_tokens';
$tier_map = $wpdb->prefix . 'benecaster_tier_map';

// Count paying subscribers for a single show.
$count = (int) $wpdb->get_var( $wpdb->prepare(
    "SELECT COUNT(*) FROM {$tokens} t
     INNER JOIN {$tier_map} tm
         ON tm.show_id = t.show_id AND tm.internal_tier_slug = t.tier_slug
     WHERE t.show_id = %d AND t.status = 'active'
       AND t.token_type = 'subscriber' AND tm.is_free_tier = 0",
    $show_id
) );

// Same pattern, site-wide — drives the daily license-server subscriber_count.
$site_count = (int) $wpdb->get_var(
    "SELECT COUNT(*) FROM {$tokens} t
     INNER JOIN {$tier_map} tm
         ON tm.show_id = t.show_id AND tm.internal_tier_slug = t.tier_slug
     WHERE t.status = 'active'
       AND t.token_type = 'subscriber' AND tm.is_free_tier = 0"
);

View on GitHub →