Order by DESC, ASC in custom WP_Query

  • I need to do multi-level ordering in a query. The issue is with ordering one value DESC and the other ASC as in SQL. The following SQL seems to give me what I want when I run it in terminal:

    SELECT DISTINCT * FROM wp_posts 
    INNER JOIN wp_postmeta 
    ON wp_posts.ID = wp_postmeta.post_id 
    WHERE wp_posts.post_type = 'post' 
    AND wp_postmeta.meta_key = 'pb_issue_featured'
    AND wp_posts.post_status = 'publish' 
    ORDER BY wp_postmeta.meta_value DESC, wp_posts.menu_order ASC;
    

    The pb_issue_featured is a boolean value. The end result I need is the query to display posts that have a meta value of 1 for this field at the top, then all the others bellow. Then the second tier ordering is the designated menu_order (I'm using the post types order plugin).

    The issue is that my boolean value needs to be ordered high to low (1 to 0) but the menu_order is the opposite. What is ordered first with the plugin has a menu order of 1. So using the built in 'orderby' in WP_Query doesn't work. Anyone have suggestions? I looked into the 'posts_orderby' filter but couldn't get it to take. Wasn't really sure where it should be applied or how I could troubleshoot it. It just didn't re order the way I had it.

    Thanks for the help! I will post the actual WP_Query if it's relevant but I wanted to keep this as short as possible.

    The query args:

    $args = array(
            'post_type' => 'post',
            'meta_key' => 'pb_issue_featured',
            'orderby'   => 'meta_value',
            'order' => 'DESC',
            'post_status' => 'publish',
            'posts_per_page' => $posts,
            'paged' => $paged,
            'meta_query' => array(
                array(
                    'key' => 'headline',
                    'value' => 1,
                    'compare' => '!=' 
                    )
                )
            );
    $q = new WP_Query($args);
    

    Well, you should post the WP_Query, to see if it is formed right.

    There it is in all it's glory. I'm currently not ordering by anything but that boolean meta value b/c it was the only way to get that level or sorting working. The site also uses 'headline' posts which take precedence over 'featured' posts. Thus I exclude them in this query

  • Try this:

    $args = array(
            'post_type' => 'post',
            'meta_key' => 'pb_issue_featured',
            'orderby'   => 'meta_value',
            'order' => 'DESC',
            'posts_per_page' => $posts,
            'paged' => $paged,
            'paged' => 1,
            'meta_query' => array(
                array(
                    'key' => 'headline',
                    'value' => 1,
                    'compare' => '!=' 
                    )
                )
            );
    
    add_filter( 'posts_orderby', 'filter_query' );
    $q = new WP_Query($args);
    remove_filter( 'posts_orderby', 'filter_query' );
    
    function filter_query( $query ) {
        $query .= ', wp_posts.menu_order ASC';
        return $query;
    }
    

    Like a boss Spartakus. Thanks for the concise and accurate answer. This worked like a charm

    You could also use an array in the order by clause (WP 4.0 and newer)

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM