Thank you! Your feedback has been delivered
Thank you! Your feedback has been sent

Independent (ASC/DESC) order values in a WordPress query

Actually there are two parts to this. Both should be pretty easy I think. First of all this query isn't doing what it should. I need to get three posts in category '3', regardless of whether the custom field of "featured_post" is true or false.

$args = array(
    'posts_per_page'    => 3, 
    'cat'               => 3,
    'meta_key'          => 'featured_article',
    'orderby'           => 'meta_value_num',
    'order'             => 'ASC',
);
$listing_query = new WP_Query($args);

Then what I need to do is have enable independent (ASC/DESC) orderyby in the query. Problem is as noted here in the WP Bug Tracker, this is not possible in the regular query statement. What I need to do is use the *posts_orderby* filter.

So what I want is for the posts to be ordered first by if they are a 'featured_article' (a true/false custom field). And then the posts would be ordered by date, most recent first. In the ideal world without the bug and my query working right it would look like this:

'orderby'           => 'meta_value_num date',
'order'             => 'ASC DESC',
User Gravatar

creativelogic

Posted Jan 21 2014 15:43 UTC

$50


  • Assigned To fulippo
  • Solved
  • wordpress
    query
  • 1176 Views

7 Replies


You should be able to achieve what you want by doing something like:

$args = array(
    'posts_per_page'    => 3, 
    'cat'               => 3,
    'meta_key'          => 'featured_article'
);
$listing_query = new WP_Query($args);

// Add these functions to your functions.php and rename them as you need
function my_filter_fields($fields_statement) {
    global $listing_query, $wpdb;
     if($listing_query->query['meta_key'] == 'featured_article'){
         $fields_statement .= ", $wpdb->postmeta.meta_value as featured_article";
     }
    return $fields_statement;
}

function my_filter_join($join_statement) {
    global $listing_query, $wpdb;
    if($listing_query->query['meta_key'] == 'featured_article'){
        $join_statement = "LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id AND $wpdb->postmeta.meta_key = 'featured_article'";
    }
    return $join_statement;
}

function my_filter_orderby($orderby_statement) {
    global $listing_query, $wpdb;
    if($listing_query->query['meta_key'] == 'featured_article'){
        $orderby_statement = "CAST(featured_article AS SIGNED) DESC, $wpdb->posts.post_date ASC"; // replace here orders
    }
    return $orderby_statement;
}




add_filter('posts_fields', 'my_filter_fields');
add_filter('posts_join', 'my_filter_join');
add_filter('posts_orderby', 'my_filter_orderby');

First function add a new field to the fields to select then we modify the sql by adding a left join (second function) and finally we order by the added field and post_date. I haven't tested it yet but I recently had a similar need and solved the problem this way.

User Gravatar

fulippo

Posted Jan 22 2014 5:18 UTC

Hello and thanks for stepping in Fulippoi! Unfortunately, we still have one key issue. Still only posts marked as "featured_articles" are being displayed, when I need all cat = 3 posts displayed.

NOTE: just a typo probably but your second function above should be call my_filter_join.

User Gravatar

creativelogic

Posted Jan 22 2014 6:29 UTC

I'll try to reproduce the problem to get a more complete overwiew on the scenario. I've fixed the typo as you suggested. Thank you

User Gravatar

fulippo

Posted Jan 22 2014 6:40 UTC

Please note, this project/bounty is still active, I'm trying to get this question moved to back to the general queue, because I still could use a solution for this. Thanks!!

User Gravatar

creativelogic

Posted Jan 22 2014 20:34 UTC

Hi, sorry for the late response. I had to reproduce all data to obtain a clear scenario. This is the reviewed and tested code which worked as desired on my test enviroment:

// Add these functions to your functions.php and rename them as you need
function my_filter_fields($fields_statement, $wp_query) {
    global $wpdb;
    if($wp_query->query['meta_key'] == 'featured_article'){
         $fields_statement .= ", $wpdb->postmeta.meta_value as featured_article";
    }
    return $fields_statement;
}

function my_filter_join($join_statement, $wp_query) {
    global $wpdb;

    if($wp_query->query['meta_key'] == 'featured_article'){

        // We turn an inner join to postmeta table into a left join
        $join_statement = str_replace("INNER JOIN {$wpdb->postmeta}", "LEFT JOIN {$wpdb->postmeta}", $join_statement);

        // then we add an additional "ON" requirement to the left join. 
        // This is because we must tell mysql to retrieve all posts with optional meta key  
        $join_statement = str_replace("({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id)", "({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) AND {$wpdb->postmeta}.meta_key = 'featured_article'", $join_statement);
    }
    return $join_statement;
}

function my_filter_where($where_statement, $wp_query) {
    global $wpdb;
    // Remove where requirement for meta_key (see my_filter_join)
    if($wp_query->query['meta_key'] == 'featured_article'){
        $where_statement = str_replace("AND (wp_postmeta.meta_key = 'featured_article' )", "", $where_statement);
    }
    return $where_statement;
}

function my_filter_orderby($orderby_statement, $wp_query) {
    global $wpdb;
    if($wp_query->query['meta_key'] == 'featured_article'){
        $orderby_statement = "featured_article DESC, $wpdb->posts.post_date ASC"; // replace here orders, optionally you can cast meta_value (according to the value)
    }
    return $orderby_statement;
}

add_filter('posts_fields', 'my_filter_fields',10, 2);
add_filter('posts_join', 'my_filter_join',10, 2);
add_filter('posts_where', 'my_filter_where',10, 2);
add_filter('posts_orderby', 'my_filter_orderby',10, 2);

I added a new filter function to modify the where condition. Please let me know if it works as expected.

Filippo

User Gravatar

fulippo

Posted Jan 23 2014 14:37 UTC

Solution

This didn't solve your task? Get your own custom solution.

Awesome, this worked perfectly! I had to change the posts.post_date sort to DESC in order for the newer featured and non-featured posts to show first. Otherwise, this was perfect. Thank you very much, especially as this was so much more complex than I thought and it should be. :)

User Gravatar

creativelogic

Posted Jan 23 2014 15:29 UTC

Glad it worked!

User Gravatar

fulippo

Posted Jan 24 2014 2:19 UTC

Add a reply

By posting a reply on CodersClan you agree to our Terms & Conditions