Conditional custom field query

Question

I'm working on a job board-type site, and the client want to sell featured posts. They want the featured posts to expire from showing up in the "featured" section after a predetermined period of time, but continue to show up in the rest of the site.

My solution to this problem would be to assign a custom field to posts in the "featured" category stating the number of days it's to be featured for, say, 15. So the logic of the query would be something like: IF the post is category "featured" AND the custom field "featuredexpiredays" IS LESS THAN days since publication, get the post.

I have a script for calculating the number of days since publication, I'm just failing completely at figuring out how to glom all this together into a single query.

Do you have any suggestions on how to do this, or alternately, suggestions of a different way make my site behave in this way? Thanks so much!

Answer 1

I found this: http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query
I have modified the date to retrieve the last 15 days. It's untested, but should work.

$expireDays = '15';

$querystr = "
    SELECT $wpdb->posts.* 
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_date >= ( CURDATE() - INTERVAL $expireDays DAY )
    ORDER BY $wpdb->posts.post_date DESC
 ";

$pageposts = $wpdb->get_results($querystr, OBJECT);

Answer 2

It's maybe easier to set an expiration date rather than period, then you can do a meta query for posts with expiration dates greater than today:

$args = array(
            'posts_per_page' => -1,
            'meta_key' => 'expiration_date',
            'meta_value' => date('Y-m-d'),
            'meta_compare' => '>=',
            'orderby' => 'meta_value',
            'order' => 'ASC'            
);
$posts = new WP_Query($args);