Sort archive view with pre_get_posts hook

by

I have function like this:

//sorting archive query by pinned posts
function archive_sort_pinned($query){
    $option = get_option('hsa_story_cats');
    $current_category = get_queried_object();

    if(!is_admin() && $query->is_main_query() && is_category($option)){
        $query->set('meta_query', array(
            'relation' => 'OR',
            array(
                'key'       => 'story_cat_order_'. $current_category->term_id,
                'compare'   => 'EXISTS',
            ),
            array(
                'key'       => 'story_cat_order_'. $current_category->term_id,
                'compare'   => 'NOT EXISTS',
                'value' => ''
            )
        ));
        $query->set('orderby', array( 'meta_value_num' => 'ASC', 'date' => 'DESC' ));
        $query->set('meta_key','story_cat_order_'. $current_category->term_id);
    }
}
add_action('pre_get_posts', 'archive_sort_pinned');

The problem is: This will return only posts with key story_cat_order_<cat_id>, when I remove last line meta key then I get all posts but they are not ordered.

I want to order them by this meta_key if they have it or by date if they don’t have meta_key.

1 Answer
1

I solved this with a different approach. I used posts_join and posts_orderby filters.

Do not forget to cast meta value as unsiged to avoid string ordering instead of int. 

//edit query join
function hsa_story_join($join){

    global $wpdb;
    $option = get_option('hsa_story_cats');
    if(!is_admin() && is_category($option)) {
        $current_category = get_queried_object();
        $join .= " LEFT JOIN (SELECT post_id, meta_key, meta_value FROM $wpdb->postmeta WHERE meta_key = 'story_cat_order_". $current_category->term_id ."') AS hsam ON $wpdb->posts.ID = hsam.post_id ";
    }
    return $join;
}
add_filter('posts_join', 'hsa_story_join');

//edit query order by
function hsa_story_order($order){

    $option = get_option('hsa_story_cats');
    if(!is_admin() && is_category($option)) {
        $current_category = get_queried_object();
        $order = " IF(hsam.meta_key='story_cat_order_". $current_category->term_id ."', cast(hsam.meta_value as unsigned), 99999) ASC, " . $order;
    }

    return $order;
}
add_filter('posts_orderby', 'hsa_story_order',99,1);

Leave a Comment