WordPress.org

Forums

[resolved] Query custom post types by two custom date fields (27 posts)

  1. robbiegod
    Member
    Posted 11 months ago #

    Recently updating wordpress and now I am trying to do accomplish something that is giving me a real headache. I was using an event calendar plugin, but now i am trying to remove that plugin and create my own events system. I'm getting hung up querying the posts by a date range.

    I registered a custom post type and then used ACF (Advanced Custom Fields) to add two Date Picker fields. One field is the Event Start Date and the other is the Event End Date. Occasionally, an event will span for 3 months and it is these types of events that I am having the most problems with.

    I have an area on my webpage that shows the events filtered by month and year. Let's call this my Event List Widget (it's not a wordpress widget). This is where I think things are getting tripped up because if I have an event that starts in May and ends in July and I am on the June 2014 section, that event won't show up.

    I'm looking for a function/query - combo that will let me first filter by the month I am in, but be flexible enough to grab those events that span multiple months.

    I've tried a few things, which I will list below. I've tested all of these queries and none of them seem to be able to treat my dates like a true date range.

    It's suggested that maybe i need an additional field that I can update when the post is submitted to tell wordpress which months to show the event in. I would query that field instead of my date fields. I'm trying to avoid doing that because I'd like to make this system more legit.

    Let me know if you all have some suggestions for me on what i can do.

    Here is what i have already tried (if needed i can provide a link to the website via PM):

    $all_events = array (
    'post_type' => 'events',
        'posts_per_page' => 50,
        'meta_query' => array(
        array(
            'key'       => 'event_start_date',
            'compare'   => '>=',
            'value'     => $startday,
        ),
         array(
            'key'       => 'event_end_date',
            'compare'   => '<=',
            'value'     => $endday,
        )
        ),
    );

    The above query almost works. It will display the events if the start and end date are within the same month, but I don't see the events that end in another month or two months from now.

    $querystr = "
        SELECT *
        FROM $wpdb->posts wposts, $wpdb->postmeta metastart, $wpdb->postmeta metaend
        WHERE (wposts.ID = metastart.post_id AND wposts.ID = metaend.post_id)
            AND (metastart.meta_key = 'event_start_date' AND metastart.meta_value > $startday )
            AND (metaend.meta_key = 'event_end_date' AND metaend.meta_value <= $endday )
            AND wposts.post_type = 'events'
        AND wposts.post_status = 'publish'
        ORDER BY metastart.meta_value ASC
     ";

    The above query does the same thing as the previous one. What do you think I am missing here?

    NOTE: This was related to this post I made a few months ago. Now getting around to replacing the functionality.

    http://wordpress.org/support/topic/upgrading-to-wordpress-and-the-events-calendar-break-my-monthyear-filter?replies=3

  2. keesiemeijer
    moderator
    Posted 11 months ago #

  3. robbiegod
    Member
    Posted 11 months ago #

    Do you think that solution will work for me if I have a start date field and end date field? I noticed that query was using $today variable so the query is just using one date to compare by. I'm actually trying to compare two dates and create a range between them.

  4. robbiegod
    Member
    Posted 11 months ago #

    Hi keesiemeijer - thanks for the reply...I still can't get this to work exactly. Turning out to be quite complicated.

    The code in my page:

    if (isset($_GET['_m'])) {
    
    	$current_month = str_pad($_GET['_m'], 2, '0', STR_PAD_LEFT);
    	$current_day = "01"; // day one
    	$current_year = $_GET['_y'];
    
    	$get_last_day = $current_year.$current_month.$current_day;
    	$lastday = date("t", strtotime($get_last_day));
    
    	$tempstartday = $current_year.$current_month.$current_day;
    	$tempendday = $current_year.$current_month.$lastday;
    
    	$startday = date('Ymd', strtotime($tempstartday));
    	$endday = date('Ymd', strtotime($tempendday));
    
    } else {
    
    	$current_month = str_pad(date('d'), 2, '0', STR_PAD_LEFT);
    	$current_day = "01"; // day one
    	$current_year = date('Y');
    
    	$get_last_day = $current_year.$current_month.$current_day;
    	$lastday = date("t", strtotime($get_last_day));
    
    	$tempstartday = $current_year.$current_month.$current_day;
    	$tempendday = $current_year.$current_month.$lastday;
    
    	$startday = date('Ymd', strtotime($tempstartday));
    	$endday = date('Ymd', strtotime($tempendday));
    
    }
    
    add_filter( 'get_meta_sql', 'get_meta_sql_date' );
    
    $qryevents = array(
    	'post_type' => 'events',
    	'posts_per_page' => 50,
    	'status' => 'published',
    	'meta_key' => 'event_start_date',
    	'orderby' => 'meta_value',
    	'order' => 'ASC',
    
    	// produces meta join and where clauses for the query
    	// which will be filtered in functions.php
    	'meta_query' => array(
    		'relation' => 'OR',
    		array(
    			'key'     => 'event_start_date',
    			'value'   => $startday,
    			'compare' => '<='
    		),
    		array(
    			'key'     => 'event_end_date',
    			'value'   => $endday,
    			'compare' => '>='
    		),
    	)
    );
    
    remove_filter( 'get_meta_sql', 'get_meta_sql_date' );
    
    $loop = new WP_Query( $qryevents );

    I've tried multiple things here, but instead of just trying random things I figure i would ask you to see if you can see what the problem is?

    The function I put in my functions file:

    function get_meta_sql_date( $where ) {
    	global $wpdb;
    
    	$where = "
    		AND  ($wpdb->postmeta.meta_key = 'event_end_date'
    		AND CAST($wpdb->postmeta.meta_value AS DATE) >= '$endday')
    		OR   ($wpdb->postmeta.meta_key = 'event_start_date'
    		AND CAST($wpdb->postmeta.meta_value AS DATE) <= '$startday')
    	";
    	return $where;
    }

    I do use the Date Picker and the save format I use for the dates is yymmdd. Should I change that to something else?

    Here is the resulting query.

    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) WHERE 1=1 AND wp_posts.post_type = 'events' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'event_start_date' OR (mt1.meta_key = 'event_start_date' AND CAST(mt1.meta_value AS DATE) <= '20140401') OR (mt2.meta_key = 'event_end_date' AND CAST(mt2.meta_value AS DATE) >= '20140430') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC LIMIT 0, 50

    The results I get contain events that start and end in May and June. I am in the month of April in that widget i referred too before so I should only see events that start in April.

    --

    If an event starts in May and ends in June, I should not see it on the April page or in the July page, but i should see it on the May and June pages.

    I pass the month and year to the widget and some ajax to tell it which month and year to start the filter by. The widget has arrows in it that you can click to cycle through the months and years.

  5. robbiegod
    Member
    Posted 11 months ago #

    I came up with an ok solution for making the events appear. I added 12 checkboxes for each month and then i changed the query to look for the month in that field.

    This is not ideal, but i can't seem to get the range query to work.

    I would really like to get the date range to work because then this new system would be perfect and then i can get rid of one of the plugins.

    Does anyone have any additional suggestions?

  6. keesiemeijer
    moderator
    Posted 11 months ago #

    Maybe try the compare "BETWEEN". Remove the code from my privious suggestion and try it with this for your query

    $all_events = array (
    	'post_type' => 'events',
    	'posts_per_page' => 50,
    	'meta_key'       => 'event_start_date',
    	'orderby'        => 'meta_value',
    	'order'          => 'ASC',
    	'meta_query' => array(
    		array(
    			'key'       => 'event_start_date',
    			'value'     => array( $startday, $endday ),
    			'compare' => 'BETWEEN',
                            'type'    => 'DATE',
    		),
    	),
    );

    http://codex.wordpress.org/Function_Reference/WP_Query#Custom_Field_Parameters

  7. robbiegod
    Member
    Posted 11 months ago #

    Wouldn't the result be the same with that query? I'm pretty I tried that type of query too and I got the same result.

    But, i'll try it again and let you know how it goes.

  8. robbiegod
    Member
    Posted 11 months ago #

    The problem with the query above is the same as before, it won't return events that span multiple months. In the case of an event that starts in May and ends in July, the event won't show on the June page.

    Here is what I'm thinking I'll need to do.

    Yeah that query does the same thing. I'm thinking I'll need to write two queries for this or 1 query and a filter of some sort.

    Something like one primary query to get all of the events in the database and grab all of the start and end dates. Maybe put all of them into an array?

    Then another query to split the results into groups by month and year and display those results based on the query string i send it. Query string is month and year numeric values.

    The first part of that is easy enough, but how about the second part? I'm gonna investigate that now.

  9. robbiegod
    Member
    Posted 11 months ago #

    Kinda shifted gears a bit. I came up with an idea to store the range of dates between the two dates selected into a new custom field. I made a textarea custom field and wrote a function that grabs the range of the two dates and the saves it into this field.

    I'm storing it as a string like this:

    20140506,20140507,20140508,20140509

    This event starts on may 6 2014 and ends on may 9 2014.

    The way i imagined this working is I'd store the range in the textarea and then create another range via the code, not stored in the database, the new range would from first day of the month to the last day of the month. Then I was gonna send the string or array, whatever would work and do a meta query using compare "IN" to get any matches. This doesn't seem to be working.

    I make the range like this - the function takes the dates and then i convert the array it makes to a string.

    $aryDates = createDateRangeArray($startday, $endday);
    $date_str = implode(",", $aryDates);

    So, then i tried this:

    $all_events = array (
        'post_type' => 'events',
    		'posts_per_page' => 50,
    		'status' => 'published',
    		'meta_query' => array(
    		'relation' => 'OR',
    		array(
            'key' => 'auto_event_date_span',
            'value' => $date_str,
            'compare' => 'IN',
            'type' => 'NUMERIC'
        )
     )
    );

    Is there a way i can do what i describle with wp_query and meta_query OR do i need to write a custom query?

  10. keesiemeijer
    moderator
    Posted 11 months ago #

    Hi @robbiegod

    Sorry for my late reply. I'm not sure the best way to do this is with one query but I've come up with this.

    In functions.php:

    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 ( 'event_start_date' === $q['key'] ) {
    			$start_date = isset( $q['value'] ) ?  $q['value'] : '';
    		}
    		if ( 'event_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 = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s) )
      		AND ( mt1.meta_key = 'event_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 = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
      		AND ( mt1.meta_key = 'event_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 = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
      		AND ( mt1.meta_key = 'event_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 = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s ) )
    		AND ( $wpdb->postmeta.meta_key = 'event_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;
    }

    And in your theme query like this:

    <?php
    add_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
    $all_events = array (
    	'post_type' => 'events',
    	'posts_per_page' => 50,
    
    	// creates the meta sql join and where clauses
    	// which will be filtered in functions.php
    	// must be two meta_query arrays
    	'meta_query' => array(
    		'relation' => 'AND',
    		array(
    			'key'       => 'event_start_date',
    			'compare'   => '>=',
    			'value'     => '20140601',
    			'type'      => 'DATE'
    		),
    		array(
    			'key'       => 'event_end_date',
    			'compare'   => '<=',
    			'value'     => '20140630',
    			'type'      => 'DATE'
    		)
    	),
    );
    
    $date_query = new WP_Query( $all_events );
    remove_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
    ?>

    I've not tested this extensively, and I'm pretty sure there are better ways to do this.

  11. robbiegod
    Member
    Posted 11 months ago #

    Thanks for the reply. I'll try it out tonight and report back.

    For now, in my project, I've just reverted back to the event calendar plugin, but if I can ditch that plugin that will be a good thing. Its overkill for what we need.

    I'll report back here, thanks for your help. If it works I owe you a beer!

  12. robbiegod
    Member
    Posted 11 months ago #

    Awesome!!! Thank you keesiemeijer!

    I definitely owe you a beer! This works. My god, that was a tricky thing to do. I think maybe they should post this on the official Advanced Custom Fields page instead of the short solution they have.

    Your filter and the query work perfectly for me.

    Thanks again!

  13. keesiemeijer
    moderator
    Posted 11 months ago #

    You're welcome. I'm glad you've got it resolved :)

  14. Atomicsmash
    Member
    Posted 8 months ago #

    @keesiemeijer this is great! Thanks!

  15. gran3
    Member
    Posted 6 months ago #

    @robbiegod, thanks a lot for this question

    I've run into the same issue and came across your hunt for a solution on different sites :)

    Im trying to do exactly the same thing and was wondering if you could also share the 'Event List Widget' you refer to. Im looking for a way to sort out entries by year, month, day through a select dropdown, but without succes. What would the urls for these pages look like? The standard archive pages like http://www.website.com/2014/11 doesn't seem to get me far

    I don't use custom post so I have the query in my archive.php with following code:

    <?php
    add_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
    
    $args = array(
    	'post_type' => 'post',
    	'posts_per_page' => 4=10,
    	'meta_key' => 'end_date',
    	'orderby' => 'meta_value_num',
    	'order' => 'ASC',
    	'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'       => 'start_date',
            'compare'   => '>=',
            'value'     => '20140601',
            'type'      => 'DATE'
        ),
        array(
            'key'       => 'end_date',
            'compare'   => '<=',
            'value'     => '20140630',
            'type'      => 'DATE'
        )
    ),
    );
    
    $posts = new WP_Query( $args );
    
    remove_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
    
    if( $posts->have_posts() ) :
    ?>

    Also my guess is that the 'value' fields there should have some dynamic content instead of a fixed value. I'm trying to wrap my head around it but without much result :)

    Would it be possible to break down the code a bit for me including the filter method?

    I have a small test version on dev2.modem.ws

    Very much appreciated!

    All the best,

  16. robbiegod
    Member
    Posted 6 months ago #

    hi there gran3,

    Actually my Event List Widget is not a widget in terms of a wordpress widget. It was a combination of a wordpress query, jquery ajax call that sent the year and month to a wordpress page that would output the results and then refresh the results using ajax.

    I'll be around my computer where my files are located on Monday, I can try to help you out some more then.

    happy thanksgiving!

  17. gran3
    Member
    Posted 6 months ago #

    Hi Robbiegod,

    Thanks a lot, would be nice to see how thats done.

    A happy (late) thanksgiving to you to!

  18. robbiegod
    Member
    Posted 4 months ago #

    Sorry about the long delay in replying. I had totally forgotten about your question. So to maybe help guide you through what i did here are some tips.

    First thing i did was i made page called Events Widget (you can call it whatever you want.) I made a blank WordPress Template and removed the header and footer functions because the query inside of the page is just going to output a list of events and I'll load it in later with ajax. I have a default query where i build the start date as being "today" and then the end date is this month, this year, and the last day of this month. That's the way I get all of events that happen in this month.

    Then i applied the function we spoke about above to modify the query and output all of the events in the query that fit in the date range.

    Later I modified this same page by allowing the query strings to define the startday and endday. If _m and _y are defined then they are processed and the start day and end day are created based on the query string. So that's how i am changing the query.

    Then on the homepage I have an area where i am loading the events and i display them by month / year. There are two arrows to flip through the events by month.

    Here is all of that code:

    <?php
    global $post;
    if (isset($_GET['_m'])) {
    	$current_month = date('m', $_GET['_m']);
    	$current_year = $_GET['_y'];
    } else {
    	$current_month = date('m'); // This gets the current month
    	$current_year = date('Y');
    }
    ?>
    <script language="javascript">var mnth=<?php echo $current_month; ?>;var yr=<?php echo $current_year; ?>;</script>
    <p><strong><a id="events-nav-prev" href="#prev"><<</a> | <a id="events-nav-next" href="#next">>></a></strong> <span class="date-info"></span></p>
    <img src="<?php bloginfo('template_url'); ?>/images/loading.gif" alt="Loading..." id="upcoming-events-loader" style="visibility:hidden;" />
    </div>
    
    <div id="upcoming-events-scroller" class="scrollable">
    <script language="javascript" type="text/javascript">
    /* use a function for the exact format desired... */
    var d = new Date();
    var m_names = new Array("January", "February", "March","April", "May", "June", "July", "August", "September", "October", "November", "December");
    
    function getEvents(getAll) {
    	jQuery('#upcoming-events-loader').css('visibility', 'visible');
    	jQuery('#upcoming-events-months .date-info').html(m_names[mnth-1] + " " + yr);
    	jQuery.get('/events-widget/', {
    			_m:mnth,
    			_y:yr,
    			getAll:getAll
    		}, function(data) {
    			jQuery('#upcoming-events-scroller').html(data);
    			jQuery('#upcoming-events-loader').css('visibility', 'hidden');
    		});
    	}
    
    jQuery(document).ready(function() {
    	getEvents('true');
    
    	// bind nav events
    	jQuery('#events-nav-prev').click(function() {
    		if (mnth == 1) {
    			mnth = 12;
    			yr -= 1;
    		} else {
    				mnth -= 1;
    		}
    		console.log(mnth);
    
    	jQuery('#upcoming-events-months .date-info').html(m_names[mnth-1] + " " + yr);
    			getEvents();
    			return false;
    		});
    
    	jQuery('#events-nav-next').click(function() {
    			if (mnth == 12) {
    				mnth = 1;
    				yr += 1;
    			} else {
    				mnth += 1;
    			}
    			console.log(mnth);
    
    	jQuery('#upcoming-events-months .date-info').html(m_names[mnth+1] + " " + yr);
    			getEvents();
    			return false;
    	});
    });
    </script>
  19. robbiegod
    Member
    Posted 1 month ago #

    I just updated WordPress to version 4.2.1. I also update ACF to v5.2.3 PRO. Sadly, now my filter for dates no longer works.

    I'm only getting events that are in the current month only. So if an event spans from April to May, that even won't appear. Only events that have a start date and end date in May will appear.

    If someone has a suggested to update the filter to make it work again, I'd appreciate the assistance.

    I'm on the hunt now for a fix.

  20. keesiemeijer
    moderator
    Posted 1 month ago #

    Hi robbiegod

    Try it with this plugin (click the "Download ZIP button).
    https://github.com/keesiemeijer/meta-date-archive

    To have it use your meta key names you have to put this in your (child) theme's functions.php file.

    add_filter( 'meta_date_archive_start', 'meta_date_archive_start_key' );
    add_filter( 'meta_date_archive_end',   'meta_date_archive_end_key' );
    
    function meta_date_archive_start_key( $key ) {
    	// Return your event start key
    	return 'event_start_date';
    }
    
    function meta_date_archive_end_key( $key ) {
    	// Return your event end key
    	return 'event_end_date';
    }

    Do a query like this (without adding and removing the 'get_meta_sql' filter):

    <?php
    $all_events = array (
    	'post_type'               => 'events',
    	'posts_per_page'          => 50,
    	'meta_archive_start_date' => '20140601',
    	'meta_archive_end_date'   => '20140630',
    );
    
    $date_query = new WP_Query( $all_events );
    ?>

    I did not test it with WordPress 4.2.1 but it should work.

  21. robbiegod
    Member
    Posted 1 month ago #

    so I add all of that in addition to the code I already have. I'll try this out tomorrow. I hope it works.

  22. keesiemeijer
    moderator
    Posted 1 month ago #

    Remove the WP_Query() and the add_filter() and remove_filter() functions you have now. Activate the plugin and use a similar query in your theme template files as in my last post.

  23. robbiegod
    Member
    Posted 1 month ago #

    thanks keesiemeijer! This worked perfectly!

  24. keesiemeijer
    moderator
    Posted 4 weeks ago #

    Hi robbiegod

    I forgot to mention the plugin (you have now) will replace your date archives with the posts with the start and end date custom fields.

    I've updated the plugin to not have the date archives replaced by default.

    Can you replace the archive plugin you have now with the updated version.
    https://github.com/keesiemeijer/meta-date-archive

  25. robbiegod
    Member
    Posted 4 weeks ago #

    sure, i had just done a round of testing with the previous version and it all seemed to work great. I'll deploy this to my dev site and then to my live site.

  26. robbiegod
    Member
    Posted 4 weeks ago #

    just deployed it to my development website and no issues to report, it still works! handy plugin. I was originally hoping to be able to do this query without a plugin, but your plugin is light enough that i think its a good solution. Awesome to be able to replace a whole Events Calendar plugin with this one light plugin. I do wish the ACF date picker fields would be able to work this way out of the box.

    Anyway, thanks for your excellent assistance. Great plugin!

  27. keesiemeijer
    moderator
    Posted 4 weeks ago #

    You're welcome. Thanks for the feedback :)

Reply

You must log in to post.

About this Topic