Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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' => '<'
        )
    )
);
share|improve this question
    
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... –  daniel Crabbe 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. –  daniel Crabbe Mar 23 '11 at 17:15
show 2 more comments

3 Answers

up vote 10 down vote accepted

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.

share|improve this answer
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... –  goldenapples 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(); ?> ? –  daniel Crabbe 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... –  daniel Crabbe Mar 24 '11 at 17:17
show 1 more comment

try something along the lines of:

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

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

}else{
$orderby = 'date';
}
share|improve this answer
    
thankx alex but not sure how this gets involved the the loop? –  daniel Crabbe Mar 23 '11 at 17:14
    
doh! when you query_posts(array('orderby' => $orderby)) –  Alex Older Mar 24 '11 at 10:17
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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