Order by meta value or date?

时间：2011-03-23 作者：v3nt

有一个名为startDate 但这只是少数几个事件。我想知道它是否适合我可以使用的职位post_date 要生成帖子列表？

// if meta_key _postmeta.startDate isn\'t set get the rest by posts.post_date

query_posts(
    array(
        array(
            \'posts_per_page\' => 10,
            \'meta_key\' => \'startDate\',
            \'meta_value\' => date(\'Y-m-d\'),
            \'meta_compare\' => \'<\',
            \'orderby\' => \'meta_value\',
            \'order\' => \'ASC\'
        ), 
        array(
            \'meta_key\' => \'post_date\',
            \'meta_value\' => date(\'Y-m-d\'),
            \'meta_compare\' => \'<\'
        )
    )
);

4 个回复

最合适的回答,由SO网友:Jan Fabry 整理而成

If you can explain it in SQL, you can query for it! There are three places where we want to change the default query:

SELECT wp_posts.*
FROM wp_posts 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE 1=1
    AND wp_posts.post_type = \'post\'
    AND (wp_posts.post_status = \'publish\')
    AND wp_postmeta.meta_key = \'startDate\'
    AND CAST(wp_postmeta.meta_value AS CHAR) < \'2011-03-23\'
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value DESC
LIMIT 0, 10

The join should be a left join
The where-clause
The order

The join and the where-clause are added via the _get_meta_sql() function. The output is filtered, so we can hook into it:

add_filter( \'get_meta_sql\', \'wpse12814_get_meta_sql\' );
function wpse12814_get_meta_sql( $meta_sql )
{
    // Move the `meta_key` comparison in the join so it can handle posts without this meta_key
    $meta_sql[\'join\'] = " LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = \'startDate\') ";
    $meta_sql[\'where\'] = " AND (wp_postmeta.meta_value IS NULL OR wp_postmeta.meta_value < \'" . date(\'Y-m-d\') . "\')";
    return $meta_sql;
}

The order clause is filtered through posts_orderby:

add_filter( \'posts_orderby\', \'wpse12814_posts_orderby\' );
function wpse12814_posts_orderby( $orderby )
{
    $orderby = \'COALESCE(wp_postmeta.meta_value, wp_posts.post_date) ASC\';
    return $orderby;
}

This gives us the following SQL query:

SELECT wp_posts.*
FROM wp_posts
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = \'startDate\')
WHERE 1=1
    AND wp_posts.post_type = \'post\'
    AND (wp_posts.post_status = \'publish\')
    AND (wp_postmeta.meta_value IS NULL OR wp_postmeta.meta_value < \'2011-03-23\')
GROUP BY wp_posts.ID
ORDER BY COALESCE(wp_postmeta.meta_value, wp_posts.post_date) ASC
LIMIT 0, 10

Remember to unhook the filters after you did your query, otherwise you will mess up other queries too. And if possible you should not call query_posts() yourself, but modify the main post query that is done by WordPress while setting up the page.

SO网友:Sander Schat

为了寻找同样的问题，我来到了这一页。受到简·法布里回答的启发，我加入了他的解决方案。由于filter\\u命名问题，无法工作。因此，我将在此处发布我的更新版本，供其他搜索者使用：

    add_filter(\'posts_join_paged\', \'pp_sql_join_meta_publication_revision_date\');
    add_filter(\'posts_where_paged\', \'pp_sql_where_meta_publication_revision_date\');
    add_filter(\'posts_orderby\', \'pp_sql_orderby_meta_publication_revision_date\');

    $posts = \\Timber::get_posts($args);

    remove_filter(\'posts_join_paged\', \'pp_sql_join_meta_publication_revision_date\');
    remove_filter(\'posts_where_paged\', \'pp_sql_where_meta_publication_revision_date\');
    remove_filter(\'posts_orderby\', \'pp_sql_orderby_meta_publication_revision_date\');

这里是过滤器本身：

function pp_sql_join_meta_publication_revision_date( $meta_sql ){
    $meta_sql = " LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = \'publication_revision_date\') ";
    return $meta_sql;
}

function pp_sql_where_meta_publication_revision_date( $meta_sql )
{
    $meta_sql .= " AND (wp_postmeta.meta_value IS NULL OR wp_postmeta.meta_value IS NOT NULL OR wp_postmeta.meta_value = \'\')";
    return $meta_sql;
}

function pp_sql_orderby_meta_publication_revision_date( $orderby )
{
    $orderby = \'COALESCE(IF(wp_postmeta.meta_value IS NULL or wp_postmeta.meta_value = "", null, wp_postmeta.meta_value), DATE_FORMAT(wp_posts.post_date, "%Y%m%d")) DESC\';
    return $orderby;
}

SO网友:Alex Older

尝试以下内容：

$postedtime = get_post_meta($post->ID, \'startDate\');

if($postedtime != null){
$orderby = $postedtime;

}else{
$orderby = \'date\';
}

SO网友:Otto

查询POST调用只进行一个查询，而不是两个查询。因此，不能让它进行两个单独的查询，然后连接结果。

记住，你要在这里选择一些帖子，然后显示它们。一次选中该集。如果您想获得两组独立的帖子，然后将它们合并，那么您必须自己使用get\\u帖子或类似的东西。

结束

小码农CODE

Order by meta value or date?

相关推荐