wp query - Sorting query_posts() with a complex orderby filter

I'm trying to order my posts with a complex orderby scenario which uses the CASE and WHEN syntax. To achieve that, I've created a filter :

add_filter( 'posts_orderby', 'order_properties_by_reference', 10, 2 );
function order_properties_by_reference( $orderby, $wp_query ){

    global $wpdb;

    $orderby = "CASE 
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 2000 AND 2999 THEN 1
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 4000 AND 4999 THEN 2
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 3000 AND 3999 THEN 3
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 1000 AND 1999 THEN 4
                else 4
                END";

    return $orderby;
}

And I've added this filter in my theme functions.php file.

Now, on a page of this theme, I'm trying to query the posts with this custom orderby filter with this code :

$args = array(
        'post_type' => 'property',
        'posts_per_page' => 20,
        'paged' => ( get_query_var('paged') ? get_query_var('paged') : 1)
);


query_posts($args);

But the filter seems not to be used by the query. I'm sure I'm missing something, can you assist?

I'm trying to order my posts with a complex orderby scenario which uses the CASE and WHEN syntax. To achieve that, I've created a filter :

add_filter( 'posts_orderby', 'order_properties_by_reference', 10, 2 );
function order_properties_by_reference( $orderby, $wp_query ){

    global $wpdb;

    $orderby = "CASE 
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 2000 AND 2999 THEN 1
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 4000 AND 4999 THEN 2
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 3000 AND 3999 THEN 3
                    WHEN {$wpdb->prefix}postmeta.meta_value BETWEEN 1000 AND 1999 THEN 4
                else 4
                END";

    return $orderby;
}

And I've added this filter in my theme functions.php file.

Now, on a page of this theme, I'm trying to query the posts with this custom orderby filter with this code :

$args = array(
        'post_type' => 'property',
        'posts_per_page' => 20,
        'paged' => ( get_query_var('paged') ? get_query_var('paged') : 1)
);


query_posts($args);

But the filter seems not to be used by the query. I'm sure I'm missing something, can you assist?

• wp-query
• filters
• mysql
• query-posts

Share Improve this question asked Feb 4, 2019 at 14:20 fraxoolfraxool 12144 bronze badges 1

• Note that querying posts via their post meta/custom fields is very slow/expensive. Additionally, you should never use query_posts, use the pre_get_posts filter to modify the parameters of the main query, don't create a replacement query with new parameters, at a minimum you're doubling the amount of work making the page twice as slow to run – Tom J Nowell ♦ Commented Feb 4, 2019 at 14:34

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Managed to solve my issue. I was missing the meta_key param in the args of my query_posts function.

Here it is with the fixed version :

$args = array(
        'post_type' => 'property',
        'posts_per_page' => 20,
        'meta_key' => 'my_meta_key',
        'paged' => ( get_query_var('paged') ? get_query_var('paged') : 1)
);