I have a custom database table called views containing product IDs, an overall view total and a number of other columns.

I have written the following code where I was attempting to add a column in the dashboard to a products custom post type and display the column value from my database table, this works fine. However I cannot figure out how to allow this column to be sortable. I understand how to make custom columns sortable in WordPress, however because this is a value from a custom database table I can't see how I would be able to use $query->set() to get the sorting working. Is this just not possible as it isn't post meta? Is there a way to order by the column value somehow?

add_action( 'manage_product_posts_custom_column', array( $this, 'product_columns_values' ) );
add_action( 'manage_edit-product_sortable_columns', array( $this, 'product_columns_sortable' ) );
add_action( 'pre_get_posts', array( $this, 'product_columns_orderby' ) ); 

public function product_columns_values( $name ) {

    global $post;
    global $wpdb;

    switch ( $name ) {

        case 'views':

            $views = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(product_id) AS views FROM {$wpdb->prefix}views WHERE product_id = %d", $post->ID ) );

            if( !empty( $views ) ) {

                echo esc_html( $views );

            } else {

                echo esc_html( '0' );

            }

            break;

        default:

            break;
    }

}

public function product_columns_sortable( $columns ) {

    $columns['views'] = 'views';
    return $columns;

}

public function product_columns_orderby( $query ) {

    if ( is_admin() && 'product' == $query->get( 'post_type' ) ) {

        $orderby = $query->get( 'orderby');

        if ( 'views' == $query->get( 'orderby') ) {

            // This is where I can't seem to do anything further as I can only set meta values, unsure on how to amend the query to take into account my views value that is not meta

        }

    }

}

I have a custom database table called views containing product IDs, an overall view total and a number of other columns.

I have written the following code where I was attempting to add a column in the dashboard to a products custom post type and display the column value from my database table, this works fine. However I cannot figure out how to allow this column to be sortable. I understand how to make custom columns sortable in WordPress, however because this is a value from a custom database table I can't see how I would be able to use $query->set() to get the sorting working. Is this just not possible as it isn't post meta? Is there a way to order by the column value somehow?

add_action( 'manage_product_posts_custom_column', array( $this, 'product_columns_values' ) );
add_action( 'manage_edit-product_sortable_columns', array( $this, 'product_columns_sortable' ) );
add_action( 'pre_get_posts', array( $this, 'product_columns_orderby' ) ); 

public function product_columns_values( $name ) {

    global $post;
    global $wpdb;

    switch ( $name ) {

        case 'views':

            $views = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(product_id) AS views FROM {$wpdb->prefix}views WHERE product_id = %d", $post->ID ) );

            if( !empty( $views ) ) {

                echo esc_html( $views );

            } else {

                echo esc_html( '0' );

            }

            break;

        default:

            break;
    }

}

public function product_columns_sortable( $columns ) {

    $columns['views'] = 'views';
    return $columns;

}

public function product_columns_orderby( $query ) {

    if ( is_admin() && 'product' == $query->get( 'post_type' ) ) {

        $orderby = $query->get( 'orderby');

        if ( 'views' == $query->get( 'orderby') ) {

            // This is where I can't seem to do anything further as I can only set meta values, unsure on how to amend the query to take into account my views value that is not meta

        }

    }

}

• custom-post-types
• sort

• Your question is more to SQL and I don't think you can do the sorting at database level unless if you've got a table with each row having the post ID and the total views in their own column. – Sally CJ Commented Jan 11, 2021 at 8:33

1 Answer 1

You can sort the results based on your custom table data by applying a custom filter to your existing query using the "posts_orderby" hook (untested example code):

add_filter('posts_orderby', 'edit_posts_orderby');

function edit_posts_orderby($orderby_statement) {
    $orderby_statement = "views DESC";
    return $orderby_statement;
}

Source: based on user comment at: https://developer.wordpress/reference/hooks/posts_orderby/