query posts - Order by meta value or date?

admin管理员组

文章数量:1314557

Got a custom field called startDate but its only on a few events. I was wondering if it isn't set for a post I could use post_date to generate the posts list?

// 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' => '<'
        )
    )
);

Got a custom field called startDate but its only on a few events. I was wondering if it isn't set for a post I could use post_date to generate the posts list?

// 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' => '<'
        )
    )
);

• query-posts
• post-meta
• date-time

Share Improve this question edited May 10, 2011 at 15:06 Jan Fabry 30.5k44 gold badges9090 silver badges136136 bronze badges asked Mar 23, 2011 at 12:40 v3ntv3nt 1,68977 gold badges3636 silver badges5454 bronze badges 7

• is post_date a custom field? – Bainternet Commented Mar 23, 2011 at 13:29
• i ws presuming its the default wordpress published field though may be wrong? Either way id like to use the default date... – v3nt Commented Mar 23, 2011 at 13:36
• ok so its not a meta field its in the posts table – Bainternet Commented Mar 23, 2011 at 13:52
• Fixed your query arguments, hope that hasn't skewed what you were illustrating at all, please feel free to revert if necessary. – t31os Commented Mar 23, 2011 at 14:42
• cheers t31os - edited it again to make it clearer. Need it to select content older than NOW using startDate, and if startDate hasn't been set use default posts' date post_date. – v3nt Commented Mar 23, 2011 at 17:15

 |  Show 2 more comments

4 Answers 4

Sorted by: Reset to default 12

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.

In search of the same problem i came to this page. And was inspired by the answer from @jan Fabry, i added his solution. Didnt work, due to filter_naming issues. So i will post my updated version here, for other seekers:

    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');

and here the filters themselves:

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;
}

try something along the lines of:

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

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

}else{
$orderby = 'date';
}

A query posts call makes only one query, not two. So no, you can't have it make two separate queries and then concatenate the results.

Remember, you're selecting some set of posts here, then displaying them. That set is selected all at once. If you want to get two separate sets of posts and then merge them, then that's something you'll have to do yourself with get_posts or similar.

本文标签： query postsOrder by meta value or date