0

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

        }

    }

}
1
  • 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 Jan 11 at 8:33
0

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.org/reference/hooks/posts_orderby/

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.