Sort archive view with pre_get_posts hook

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);