3

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.

  • Would you mind to show how you created the View? (My)SQL Views aren't that commonly used, so it might help. Also please show us the fully WP_Query incl. arguments as this might be important. – kaiser Nov 1 '14 at 22:58
  • 1
    @Kaiser, I have updated my post with VIEW's code. – Annie Nov 1 '14 at 23:02
4

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.

| improve this answer | |
  • Would this change be permanent and does it has drawbacks (like any effect on insert / update)? Where should I put this code and what would be the scope of change? – Annie Nov 2 '14 at 0:33
  • I updated the answer. You can try this as your own plugin. Take a backup and then create for example the file /custom-view/custom-view.php inside your /wp-content/plugins/ directory and activate. – birgire Nov 2 '14 at 3:26
  • 1
    Great to hear it works for you. I updated the answer, to explain how you can access it. @kaiser amazingly it looks like it's working for her ;-) – birgire Nov 2 '14 at 18:07
  • 1
    aha, don't use this part in your edit 2, it will not target the SELECT queries only. Just use the code in the current answer, I just checked and it worked on my install. – birgire Nov 2 '14 at 18:27
  • 2
    @kaiser is_admin() is available very very early. The function is defined in load.php, absolutely the 1st file loaded by wp-settings.php. Regarding the constant it checks, it's defined even earlier, on the 1st line of wp-admin/admin.php, the admin bootstrap file. – gmazzap Nov 2 '14 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.