mysql - 我需要 mysql 查询 wordpress 来获取有序列表

Question

我需要按三个字段获取有序列表。

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

这是由两个字段组成的查询排序列表:[custom_field] pub_year(可能值 2018、2017...)和 [delautl wp post date field] post_date。没关系。

但现在我需要显示 [custom_field] 即将到来 = 1 的项目。问题是，如果此字段存在三种状态: - 即将推出的项目 = 1 - 即将推出的项目 = 0 - 以及不存在自定义字段的项目

我需要获取订购列表，其中第一个项目即将到来 = 1，其他项目应按 pub_year 和日期排序。我怎样才能做到这一点。如果您需要更多信息，请告诉我。非常感谢。

Answer 1

我需要有序列表，并使用更新元方法为所有帖子添加即将到来的元键= 0。所以所有字段都有这个字段。

接下来我使用了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() ) : ...