自定义字段中缺少帖子的WordPress查询顺序 [英] Wordpress Query order by custom field missing posts

问题描述

这是我当前的查询:

query_posts(array_merge(array('tag' => $pagetag,'meta_key'=>priority,'orderby' =>meta_value, 'order' =>'ASC','paged' => get_query_var('paged'))));

我的问题是查询仅向我显示具有'meta_key'值的帖子，这意味着'priority'不为NULL. 如何改善此查询，以便仍按我的meta_key排序，但同时显示所有非NULL的帖子?

My problem is that the query shows me only the post that has values for my 'meta_key' meaning that 'priority' is not NULL. How can I improve this query so that it will still orderby my meta_key but will show all the posts that aren't NULL as well?

提前谢谢！

推荐答案

问题在于，只要您在条件中提到meta_key，WordPress就会在wp_postmeta表中添加INNER JOIN.解决该问题的一种方法是在order by子句上添加一个过滤器，如下所示:

The problem is that WordPress adds an INNER JOIN to the wp_postmeta table as soon as you mention meta_key in your conditions. One way around the problem is to add a filter on the order by clause, something like this:

function so_orderby_priority($original_orderby_statement) {
    global $wpdb;

    return "(SELECT $wpdb->postmeta.meta_value
               FROM $wpdb->postmeta
              WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
                AND $wpdb->postmeta.meta_key = 'priority') ASC";
}

add_filter('posts_orderby', 'so_orderby_priority');

query_posts(
    array(
        'tag' => $pagetag,
        'paged' => get_query_var('paged')
    )
);

remove_filter('posts_orderby', 'so_orderby_priority');

请注意，MySQL首先对NULL进行排序-如果您希望对它们进行最后排序，请尝试这样的操作(假设所有优先级均按字母顺序在ZZZZZ之前):

Note MySQL sorts NULLs first - if you want them sorted last, try something like this (assuming all your priorities come before ZZZZZ alphabetically):

function so_orderby_priority($original_orderby_statement) {
    global $wpdb;

    return "IFNULL(
               (SELECT $wpdb->postmeta.meta_value
                  FROM $wpdb->postmeta
                 WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
                   AND $wpdb->postmeta.meta_key = 'priority'),
                'ZZZZZ') ASC";
}

修改

这里有一些解释，假定您至少了解SQL.

Here's a bit more explanation, which assumes you understand SQL at least a bit.

您的原始query_posts导致针对数据库运行以下查询:

Your original query_posts resulted in the following query running against the database:

SELECT wp_posts.*
FROM   wp_posts
       INNER JOIN wp_term_relationships ON ( wp_posts.id = wp_term_relationships.object_id )
       INNER JOIN wp_postmeta ON ( wp_posts.id = wp_postmeta.post_id )
WHERE  1 = 1
       AND ( wp_term_relationships.term_taxonomy_id IN ( 3 ) )
       AND wp_posts.post_type = 'post'
       AND ( wp_posts.post_status = 'publish'
              OR wp_posts.post_status = 'private' )
       AND ( wp_postmeta.meta_key = 'priority' )
GROUP  BY wp_posts.id
ORDER  BY wp_postmeta.meta_value ASC
LIMIT  0, 10; 

INNER JOIN wp_postmeta是从结果中删除所有优先级不高的帖子的原因.

That INNER JOIN wp_postmeta is what removed any posts without a priority from your results.

从query_posts中删除与meta_*相关的条件:

Removing the meta_* related conditions from your query_posts:

query_posts(
    array(
        'tag' => $pagetag,
        'paged' => get_query_var('paged')
    )
);

解决了该问题，但排序顺序仍然错误.新的SQL是

solved that problem, but the sort order is still wrong. The new SQL is

SELECT wp_posts.*
FROM   wp_posts
       INNER JOIN wp_term_relationships ON ( wp_posts.id = wp_term_relationships.object_id )
WHERE  1 = 1
       AND ( wp_term_relationships.term_taxonomy_id IN ( 3 ) )
       AND wp_posts.post_type = 'post'
       AND ( wp_posts.post_status = 'publish'
              OR wp_posts.post_status = 'private' )
GROUP  BY wp_posts.id
ORDER  BY wp_posts.post_date DESC
LIMIT  0, 10; 

posts_orderby过滤器允许我们更改ORDER BY子句:wp_posts.post_date DESC被过滤器返回的内容代替.最终的SQL变为:

The posts_orderby filter allows us to change the ORDER BY clause: wp_posts.post_date DESC gets replaced by what the filter returns. The final SQL becomes:

SELECT wp_posts.*
FROM   wp_posts
       INNER JOIN wp_term_relationships ON ( wp_posts.id = wp_term_relationships.object_id )
WHERE  1 = 1
       AND ( wp_term_relationships.term_taxonomy_id IN ( 3 ) )
       AND wp_posts.post_type = 'post'
       AND ( wp_posts.post_status = 'publish'
              OR wp_posts.post_status = 'private' )
GROUP  BY wp_posts.id
ORDER  BY (SELECT wp_postmeta.meta_value
           FROM   wp_postmeta
           WHERE  wp_posts.id = wp_postmeta.post_id
                  AND wp_postmeta.meta_key = 'priority') ASC
LIMIT  0, 10 

这将满足您的需求.

这篇关于自定义字段中缺少帖子的WordPress查询顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！