Filter post listing by meta value which is a date

1 comment

1. Add a query variable to store the month

   So first of all it’ll be necessary to create a custom query variable – this will store the month we’re after (in 2012/6) format. I’ll call it custom_month, but its best practise to prefix it to avoid clashes with other plug-ins:

   add_filter('query_vars', 'wpse57344_register_query_vars' );
   function wpse57344_register_query_vars( $qvars ){
       //Add these query variables
       $qvars[] = 'custom_month';
       return $qvars;
   }
   

   Add the drop-down

   The query variable will now be recognised and any given value stored by WordPress in the main query (global $wp_query).

   Next we create the drop-down to go at the top of the post admin table (this can be changed to be any post type).

   add_action( 'restrict_manage_posts', 'wpse57344_restrict_posts_by_metavalue' );
   function wpse57344_restrict_posts_by_metavalue() {
       global $typenow;
       $months = wpse57344_get_months();
       if ($typenow == 'post') {
           $selected = get_query_var('custom_month');
           $output = "<select style='width:150px' name='custom_month' class='postform'>n";
           $output .= '<option '.selected($selected,0,false).' value="">'.__('Show All','wpse57344_plugin').'</option>';
           if ( ! empty( $months ) ) {
               foreach ($months as $month):
                   $value =esc_attr($month->year.'/'.$month->month);
                   $month_dt = new DateTime($month->year.'-'.$month->month.'-01');
                   $output .= "<option value='{$value}' ".selected($selected,$value,false).'>'.$month_dt->format('F Y').'</option>';
               endforeach; 
           }
           $output .= "</select>n";       
       echo $output;
       }
   }
   

   The above gets an array of months a month object consists of:

   year => //the year of the month e.g. 2012
   month => //the number of the month, e.g. 6 for July
   posts => //number of posts with the date meta value in this month
   

   Obviously wpse57344_get_months() doesn’t exist natively – we’ll construct it later.

   Assuming with an array of months, we create the drop-down with each option having the value of the form yyyy/mm. I’ve given the form the same name as the query variable we added.

   Altering the query

   When a month in the drop-down is selected, the month in yyyy/mm is posted as the value for custom_month. Because we’ve registered this variable name with WordPress we can access it through $query->get('custom_month').

   So we check if its empty or not – if its not, then we restrict to posts where their meta value data is in that month. To do that we use a meta query and the BETWEEN operator.

   add_action( 'pre_get_posts', 'wpse57351_pre_get_posts' );
   function wpse57351_pre_get_posts( $query ) {
   
       //Only alter query if custom variable is set.
       $month_str = $query->get('custom_month');
       if( !empty($month_str) ){
   
               //For debugging, uncomment following line
               //var_dump($query);
   
           //Be careful not override any existing meta queries.
           $meta_query = $query->get('meta_query');
           if( empty($meta_query) )
               $meta_query = array();
   
           //Convert 2012/05 into a datetime object get the first and last days of that month in yyyy/mm/dd format
           $month = new DateTime($month_str.'/01');
   
           //Get posts with date between the first and last of given month
           $meta_query[] = array(
               'key' => 'customdate',
               'value' => array($month->format('Y/m/d'),$month->format('Y/m/t')),
               'compare' => 'BETWEEN',
           );
           $query->set('meta_query',$meta_query);
   
               //For debugging, uncomment following line
               //var_dump($query);
       }
   }
   

   Get the months

   It remains then to define th function wpse57344_get_months(). We need to query the postmeta table and pick out distinct months from the dates in your posts’ meta. In the following I assume that your date is stored with key ‘customdate’ and has format (as indicated in comments) yyyy/mm/dd.

   function wpse57344_get_months(){
       global $wpdb;
           $months = wp_cache_get( 'wpse57344_months' );
           if ( false === $months ) {
               $query = "SELECT YEAR(meta_value) AS `year`, MONTH(meta_value) AS `month`, count(post_id) as posts 
                   FROM $wpdb->postmeta WHERE meta_key ='customdate'           
                   GROUP BY YEAR(meta_value), MONTH(meta_value) ORDER BY meta_value DESC";
               $months = $wpdb->get_results($query);
               wp_cache_set( 'wpse57344_months', $months );
           }
           return $months;
   }
   

   This returns an array of month objects in the desired format.

   Log in to Reply