Query posts by custom date field using archive.php

  • Resolved gran3

    (@gran3)


    10 years ago

    Dear all,

    I am working on a very simple and straight forward event website. I use the normal posts to create ‘events’ and use the Advanced Custom Field plugin to add two date fields, one start_date (where the event begins) one end_date (the date the event ends) in case the event is only one day long, I use only the end_date field.

    The home page should filter all the upcoming posts for the current month (this would be a custom loop in the index using the current date), and has a simple select dropdown menu to select a day, month and year, and a ‘Go’ button

    My assumption would be that if the archive.php file can do this trough changing the url like,
    website.com/2014 (all the posts written in 2014)
    website.com/2014/11 (all the posts written in november of 2014)
    website.com/2014/11/26 (all the posts written on the 26th of November 2014)

    I could have these urls show the posts using the end_date custom field instead of the post publish date with the addition of showing the posts on days between the start_date and end_date.

    @robbiegod has posted a very similar question here.
    His question is more focused on getting the multiple day events span all the archive pages (not just the day the event ends but all in between), which is something I would like to integrate as well. But how to generally set this up is unclear to me.

    My current archive.php file

    <?php add_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );

$args = array(
	'post_type' => 'post',
	'posts_per_page' => 10,
	'meta_key' => 'end_date',
	'orderby' => 'meta_value_num',
	'order' => 'ASC',
	'meta_query' => array(
    'relation' => 'AND',
        array(
            'key'       => 'start_date',
            'compare'   => '>=',
            'value'     => $start_date,
            'type'      => 'DATE'
        ),
        array(
            'key'       => 'end_date',
            'compare'   => '<=',
            'value'     => $end_date,
            'type'      => 'DATE'
        )
    ),
);

$posts = new WP_Query( $args );

remove_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );

if( $posts->have_posts() ) :  while ( $posts->have_posts() ) : $posts->the_post(); ?>

    and the function that goes with it from @keesiemeijer

    // sortable posts by custom date field for long period events
function get_meta_sql_date( $pieces, $queries ) {
    global $wpdb;

    // get start and end date from query
    foreach ( $queries as $q ) {

        if ( !isset( $q['key'] ) ) {
            return $pieces;
        }

        if ( 'start_date' === $q['key'] ) {
            $start_date = isset( $q['value'] ) ?  $q['value'] : '';
        }
        if ( 'end_date' === $q['key'] ) {
            $end_date = isset( $q['value'] ) ?  $q['value'] : '';
        }
    }

    if ( ( '' === $start_date ) || ( '' === $end_date ) ) {
        return $pieces;
    }

    $query = "";

    // after start date AND before end date
    $_query = " AND (
        ( $wpdb->postmeta.meta_key = 'start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s) )
        AND ( mt1.meta_key = 'end_date' AND ( CAST(mt1.meta_value AS DATE) <= %s) )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date );

    // OR before start date AND after end end date
    $_query = " OR (
        ( $wpdb->postmeta.meta_key = 'start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
        AND ( mt1.meta_key = 'end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s) )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date );

    // OR before start date AND (before end date AND end date after start date)
    $_query = " OR (
        ( $wpdb->postmeta.meta_key = 'start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
        AND ( mt1.meta_key = 'end_date'
            AND ( CAST(mt1.meta_value AS DATE) <= %s )
            AND ( CAST(mt1.meta_value AS DATE) >= %s )
        )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date, $start_date );

    // OR after end date AND (after start date AND start date before end date) )
    $_query = "OR (
        ( mt1.meta_key = 'end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s ) )
        AND ( $wpdb->postmeta.meta_key = 'start_date'
            AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s )
            AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s )
        )
    )";
    $query .= $wpdb->prepare( $_query, $end_date, $start_date, $end_date );

    $pieces['where'] = $query;

    return $pieces;
}

    Anny point in the right direction would be very much appreciated.
    You can find my test site here.

Viewing 8 replies - 16 through 23 (of 23 total)
  • Thread Starter gran3

    (@gran3)

    Doesn’t seem to change much. Still both languages are being shown;

    Moderator keesiemeijer

    (@keesiemeijer)

    Both posts are shown because they have the same event date meta. I’m not familiar with the plugin. And not sure how to filter the posts for one language. What query are you using on the main page?

    Can you check what get_locale(); returns.
    Change this:

    $locale = get_locale();

    to this:

    $locale = get_locale();
echo $locale;

    Thread Starter gran3

    (@gran3)

    Hey Keesiemeijer,

    get_locale prints out fr_FR and nl_NL as expected.
    Also $query->set( ‘lang’, ‘nl’ ); should be the correct way of doing it but somehow it doesn’t query correctly,

    when I try

    if($locale === 'fr_FR') {
	$query->set( 'lang', 'fr' );
	echo $locale;
}

if($locale ==='nl_NL') {
	$query->set( 'lang', 'nl' );
	echo $locale;
}

    I get the error Cannot modify header information – headers already sent
    But when the echo is only in one of the if statements it works fine.

    The query on the main page is:

    <?php
$today = date('Ymd');

$args = array(
	'post_type' => 'post',
	'posts_per_page' => 10,
	'meta_key' => 'event_end_date',
	'orderby' => 'meta_value_num',
	'order' => 'ASC',
	'meta_query' => array(
	     array(
	        'key'		=> 'event_end_date',
	        'compare'	=> '>=',
	        'value'		=> $today,
	    )
    ),
);

$posts = new WP_Query( $args );

if( $posts->have_posts() ) : while ( $posts->have_posts() ) : $posts->the_post(); ?>

    This compares the end date to the current date, and shows all events which are not yet expired.

    Moderator keesiemeijer

    (@keesiemeijer)

    Ok, I think I’ve got it. Try it with this in your theme’s functions.php: https://pastebin.com/BmtEnVnn

    Thread Starter gran3

    (@gran3)

    Hey Keesiemeijer!

    You did it! All works like a charm now!

    Small recap of what it is doing for future searches.
    Posts use custom start and end date field to sort the archives instead of the standard publishing date.

    @keesiemeijer, could you follow me back on twitter? @modem_studio, then I can donate you something for your work.

    Moderator keesiemeijer

    (@keesiemeijer)

    There’s no need for a donation. I like these kind of puzzles.
    Have a drink on me ??

    With the new code you can also query for dates with WP_Query or get_posts() in your theme tempate files.

    Use the parameters ‘meta_archive_start_date’ and ‘meta_archive_end_date’.

    Here are some examples:

    // Get posts in the month december
$args = array(
	'meta_archive_start_date' => '201412',
	'meta_archive_end_date'   => '201412',
);
$archive_posts = get_posts( $args );

// Get posts in 2015
$args = array(
	'meta_archive_start_date' => '2015',
	'meta_archive_end_date'   => '2015',
);
$archive_posts = new WP_Query( $args );

    Thread Starter gran3

    (@gran3)

    Thats great! Thanks a lot,
    If you’re ever in Berlin, drop me a line, Ill buy u a beer ??

    Best!

    Pete

    (@perthmetro)

    And that, ladies and gentlemen, is what WordPress is all about ??

Viewing 8 replies - 16 through 23 (of 23 total)
  • The topic ‘Query posts by custom date field using archive.php’ is closed to new replies.