WP Plugin: How to avoid db access for each comment?

Question

I'm writing a WP plugin which enables voting for comments.

To store the votes a custom db table wp_comment_vote is being used.

CREATE TABLE wp_comment_vote (
    comment_vote_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    comment_id bigint(20) UNSIGNED NOT NULL,
    vote_up tinyint(1) NOT NULL DEFAULT '0', -- '1' means up vote, '0' means down vote
    PRIMARY KEY (comment_vote_id)
)

To retrieve the total votes for each comment (up votes minus down votes) I modify the comment_reply_link filter.

function comment_vote_comment_reply_link($link)
{
    $vote_link = comment_vote_show_votes();
    return $link . $vote_link;
}
add_filter('comment_reply_link', 'comment_vote_comment_reply_link');

function comment_vote_show_votes()
{
    $current_CommentID =  get_comment_ID();
    $sum_votes = get_sum_votes($current_CommentID);
    return '<div class="comment-vote-votes">' . $sum_votes . '</div>';
}

function get_sum_votes($comment_id)
{
    global $wpdb;
    $table = $wpdb->prefix . 'comment_vote';
    $sql_query = <<<EOD
SELECT 
        (SELECT COUNT(*) FROM $table WHERE comment_id='$comment_id' AND vote_up='1') - 
        (SELECT COUNT(*) FROM $table WHERE comment_id='$comment_id' AND vote_up='0')
    AS sum_votes
EOD;
    $results = $wpdb->get_var($sql_query);
    return $results;
}

As you see the db is accessed for each comment which results in 40 db calls when there are 40 comments.

My Questions are:

1. Is this a potential performance issue to access the db on each comment?
2. If yes, is there a way to avoid this (e.g. by getting all votes grouped by comment_id) outside the comment loop but still use the comment_reply_link filter?

Answer 1

Yes, I would suspect that your current solution may cause performance issues, if the number of comments gets high.

To make the calculations a little easier for your site, you could handle them before hand based on the post ID. I.e

1. get the post ID
2. query all of the post's comments' IDs (WP_Comment_Query or custom $wpdb query)
3. get the votes data from the custom table based on the comment IDs, i.e. WHERE IN query
4. calculate the votes and store the result in a transient, e.g. name "comment_vote_counts_{$post_id}" with array value [$comment_id => $vote_count, ...], with suitable expiration time

Then either modify your comments loop so that you can feed into it the transient value after reading it once or push the transient value into the WP object cache with wp_cache_set() and read the vote counts with wp_cache_get() in your callbacks.

Instead of using the WP object cache, you could also "cache" the data with a custom object and use it to provide the votes data via a filter. Something along these lines for example,

// provider class
class MyPostCommentsVotesCounts
{
    protected array $votes;

    public function __construct(array $votes)
    {
        $this->votes = $votes;
    }

    public function provide_votes(int $value, int $comment_id): int
    {
        return $this->votes[$comment_id] ?? 0;
    }
}

// get the data or query and calculate it
function wpse_418774_get_comment_vote_counts_for_post(int $post_id): array {
    $counts = get_transient( "comment_vote_counts_{$post_id}" );
    if ($counts && is_array($counts)) {
        return $counts;
    }

    // query and calculate vote counts...
    $counts = [
        // $comment_id => $vote_count,
        // 13 => 100,
        // 22 => 150,
        // 32 => 50,
    ];

    // see https://codex.wordpress.org/Easier_Expression_of_Time_Constants
    set_transient( "comment_vote_counts_{$post_id}", $counts, 5 * MINUTE_IN_SECONDS );

    return $counts;
}

// setup the data provider
function wpse_418774_provide_post_comments_vote_counts(int $post_id): void {
    $votes = new MyPostCommentsVotesCounts(
        wpse_418774_get_comment_vote_counts_for_post($post_id)
    );

    add_filter( 'wpse_418774_comment_vote_counts', [$votes, 'provide_votes'], 10, 2 );
}

// retrieve the votes data by applying a filter
function comment_vote_show_votes(): string {
    $sum_votes = apply_filters( 'wpse_418774_comment_vote_counts', 0, get_comment_ID() );

    return '<div class="comment-vote-votes">' . $sum_votes . '</div>';
}

// setup the provider at some suitable point of the WordPress lifecycle, e.g.
add_action( 'template_redirect', 'wpse_418774_set_up_post_comment_vote_counts' );
function wpse_418774_set_up_post_comment_vote_counts(): void {
    if (! is_single()) {
        return;
    }

    wpse_418774_provide_post_comments_vote_counts(get_the_ID());
}

Answer 2

In addition to the caching, you could get all of the comment scores in a single database query e.g.

