如何修改WooCommerce默认排序，按mwb_wpr_data表商品浏览量排序？

Alright, let's figure out how to make WooCommerce sort products by your custom view count from the mwb_wpr_data table. The existing Post View Counter code works because it uses post meta, but since your data lives in a separate table, we need to adjust the approach to join that table and calculate view counts first. Here's the step-by-step solution:

We'll use two key WordPress filters to connect your view data table and adjust the sorting logic. Here's the adapted code:

add_action( 'pre_get_posts', 'custom_product_view_sort' );
function custom_product_view_sort( $query ) {
    // Target only main queries for WooCommerce product pages (shop, categories, tags, search)
    if ( $query->is_main_query() && ( $query->is_shop() || $query->is_product_category() || $query->is_product_tag() || $query->is_search() ) && ! is_admin() ) {
        // Hook in to join our custom view table
        add_filter( 'posts_join', 'join_product_view_table' );
        // Set the sorting logic to use our calculated view count
        add_filter( 'posts_orderby', 'orderby_product_view_count' );
        
        // Set sort direction to descending (most viewed first)
        $query->set( 'order', 'DESC' );
    }
}

function join_product_view_table( $join ) {
    global $wpdb;
    // Join a subquery that counts total views per product from your table
    $join .= " LEFT JOIN (
        SELECT productid, COUNT(*) as view_count
        FROM {$wpdb->prefix}mwb_wpr_data
        WHERE action = 'view'
        GROUP BY productid
    ) AS product_views ON {$wpdb->posts}.ID = product_views.productid ";
    return $join;
}

function orderby_product_view_count( $orderby ) {
    // Sort by view count first, fallback to post date if a product has no views
    $orderby = " product_views.view_count DESC, {$wpdb->posts}.post_date DESC ";
    return $orderby;
}

Key Details to Understand: #

• pre_get_posts Hook: We only target public-facing WooCommerce product queries (shop page, category/tag archives, search results) and exclude the admin area to avoid messing with backend sorting.
• posts_join Filter: This adds a left join to a subquery that calculates how many times each product was viewed (by counting rows in mwb_wpr_data where action = 'view'). Using {$wpdb->prefix} ensures compatibility with custom WordPress table prefixes (like wp_ or something else you might use).
• posts_orderby Filter: We prioritize sorting by the calculated view_count in descending order. We also add a fallback to post date for products with no views, so they don't get stuck at the very bottom.
• Why We Can't Use the Original Post View Counter Code: That plugin stores view counts as post meta (directly in the wp_postmeta table), so it can use the simple orderby parameter. Your data is in a separate table, so we need to join it first before we can sort by its values.

Quick Checks: #

• Double-check that the productid column in mwb_wpr_data matches the ID of your WooCommerce product posts (which it should, since it's a foreign key).
• If you want this to be a selectable sorting option (instead of replacing the default sort entirely), you'd need to add a custom option via the woocommerce_catalog_orderby filter—but this code directly replaces the default sorting behavior as you requested.

内容的提问来源于stack exchange，提问作者BernardoLima