为何$wpdb->get_results()与add_filter()中get_posts()返回值不同？

Hey there! As someone who’s wrestled with WordPress query nuances plenty of times, let’s break down why you’re seeing mismatched results and fix your currency-converted sorting issue.

Why the Results Don’t Match #

The core problem boils down to how these two methods interact with WordPress’s core system:

• get_posts() doesn’t just run a raw query—it passes through dozens of built-in filters (like posts_clauses, posts_orderby, and plugin-specific hooks from tools like WooCommerce) that modify the final query. These filters enforce post status rules, post type restrictions, and even third-party logic you might not be aware of.
• $wpdb->get_results() is a direct database call that skips all those WordPress-level checks. Your raw SQL might be missing critical conditions (like filtering for published posts only, or respecting post type hierarchies) that get_posts() handles automatically—hence the inconsistent returns.

The Right Way to Sort by Converted USD Prices #

Instead of bypassing WordPress’s query system, we’ll hook into it to safely modify the sorting logic while keeping all core rules intact. Here’s a step-by-step solution:

1. Use the posts_orderby Filter #

We’ll customize the ORDER BY clause of your query to calculate converted USD prices directly in SQL (since we can’t use your PHP conversion function in the database call).

Assuming your product prices and currencies are stored in custom fields _product_price and _product_currency, here’s the code:

add_filter('posts_orderby', 'sort_products_by_converted_usd', 10, 2);
function sort_products_by_converted_usd($orderby, $query) {
    // Only target your specific product query to avoid breaking other site functionality
    if (!is_admin() && $query->get('post_type') === 'product' && $query->get('orderby') === 'converted_usd') {
        global $wpdb;

        // Ensure we only fetch posts with both price and currency set
        $query->query_vars['meta_query'][] = array(
            'relation' => 'AND',
            array('key' => '_product_price'),
            array('key' => '_product_currency')
        );

        // Replace the exchange rate logic with your actual conversion rules
        $orderby = "
            CASE
                WHEN pm_currency.meta_value = 'EUR' THEN pm_price.meta_value * 1.09
                WHEN pm_currency.meta_value = 'GBP' THEN pm_price.meta_value * 1.27
                WHEN pm_currency.meta_value = 'JPY' THEN pm_price.meta_value * 0.0071
                -- Add more currencies as needed
                ELSE pm_price.meta_value
            END {$query->get('order')},
            {$wpdb->posts}.ID {$query->get('order')}
        ";

        // Join the postmeta table twice to access both price and currency fields
        $query->query_vars['join'] .= "
            LEFT JOIN {$wpdb->postmeta} pm_price ON {$wpdb->posts}.ID = pm_price.post_id AND pm_price.meta_key = '_product_price'
            LEFT JOIN {$wpdb->postmeta} pm_currency ON {$wpdb->posts}.ID = pm_currency.post_id AND pm_currency.meta_key = '_product_currency'
        ";
    }
    return $orderby;
}

2. Call the Customized Query #

When you need to fetch sorted products, use get_posts() with a custom orderby parameter to trigger our filter:

$sorted_products = get_posts(array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'orderby' => 'converted_usd',
    'order' => 'ASC', // Change to 'DESC' for highest prices first
    'posts_per_page' => -1 // Adjust to your desired number of results
));

Why This Works Better Than Raw $wpdb #

• It respects WordPress’s core rules (like only fetching published posts, avoiding trash items, etc.).
• It plays nicely with plugins that modify product queries (like WooCommerce’s stock filters or search logic).
• It’s maintainable—you won’t have to manually update your raw SQL if your site’s post type rules change.

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