I need mysql query for wordpress for get ordered list

Question

I need to get ordered list by three fields.

$sql = "
SELECT 
    SQL_CALC_FOUND_ROWS $wpdb->posts.ID 
FROM 
    $wpdb->posts 
INNER JOIN 
    $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id )
INNER JOIN 
    $wpdb->postmeta AS mt1 ON ( $wpdb->posts.ID = mt1.post_id ) 

WHERE 
    1=1

AND 
( 
   ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Book' ) 
OR ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Book chapter' ) 
OR ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Journal Article' )
)

/*AND 
( 
   mt3.meta_key = 'forthcoming'
)*/

AND 
    $wpdb->postmeta.meta_key = 'pub_year'        
AND 
    $wpdb->posts.post_type = 'publication' 
AND 
    $wpdb->posts.post_status = 'publish' 
GROUP BY 
    $wpdb->posts.ID 

ORDER BY 

    /*FIELD(mt3.meta_key, 'forthcoming') DESC,
    FIELD(mt3.meta_value, 'null') DESC,*/
    //FIELD($wpdb->postmeta.meta_value,1),

    $wpdb->postmeta.meta_value DESC, 
    $wpdb->posts.post_date DESC 
";

$total = count($wpdb->get_results($sql));
$offset = ( $paged * $posts_per_page ) - $posts_per_page;

$results = $wpdb->get_results( $sql . "
    LIMIT 
        $offset, $posts_per_page" );

This is query ordered list by two fields: [custom_field] pub_year (possible values 2018, 2017...) and [delautl wp post date field] post_date. It is ok.

But now I need to show items with [custom_field] forthcoming = 1. Problem that there three states if this field: - items with forthcoming = 1 - items with forthcoming = 0 - and items where custom field forthcoming not exist

I need to get ordered list with firts items forthcoming = 1 and other should be ordered by pub_year and date. How I can do this. Tell me if you need some more info. Thanks a lot.

Answer 1

I was get needed ordered list with adding for all posts meta key forthcoming = 0 with update meta method. So all fields have this field.

Next I was used wp_query meta_query args:

$pub_series = 'Book,Book chapter,Journal Article';
        $search_args = array();
        if (!empty($pub_series)) {
            $pub_series_arr = explode(",", $pub_series);
            if (!empty($pub_series_arr)) {
                $search_args_add = array(
                    'relation' => 'OR'
                );
                foreach ($pub_series_arr as $pub_series_el) {
                    $adv_search_query_el = trim($pub_series_el);
                    if (!empty($adv_search_query_el)) {
                        $search_args_add[] = array(
                            'key' => 'pub_series',
                            'value' => $adv_search_query_el,
                            'compare' => '='
                        );
                    }
                }
                $search_args['meta_query'][] = $search_args_add;
            }
        }
        $search_args['meta_query'][] = array(
            'relation' => 'OR',
            'forthcoming_clause' => array(
                'key' => 'forthcoming',
                'value' => '1'
            ),
            'forthcoming_clause0' => array(
                'key' => 'forthcoming',
                'value' => '0'
            )
        );
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$args = array(
    'post_type' => 'publication',
    'post_status' => 'publish',
    'posts_per_page' => 10,
    'orderby' => array(
        'forthcoming_clause' => 'DESC',
        'meta_value' => 'DESC',
        'date' => 'DESC'
    ),
    'meta_key' => 'pub_year',
    'paged' => $paged
);
$args = array_merge($args, $search_args);

 $posts = new WP_Query( $args );
    if ( $posts->have_posts() ) : ...