10

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' => '<'
        )
    )
);
7
  • is post_date a custom field?
    – Bainternet
    Mar 23 '11 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
    Mar 23 '11 at 13:36
  • ok so its not a meta field its in the posts table
    – Bainternet
    Mar 23 '11 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
    Mar 23 '11 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
    Mar 23 '11 at 17:15
11

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.

6
  • 2
    Very elegant solution, I wouldn't have thought of using COALESCE like that. I would just advise not assuming the default 'wp_' prefix and using {$wpdb->prefix} instead... Mar 23 '11 at 23:54
  • @goldenapples: Yes, you could generalize it, but it is already so specific for this query (it will mess up other queries with a meta part), that I thought this was not necessary.
    – Jan Fabry
    Mar 24 '11 at 8:28
  • Thanks Jan - that is an eye opener! Still getting to grips with wordpress and wondering where this is called on my page? And how would i 'un-hook' it? ie // $theQuery... then <?php if (have_posts()) : while (have_posts()) : the_post(); ?> ?
    – v3nt
    Mar 24 '11 at 11:53
  • @daniel: You can place the functions in your theme's functions.php file. Then right before you execute the query you place the two add_filter() lines. After the query you write remove_filter( 'get_meta_sql', 'wpse12814_get_meta_sql' ); remove_filter( 'posts_orderby', 'wpse12814_posts_orderby' ); to remove them again.
    – Jan Fabry
    Mar 24 '11 at 12:17
  • ah - that's all making sense now and working too! Many thanks Jan. Thats gonna be useful...
    – v3nt
    Mar 24 '11 at 17:17
1

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;
}
3
  • What were the filter naming issues with Jan's answer?
    – Rup
    Nov 24 '20 at 15:49
  • You should also probably get the table names from $wpdb->postmeta in case the prefix has changed or this is a multisite.
    – Rup
    Nov 24 '20 at 15:51
  • @Rup, yes, the 'get_meta_sql' was not triggered in my query here. I guess because my args for WP_Query were the basic 'post_type' and 'post_status'. And yes, you are right about the $wpdb. I did do that here, it was just a continuation of the code above. Nov 24 '20 at 16:08
0

try something along the lines of:

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

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

}else{
$orderby = 'date';
}
2
  • thankx alex but not sure how this gets involved the the loop?
    – v3nt
    Mar 23 '11 at 17:14
  • doh! when you query_posts(array('orderby' => $orderby))
    – Alex Older
    Mar 24 '11 at 10:17
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.