phrases – mysql to get associated tags of a tag and order by intersection charge?

What I attempt to do is create a wp plugin to get associated tags of a tag and order by intersection charge.

For instance, in a WordPress web site, information construction like beneath:

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

Now, I wish to get associated tags of tag A.

The components of intersection charge:

INTERSECT_RATE = INTERSECT_COUNT / ALL_POST_COUNT_IN_A_TAG

So,

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

The issue is I am not very aware of the advanced question of mysql. What I’ve completed to date:

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

    international $wpdb;

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

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

    $outcomes = $wpdb->get_results($wpdb->put together(
        "SELECT 
        associated.term_id, 
        COUNT(*) as rel_count
    FROM
        {$wpdb->prefix}phrases associated
        INNER JOIN {$wpdb->prefix}term_taxonomy related_tax ON (related_tax.term_id = associated.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}phrases 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 associated.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,
        $quantity
    ));

    // var_dump($outcomes);

    $phrases = array();
    foreach ($outcomes as $consequence) {
        $time period = get_term((int)$result->term_id, $related_taxonomy);
        $term->rel_count = (int)$result->rel_count;
        $phrases[] = $time period;
    }

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

    return $phrases;
}

The question outcomes for associated tags will not be unhealthy, however I doubt that possibly I did one thing flawed attributable to my unhealthy mysql ability degree, or there’s something ought to be improved for higher relevance or efficiency.

Any assist? Thanks first.