SELECT  V.comment_id, V.vote_up, count(*) count
  FROM  wp_comment_vote V
  JOIN  wp_comments C USING (comment_id)
  WHERE C.comment_post_id = 1234
  GROUP BY V.comment_id, V.vote_up;

potentially with a clause to remove unapproved comments too, and / or an ORDER BY. This will return the number of up and down votes for each comment in separate rows (if up/down votes exist), but that's easily processable in PHP. (If this wasn't MySQL I'd probably try and do it in the database too, but MySQL doesn't support pivots or named queries which would make it easy to do, and IMO it's not worth over-complicating the query just for a relatively simple reduce function like that.)

Note that this query is going to be a bit expensive when you get thousands of votes. And I'm not sure if you can improve that with indexing: indexing the comment ID should help, but it's pretty much always going to have some portion of a table scan. So you possibly want to archive or sum the data somehow, e.g.

• have a comment score summary table, which has the total score per comment computed at a given point in time; store that point in time as the highest comment vote ID from the autoincrement somewhere e.g. in options
• to count the votes you can then take the summary score and scan for extra votes after the stored comment vote ID only
• potentially delete rows from the comment vote table earlier than that ID, except if you need to remember that a given user ID cast the vote, or a given IP address - then you'll need to preserve them so you can query on those, etc.

Which may or may not be a good way of scaling this. But either way you'll need to think about scaling!

Answer 3

As a follow up I'd like to share the complete solution (if everyone is interested). Thanks to @Antti Konskinen and @Rup again.

/*
Plugin Name: Vote on Comments 
Version:
*/

define('COMMENT_VOTE_PLUGIN_VERSION', "1.1.5");
define('COMMENT_VOTE_PLUGIN_URL', WP_PLUGIN_URL . "/" . dirname(plugin_basename(__FILE__)));
define('COMMENT_VOTE_PLUGIN_DIR', WP_PLUGIN_DIR . "/" . dirname(plugin_basename(__FILE__)));

require_once(COMMENT_VOTE_PLUGIN_DIR . '/inc/comment-vote-counts.php');

register_activation_hook( __FILE__, 'comment_vote_init_db' );
function comment_vote_init_db() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'comment_vote';

    $sql = "CREATE TABLE $table_name (
    comment_vote_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    post_id bigint(20) UNSIGNED NOT NULL,
    comment_id bigint(20) UNSIGNED NOT NULL,
    user varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    vote tinyint(1) NOT NULL,
    PRIMARY KEY  (comment_vote_id),
    KEY post_id_comment_id_vote (post_id, comment_id, vote),
    UNIQUE post_id_comment_id_user (comment_id, user),
    FOREIGN KEY (comment_id) REFERENCES xre2_comments(comment_ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (post_id) REFERENCES xre2_posts(ID) ON DELETE CASCADE ON UPDATE CASCADE
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}

class CommentVoteCounts
{
    protected array $votes;

    public function __construct(array $votes)
    {
        $this->votes = $votes;
    }

    public function provide_votes(int $value, int $comment_id): int
    {
        return $this->votes[$comment_id] ?? 0;
    }
}

function comment_vote_enqueuescripts()
{
    wp_enqueue_style('comment-vote', COMMENT_VOTE_PLUGIN_URL . '/assets/css/comment-vote.css', array(), COMMENT_VOTE_PLUGIN_VERSION);
    wp_enqueue_script('comment-vote', COMMENT_VOTE_PLUGIN_URL . '/assets/js/comment-vote.js', array('jquery'), COMMENT_VOTE_PLUGIN_VERSION);
    wp_localize_script('comment-vote', 'comment_vote_ajax', array('ajaxurl' => admin_url('admin-ajax.php')));
}
add_action('wp_enqueue_scripts', 'comment_vote_enqueuescripts');

function comment_vote_text($comment_text, $comment, $args)
{
    if ($comment->comment_approved) {
        $vote_link = comment_vote_showlink();
        return $comment_text . $vote_link;
    }
    return $comment_text;
}
add_filter('comment_text', 'comment_vote_text', 10, 3);

function comment_vote_showlink()
{
    error_log("Plugin URL: " . COMMENT_VOTE_PLUGIN_URL);

    $nonce = wp_create_nonce("comment_vote_nonce");
    $post_id = get_the_ID();
    $comment_id = get_comment_ID();

    $arguments = "'" . $nonce . "', " . $post_id . ", " . $comment_id;
    $link_up = '<a class="comment-vote-thumb" href="javascript:commentVote(' . $arguments . ", true" . ');"><span class="material-symbols-outlined">thumb_up</span></a>';
    $link_down = '<a class="comment-vote-thumb" href="javascript:commentVote(' . $arguments . ", false" . ');"><span class="material-symbols-outlined">thumb_down</span></a>';

    $label_votes = comment_vote_show_votes();
    $completelink = '<div class="comment-vote" id="comment-vote-' . $comment_id . '">';
    $completelink .= $link_up . $label_votes . $link_down;
    $completelink .= '<img id="comment-vote-loading-' . $comment_id . '" class="comment-vote-loading" style="display: none" src="' . COMMENT_VOTE_PLUGIN_URL . '/assets/images/loading.gif?ver' . COMMENT_VOTE_PLUGIN_VERSION . '" />';
    $completelink .= '</div>';
    return $completelink;
}

