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.

I have a custom field called expiry_date with a value of let's say 2010-12-11 00:00:00.

What I would like to do is, in my query loop, only return posts with this expiry_date custom field attached to posts and the following condition:

  • The date in the expiry_date custom field has to be within 24 hours of expiring, if it past the current date, the post is 'expired' and will not show in the loop or if the post is within 25 hours of expiring, it will also not show etc.

Any idea at all please

share|improve this question

2 Answers 2

I agree with Denis, I don't like using custom fields for this kind of stuff, but I'm not aware of any other way yet...

What you what can be done by altering the SQL query for the loop, something like this:

add_filter('posts_join', 'my_post_filter_join');
function my_post_filter_join($join) {
  global $wpdb;
  if(is_home() || is_category() || is_search())
    $join .= " LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)";
  return $join;
}

add_filter('posts_where', 'my_post_filter_where');
function my_post_filter_where($where) {
  global $wpdb;

  // current date in unix format
  $current_time = current_time("timestamp");

  // exclude any posts older than your expiry date set in the custom field
  if(is_home()|| is_category() || is_search())
    $where .= " AND ($wpdb->postmeta.meta_key = 'expiry_date' AND $wpdb->postmeta.meta_value > '{$current_time}')";
  return $where;
}

I used unix timestamps here instead of a formatted date string, you should do that too, it's easier to handle. For the admin interface you can use a datepicker and convert the date to a timestamp when the post is saved...

share|improve this answer

You're looking for a BETWEEN clause, which isn't supported off the top of my head.

You might get around this by using two meta clauses, one >= and the other <=, both on the same meta key. WP is likely to join the meta table twice, which isn't great for performance, but it'll work.

Alternatively, use the posts_join and the posts_where filters.

Oh, and... in case it needs reminding, your queries are going to be slow if you've any significant amount of data, due to the lack of index on the meta value. I've no idea of what your use case might be about, but it sounds like one where an extra field to the originating table is the proper way to go.

share|improve this answer
    
I don't think it is possible to query for two conditions on same meta key (unless something changed?). As far as I remember Codex suggests using those filters as only way to get yourself date range. –  Rarst Dec 10 '10 at 18:01
    
@Rast: Not that I'm aware of. But I'm suspecting that WP will allow something like posts join meta as meta1 join meta as meta2 where meta1.val >= val1 and meta2.val <= val2. At worst the question's author will end up storing the same meta twice, under two keys, to force WP into the above setup. –  Denis de Bernardy Dec 10 '10 at 18:09
1  
Also, I'm highly suspicious at the whole idea of using a meta for this. But that's me beating a dead horse. ;-) –  Denis de Bernardy Dec 10 '10 at 18:10
    
@Denis, the date is automatically put into custom fields from a data feed, so it's what I have to work with... –  Keith Donegan Dec 16 '10 at 22:35
    
@Keith: I still think you should be using Doctrine or Propel alongside Symfony or another php framework. For what you're doing WP is only going to translate into problems in the long run... –  Denis de Bernardy Dec 17 '10 at 1:06

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.