How to use a different database to list and manage comments in the backend

Question

I need to make Wordpress use another database (not the one specified in wp-config site-wide) on the "Comments" admin backend. I then must be able to edit, approve, delete etc. comments as usual. The database server I am going to connect to has been setup to accept external connections.

I have a couple of ideas how to approach this:

1. from wp-config.php: have an if block to change the database connection strings if user is viewing comments page.

However this doesn't sound good to me, I assume this may create a lot of unnecessary database connections if user goes back and forth to the comments page.

1. modify wp-admin files: create an instance of wpdb and tell Wordpress to use it for comment administration.

However, I can't find the .php files that get the comments from the database (so I can change the database where they do it). Have been looking at edit-comments.php->class-wp-comments-list-table.php->class-wp-list-table.php (down to base class), but I can't see the bit that talks to the database.

1. Any hooks available to tap into and change the database? (doubt it)

Many thanks in advance.

Answer 1

You can filter query. See wpdb::query() in wp-includes/wp-db.php.

You get the complete SQL string as parameter. Then look for queries to the comments table:

add_filter( 'query', function( $query ) 
{
    global $wpdb;

    if ( FALSE === stripos( $query, "FROM $wpdb->comments" )
        return $query;

    // Now change the SQL
});

But you have to catch all the JOINs with other tables too. Not sure if this is possible in an elegant way.

Answer 2

The issue with trying to subvert whole database access is that even on page, dealing with comments, WP will still need and perform queries for other data. The challenge is not as much changing where query go, as changing it without everything falling apart.

Your best chance it probably to try some solution for sharding on WP level (such as HyperDB or SQL level).

PS it would help to suggest possible alternatives if you included context why do you need (or think you need) separate table for comments. There are people around that have experience dealing with large installations and who probably have more precise answer than generic theory.