Query events within a date range

Hi,

My question is pretty simple.

I'm trying to create a custom query to get events between a date range. I'm doing that for a custom template that will use the Calendar class.
Actually, i have to call every events to make it work.
Moreover, not every events are displayed in the calendar. This is the actual template with comments to show what is working and what's not:
https://gist.github.com/3945881

Thank you !

  • KuneStudio

    Ok, it's resolved (well, it looks like my code is running but I have to debug just to be sure). If someone is interested, this is the walkthrough:

    in the functions.php I added:

    function date_check_join( $join ) {
        global $wpdb;
        if(is_page('calendrier'))
    	    $join .= " JOIN ".$wpdb->postmeta." AS startdate ON
    	        (".$wpdb->posts.".ID = startdate.post_id AND
    	        startdate.meta_key = 'incsub_event_start')
    	        JOIN ".$wpdb->postmeta." AS enddate ON
    	        (".$wpdb->posts.".ID = enddate.post_id AND
    	        enddate.meta_key = 'incsub_event_end')";
        return $join;
    }
    
    function date_check_where( $where ) {
    
        global $wpdb, $wp_query;
    
      	// current date in unix format
      	$current_time = current_time("timestamp");
    
      	$annee = $wp_query->query_vars['annee'];
      	$mois = $wp_query->query_vars['mois'];
      	$year = $annee ? $annee : date_i18n('Y'); // date_i18n uses current_time when called like this
      	$month = $wp_query->query_vars['event_monthnum'];
      	$month = $mois ? $mois : date_i18n('m');
    	$time = strtotime("{$year}-{$month}-01");
    	$timem = strtotime(date("Y-m-d", $time) . "+1 month");
    
        if(is_page('calendrier')){
    	    $where .= " 
    
    	    	AND (
    	    			( Unix_Timestamp(startdate.meta_value) >= $time AND Unix_Timestamp(startdate.meta_value) <= $timem )
    	    			OR
    	    			( Unix_Timestamp(enddate.meta_value) >= $time AND Unix_Timestamp(enddate.meta_value) <= $timem )
    	    			OR
    	    			( Unix_Timestamp(startdate.meta_value) <= $time AND Unix_Timestamp(enddate.meta_value) >= $timem )
    	    		)
    
    	    ";
    	}
        return $where;
    }

    I recover the "year" and "month" query_vars that i have setup and I use a Join clause to get every post in the date range.
    I don't know if the SQL is the most optimum but it looks like it's working (to explain it, I check either if:
    - the event dates include the first day of the current month
    - the event dates include the last day of the current month
    - the event dates are "larger" than the current moth
    ).

    Then, I wrap my query (line 37 of the gist) with

    add_filter( 'posts_join', 'date_check_join' );
    add_filter( 'posts_where', 'date_check_where' );

    and

    remove_filter( 'posts_join', 'date_check_join' );
    remove_filter( 'posts_where', 'date_check_where' );

    Et voilà :slight_smile:

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.