function comment_vote_ajaxhandler()
{
    if (!wp_verify_nonce($_POST['nonce'], "comment_vote_nonce")) {
        wp_send_json_error("Invalid Nonce");
    }
    if (!isset($_POST['post_id']) || !isset($_POST['comment_id']) || !isset($_POST['user']) || !isset($_POST['up'])) {
        wp_send_json_error('Missing params');
    }
    $up = $_POST['up'];
    if ($up != 'true' && $up != 'false') {
        wp_send_json_error("Invalid value for up param: [" . $up . "]");
    }
    $post_id = $_POST['post_id'];
    $comment_id = $_POST['comment_id'];
    $user = $_POST['user'];

    $new_votes = null;
    try {
        $vote = filter_var($up, FILTER_VALIDATE_BOOLEAN);
        $new_votes = vote($post_id, $comment_id, $user, $vote);
    } catch (Exception $e) {
        wp_send_json_error("Error: " . (string)$e);
    }
    if (!is_null($new_votes)) {
        wp_send_json_success($new_votes);
    }
    wp_send_json_error("Something didn't work.");
}
add_action('wp_ajax_nopriv_comment_vote_ajaxhandler', 'comment_vote_ajaxhandler');
add_action('wp_ajax_comment_vote_ajaxhandler', 'comment_vote_ajaxhandler');

function vote($post_id, $comment_id, $user, $vote): int
{
    global $wpdb;
    $table = $wpdb->prefix . 'comment_vote';
    $result = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM $table WHERE post_id=%s AND comment_id=%s AND user=%s",
            $post_id,
            $comment_id,
            $user
        )
    );
    $exists = $result > 0;

    if (!$exists) {
        $data = array(
            'post_id' => $post_id,
            'comment_id' => $comment_id,
            'user' => $user,
            'vote' => ($vote ? '1' : '-1')
        );
        $wpdb->insert($table, $data);
        update_votes_cache($post_id);
    }

    $comment_vote_counts = get_comment_vote_counts_for_post($post_id);
    $sum_votes = $comment_vote_counts[$comment_id];
    return $sum_votes;
}

function get_all_votes(int $post_id)
{
    global $wpdb;
    $table = $wpdb->prefix . 'comment_vote';
    $sql_query = <<<EOD
SELECT comment_id, SUM(vote) AS votes
FROM $table
WHERE post_id = '$post_id'
GROUP BY comment_id;
EOD;
    $results = $wpdb->get_results($sql_query);
    $arr = array();
    foreach ($results as $result) {
        $arr[$result->comment_id] = $result->votes;
    }
    return $arr;
}

// get the data or query and calculate it
function get_comment_vote_counts_for_post(int $post_id): array
{
    $counts = get_transient("comment_vote_counts_{$post_id}");
    if ($counts && is_array($counts)) {
        return $counts;
    }

    return update_votes_cache($post_id);
}

function update_votes_cache(int $post_id): array
{
    $vote_counts = get_all_votes($post_id);
    set_transient("comment_vote_counts_{$post_id}", $vote_counts, 1 * MINUTE_IN_SECONDS);

    return $vote_counts;
}

// setup the data provider
function provide_post_comments_vote_counts(int $post_id): void
{
    $vote_counts = new CommentVoteCounts(
        get_comment_vote_counts_for_post($post_id)
    );

    add_filter('comment_vote_counts', [$vote_counts, 'provide_votes'], 10, 2);
}

// retrieve the votes data by applying a filter
function comment_vote_show_votes(): string
{
    $comment_id = get_comment_ID();
    $sum_votes = apply_filters('comment_vote_counts', 0, $comment_id);

    return '<div id="comment-vote-votes-' . $comment_id . '" class="comment-vote-votes comment-vote-' . ($sum_votes == 0 ? 'neu' : ($sum_votes > 0 ? 'pos' : 'neg')) . '">' . $sum_votes . '</div>';
}

// setup the provider at some suitable point of the WordPress lifecycle, e.g.
add_action('template_redirect', 'set_up_post_comment_vote_counts');
function set_up_post_comment_vote_counts(): void
{
    if (!is_single()) {
        return;
    }
    provide_post_comments_vote_counts(get_the_ID());
}