WordPress WP_Query compare only Month part of meta_query key

3 comments

1. Here are few additional options:

   1. If you want to filter a given month in a given year, you can just calculate the timestamp period for that month and use the corresponding compare operators of the meta query to filter it.

   2. You might want to consider changing the format of the wpcf-product-start_date custom field values, to Y-m-d, Y-m or m, depending of your needs.

   3. You could use the {added,updated,deleted}_post_meta hooks (see for example this fine answer) to automatically save a new custom field, containing the month number calculated from the wpcf-product-start_date timestamp. For example:

      add_action( 'added_post_meta',   'b2e_update_post_meta', 10, 4 );
      add_action( 'updated_post_meta', 'b2e_update_post_meta', 10, 4 );
      
      function b2e_update_post_meta( $meta_id, $post_id, $meta_key, $meta_value )
      {
          if ( 'wpcf-product-start-date' === $meta_key )
          {
              // avoid infinite loop
             remove_action( current_filter(), __FUNCTION__ );            
      
             // add/update the corresponding month value
             update_post_meta( $post_id, 
                               'wpcf-product-start-month', 
                               date( 'n', $meta_value ) 
             );
          }
      }
      
      add_action( 'deleted_post_meta', 'b2e_delete_post_meta', 10, 4 );
      
      function b2e_delete_post_meta( $deleted_meta_ids, $post_id, $meta_key,
                                     $only_delete_these_meta_values )
      {
          if ( 'wpcf-product-start-date' === $meta_key )
          {
              // avoid infinite loop
              remove_action( current_filter(), __FUNCTION__ );            
      
             // delete the corresponding month key        
              delete_post_meta( $post_id, 'wpcf-product-start-month' );
          }
      }
      

      Then you can use wpcf-product-start-month in your meta-query.

      Notice that I used wpcf-product-start-date here. Somehow this didn’t triggered for the custom field with the meta-key wpcf-product-start_date. But let me know if you have problems with this.

   4. If you are adventurous you can play with the posts_where filter:

      $args = array(
          'posts_per_page' => -1,
          'post_type'      => 'product',
          'meta_query'     => array(
              'relation'    => 'AND',
              array(
                  'key'     => 'wpcf-product_status',
                  'value'   => 'valid',
                  'compare' => '='
              ),
              array(
                  'key'     => 'wpcf-product-start_date',
                  'compare' => '=',
                  'value'   => 'replace_me',
              ),
          ),
      );
      
      add_filter( 'posts_where', 'b2e_posts_where' );
      $the_query = new WP_Query( $args );
      remove_filter( 'posts_where', 'b2e_posts_where' );
      

      where

      function b2e_posts_where( $where )
      {
          $month = 3; // Edit this value
      
          if( FALSE !== stripos( $where, 'replace_me' ) )
          {
              $from  = "CAST(mt1.meta_value AS CHAR) = 'replace_me'";
              $to    = "MONTH( FROM_UNIXTIME( 1 * mt1.meta_value ) ) = $month 
                        AND YEAR( FROM_UNIXTIME( 1 * mt1.meta_value ) ) > 1970 ";
              $where = str_ireplace( $from, $to, $where );
          }
          return $where;
      }
      

      Notice that this assumes that the replace_me value is in the second meta query array item.
      The table names can change if you reorder the array items (e.g. wp_postmeta, mt1, ... ). It’s possible to find the
      correct table name automatically, but that’s beyond the scope of this answer.

   Hope this help.

   Log in to Reply

2. Try this. And be sure that ‘wpcf-product-start_date’ has got the same format of ‘date(‘m’)’ 🙂

   $m = date('m');
   $args = array(
       'numberposts' => -1,
       'post_type' => 'product',
   
       'meta_query' => array(
               'Relation' => 'AND',
       array(
               'key'     => 'wpcf-product_status',
               'value'   => 'valid',
               'compare' => '='
           ),
       array(
               'key'     => 'wpcf-product-start_date',
               'compare' => '=',
               'value'   => '$m'    
           ),
       ),
       ),
   );
   

   Log in to Reply

3. OR…

   You can get the month start and month end as a timestamp and use a ‘BETWEEN’ meta compare:

   eg.

   $year = date('Y'); $month = date('m')
   $days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
   $month_start = strtotime($year .  '-' . $month . '-1 00:00:00');
   $month_end = strtotime($year .  '-' . $month . '-' . $days_in_month . ' 23:59:59');
   
   $args = array(
       'numberposts' => -1,
       'post_type' => 'product',
       'meta_query' => array(
           'key' => 'wpcf-product-start_date',
           'value' => array(
                $month_start,
                $month_end
           ),
           'compare' => 'BETWEEN'
       )
   );
   

   or…

   $args = array(
       'numberposts' => -1,
       'post_type' => 'product',
       'meta_query' => array (
           'relation' => 'AND',
           array(
               'key' => 'wpcf-product-start_date',
               'value' => $month_start,
               'compare' => '>='
           ),
           array(
               'key' => 'wpcf-product-start_date',
               'value' => $month_end,
               'compare' => '<='
           )
       )
   )
   

   Log in to Reply