Support

Account

Home Forums Front-end Issues Filtering / Querying Posts based on year (field stored yyyymmdd)

Solved

Filtering / Querying Posts based on year (field stored yyyymmdd)

  • I have a post type called shows with a field called show_date, storing the date like yyyymmdd in the database. Is it possible to query / filter posts by the first four characters of the show_date string (i.e. the year)?

    Ultimately, I want to be able to use the REST API to query / filter with this specific meta, but I am stuck figuring out how to do this. Here’s what I’ve been able to put together so far – this is my custom post type archive, which initially should pull all of the posts, but as I said, eventually I’d like to filter/query by year without having to have a separate year field or something.

    <select id="years" name="yearFilter">
      <option>Select Year</option>
    </select>
    
    <?php 
      $posts_per_page = -1;
      // set up our archive arguments
    
      $archive_args = array (
        'post_type' => 'setlist-shows',
        'posts_per_page' => $posts_per_page,
        'meta_key'  => 'show_date',
        //'orderby' => 'meta_value_num',
        //'order'   => 'DESC'
      );
    
      $archive_query = new WP_Query( $archive_args );
    
    ?>
    
    <div class="all-shows">
      <table>
        <tr>
          <th>Date</th>
          <th>Location</th>
          <th>Venue</th>
          <th></th>
        </tr>
        <?php while ( $archive_query->have_posts() ) : $archive_query->the_post(); 
    
          $date = get_field('show_date', false, false);
          $date = new DateTime($date);?>
    
        <tr>
          <td><?php echo $date->format('M j, Y');?></td>
          <td><?php the_field('location');?></td>
          <td><?php the_field('venue');?>
          <td><a href="<?php the_permalink();?>">Setlist</a></td>
        </tr>
      <?php endwhile; // end the custom loop ?>
      </table>
    </div>
    <?php wp_reset_postdata(); // always reset post data after a custom query ?>
    
    </main>
    
    <script>
    jQuery(document).ready(function($){
      $.get("/wp-json/wp/v2/setlist-shows", function (posts) {
        $.each(posts, function(index, post) {
          showDate = post.show_date;
          showYear = showDate.substring(0,4);
          var years = '<option value="' + showYear + '">' + showYear + '</option>';     
          $('#years').append(years);
        });
      });
    });
    </script>
  • This is the only way I can think to do it

    
      $archive_args = array (
        'post_type' => 'setlist-shows',
        'posts_per_page' => $posts_per_page,
        'meta_key'  => 'show_date',
        'orderby' => 'meta_value_num',
        'order'   => 'DESC',
        'meta_query => array(
          array(
            'key' => show_date,
            'value' => '20170000',
            'compare' => '>'
          ),
          array(
            'key' => show_date,
            'value' => '20180000',
            'compare' => '<'
          )
        )
      );
    
  • It’s so obvious, that makes perfect sense haha. Clearly I have been working on this specific template for too long – thanks John!

Viewing 3 posts - 1 through 3 (of 3 total)

The topic ‘Filtering / Querying Posts based on year (field stored yyyymmdd)’ is closed to new replies.