Relating Wordpress posts to a particular time of the year

Question

I have a 'diary' type blog in which I want to highlight all the posts made around the current day and month, regardless of the year. You could say they need to be 'seasonal'.

Changing the order to day of the year, starting with TODAY seems a good way of achieving this. In MySQL a conditional ORDER BY is required so that dates before NOW(), less a few days, are sorted to the end of the list.

ORDER BY 
    CASE WHEN DAYOFYEAR(post_date) < DAYOFYEAR(NOW())-14 THEN 1
    ELSE 0  END ASC, DAYOFYEAR(post_date) asc

Tested independently of Wordpress this gives the desired sort order eg:

post_date
2022-04-21 12:53:35 
2022-04-29 20:17:35 (today's date)
2019-04-30 13:35:12 
2018-05-23 10:10:12
2017-07-11 13:06:35
2021-09-03 08:11:08
2017-01-01 20:17:35
2021-03-22 12:19:11
2020-04-03 11:12:44

A function like the one below can be used to change sort order of the Wordpress query but it's not working. Is pre_get_posts really the place to do this and am I incorporating the ORDER BY statement in the correct way? Could my theme (Elegant Themes 'Extra') be interfering with the sort order?

add_action( 'pre_get_posts', 'sort_posts_by_day_of_year');
            
function sort_posts_by_day_of_year($query) {
//gets the global query var object

global $wp_query;
            
//gets the front page id set in options
$front_page_id = get_option('page_on_front');
            
if ( 'page' != get_option('show_on_front') || $front_page_id != $wp_query->query_vars['page_id'] )
                    return;

if ( !$query->is_main_query() )
                return;

$query-> set('post_type' ,'post');
                
$query-> set('post__in' ,array( $front_page_id));
    $query-> set('orderby' ,'CASE 
    WHEN DAYOFYEAR(post_date) < DAYOFYEAR(NOW())-14 THEN 1 
    ELSE 0 END ASC, DAYOFYEAR(post_date) asc');
                
$query->set( 'order' , 'ASC' );
            
//we remove the actions hooked on the '__after_loop' (post navigation)
remove_all_actions ( '__after_loop');
}
                        

The above doesn't have any effect even after removing surplus line returns (the sort order remains as standard) but it doesn't cause a crash. Is the syntax correct but the function is not working in the right place? I tried changing $front_page_id to the actual page number but that had no effect.

Any help/advice very much appreciated.

Answer 1

An answer to my own question - I have corrected the MySQL part so that it works - just a simple error of logic was causing the problem. I also added in -14 to make the posts for NOW() start appearing two weeks earlier:

SELECT post_TYPE, post_date,ID,post_title,DAYOFYEAR(post_date) from wp_posts where post_type = 'post' 
ORDER BY CASE  
WHEN DAYOFYEAR(post_date) < DAYOFYEAR(NOW())-14 THEN 1
       ELSE 0
      END ASC, DAYOFYEAR(post_date) asc

And thanks to Wordpress.org Forums I have been able to fix the rest of my problem.

The orderby query var only accepts certain pre-defined values. You cannot pass arbitrary columns or SQL this way. You can use the "posts_orderby" filter to get WP to use any SQL order clause you'd like. https://developer.wordpress.org/reference/hooks/posts_orderby/

My current solution is:

add_filter('posts_orderby', 'seasonal_posts_orderby');
function seasonal_posts_orderby($sortorder) {
    $sortorder = "CASE WHEN DAYOFYEAR(post_date) < DAYOFYEAR(NOW())-14 THEN 1 ELSE 0 END , DAYOFYEAR(post_date) ";
    return $sortorder;   
}

This affects all queries including the dashboard list of posts - which suits me well at present, but I may need to add a posts filter to avoid sorting projects, which I am thinking of using.