What I try to do is create a wp plugin to get related tags of a tag and order by intersection rate.

For example, in a WordPress site, data structure like below:

• 40 posts has tags B,
• 200 posts has tags C,
• 20 posts has tags A, B,
• 40 posts has tags A, C

Now, I want to get related tags of tag A.

The formula of intersection rate:

INTERSECT_RATE = INTERSECT_COUNT / ALL_POST_COUNT_IN_A_TAG

So,

• Rate for B is 20 / 40 = 0.5, 50%
• Rate for C is 40 / 200 = 0.2, 20%

The problem is I’m not very familiar with the complex query of mysql. What I have done so far:

function get_related_terms($args = []) {
    $base_term_id = $args['term_id'];
    $base_taxonomy = $args['taxonomy'];
    $related_taxonomy = $args['rel_taxonomy'];
    $post_type = $args['post_type'];
    $number = $args['number'] ?? 20;

    global $wpdb;

    $key = $base_taxonomy . ':' . $related_taxonomy . ':' . $base_term_id;

    if ($terms = wp_cache_get($key, 'related_terms')) {
        return $terms;
    }

    $results = $wpdb->get_results($wpdb->prepare(
        "SELECT 
        related.term_id, 
        COUNT(*) as rel_count
    FROM
        {$wpdb->prefix}terms related
        INNER JOIN {$wpdb->prefix}term_taxonomy related_tax ON (related_tax.term_id = related.term_id)
        INNER JOIN {$wpdb->prefix}term_relationships related_rel ON (related_tax.term_taxonomy_id = related_rel.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}posts posts ON (related_rel.object_id = posts.ID)
        INNER JOIN {$wpdb->prefix}term_relationships base_rel ON (posts.ID = base_rel.object_id)
        INNER JOIN {$wpdb->prefix}term_taxonomy base_tax ON (base_rel.term_taxonomy_id = base_tax.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}terms base ON (base.term_id = base_tax.term_id)
    WHERE
        related_tax.taxonomy = '%s'
        AND base_tax.taxonomy = '%s'
        AND posts.post_type="%s"
        AND posts.post_status="publish"
        AND base.term_id = %d
        AND related.term_id != base.term_id
    GROUP BY related_tax.term_id
    ORDER BY rel_count/related_rel.object_id DESC
    LIMIT 0, %d",
        $related_taxonomy,
        $base_taxonomy,
        $post_type,
        $base_term_id,
        $number
    ));

    // var_dump($results);

    $terms = array();
    foreach ($results as $result) {
        $term = get_term((int)$result->term_id, $related_taxonomy);
        $term->rel_count = (int)$result->rel_count;
        $terms[] = $term;
    }

    wp_cache_set($key, $terms, 'related_terms');

    return $terms;
}

The query results for related tags are not bad, but I doubt that maybe I did something wrong due to my bad mysql skill level, or there is something should be improved for better relevance or performance.

Any help? Thanks first.