WP_Query to work with custom view

Question

I have created a Database VIEW on wp_posts which selects its all columns and exposes an extra calculated columns (average). So my View is identical to wp_posts plus one column, which I want to use for sorting at later stage.

• Is there a way to bind WP_Query to that view?
• In general, is it possible to bind WP_Query to a custom table, which has all the mandatory columns of WP_Query?

I am able to get records with $wpdb->get_results( _custom_query_here_ ), but that doesn't let me use WP_Query features.

P.S.: I thought WP_Query::parse() is the answer to my curiosity, but apparently not.

EDIT

Here is the VIEW query:

CREATE VIEW `calculated_posts`
AS 
  SELECT *, 
         (SELECT Avg(CAST(`meta_value` as SIGNED))
          FROM   `wp_commentmeta` 
          WHERE  meta_key = 'rating' 
                 AND comment_id IN (SELECT comment_id 
                                    FROM   wp_comments 
                                    WHERE  comment_post_id = ID)) AS 
            rating_average 
  FROM   wp_posts;

Edit 2:

From the solution provided by @birgire here: https://wordpress.org/support/topic/changing-table-name-and-make-wordpress-still-working, I used the following code:

global $wpdb;
$wpdb->posts = 'calculated_posts';

The WP_Query runs successfully, but I can't access the field rating_average.

Answer 1

Use a custom view in the front-end:

You can try to modify the SELECT queries in the front-end with the following (alpha) plugin:

<?php
/**
 * Plugin Name: wpdb - a custom SELECT view for the wp_posts table on the front-end
 * Version:     alpha
 */

! is_admin() && add_filter( 'query', function( $query ) {
    global $wpdb;
    $view = 'calculated_posts'; // <-- Edit to your needs.
    if( 'select' === mb_substr( strtolower( $query ) , 0, 6 ) )
        if( false !== mb_stripos( $query, $wpdb->posts ) )
            $query = str_ireplace( $wpdb->posts, $view, $query );

    return $query;
}, PHP_INT_MAX );

by using the query filter of the wpdb class.

We don't want to modify the INSERT, UPDATE and DELETE queries.

This will only affect queries made from the native wpdb class, but not for example direct MySQLi calls.

Notice that there's of course the possibility that plugins and themes can bybass the native wpdb class when connecting to the database.

There might also be examples of more complex queries, e.g. combination of SELECT and INSERT. The above plugin could be modified to adjust for these cases.

Notice: Remember to take a backup before trying.

Accessing the extra field of the custom view:

The extra rating_average field should now be available in the WP_Post instance, like:

$post->rating_average

We can also create a custom template tag:

function get_the_rating_average()   
{
    $post = get_post();
    return ! empty( $post ) ? $post->rating_average : false;
}  

Here I'm just modifying the structure of the get_the_ID() function.

The corresponding display function is:

function the_rating_average()   
{
    echo get_the_rating_average();
}  

We can now easily access the extra field in the loop:

$q = new WP_Query( array( 'posts_per_page' => '5' ) );
while( $q->have_posts() ) : $q->the_post();
    the_title();
    the_rating_average();  #<-- displaying our extra field here
endwhile;

We also better use a name for our extra field that's not already in use by the default fields.