我需要为wordpress的mysql查询获取订单列表

zsbz8rwp 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(373)

我需要按三个字段排序列表。

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

这是由两个字段组成的查询排序列表：[自定义字段]发布年份（可能值2018、2017…）和[delautl wp post date字段]发布日期。没关系。
但是现在我需要显示[custom\u field]为1的项目。问题是，如果此字段有三种状态：-即将出现的项=1-即将出现的项=0-以及自定义字段即将出现的项不存在
我需要得到第一个即将到来的项目=1和其他应按出版年和日期订购的订购清单。我该怎么做。如果你需要更多的信息，告诉我。谢谢。

mysql wordpress sql-order-by

来源：https://stackoverflow.com/questions/50026027/i-need-mysql-query-for-wordpress-for-get-ordered-list

1条答案

按热度按时间

aiqt4smr1#

我得到了需要的有序列表，添加了所有帖子的meta-key=0和update-meta方法。所以所有的领域都有这个领域。
接下来我使用wp\u query meta\u 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() ) : ...

赞(0）回复(0）举报 2021-06-24

我来回答

我需要为wordpress的mysql查询获取订单列表

1条答案

相关问题

热门标签

最新问答