Sorting and limitation with pre_get_posts

This is my third question regarding WP queries and pre_get_posts (sorry about that) but I cant give up one step before the full success 🙂

So the story – my query is limiting front page posts to only those which have featured images. So far so good. My last goal is to sort those posts by custom field, order them in ASC order and show only posts which metakey/custom field date is in 30 days from now. So far this is my code along with my attempts but as it seems Im not even close…

Current sorting is chaotic. For the second part showing only posts in the next 30 days what I have in mind is to set one var holding current date + 30 days, one var with custom field date and to check is the first one bigger than the second one but how I can get the custom field in this function and where to put this if check ? Probably these question are simple but Im struggling. Thank you for your time and for sharing knowledge.

add_action( 'pre_get_posts', function ( $q ) 
{

    if (     $q->is_home() // Target the home page only
          && $q->is_main_query() // Target only the main query
          || $q->is_category()
    ) {
        $meta_query = array(
            array(
                'key' => '_thumbnail_id',
            )
        );
        $q->set( 'meta_query', $meta_query );
        $q->set('orderby','meta_value_num');
$q->set('meta_key', 'event_date');
$q->set('order', 'ASC');
    }
});

Solutions Collecting From Web of "Sorting and limitation with pre_get_posts"

Before we code and go into particulars, lets look at the problem at hand.

  • First of all, any valid date format is valid in a custom field if you are not going to use it for comparisons and sorting

  • When you are going to sort by date or do comparisons by date in a custom field, bullet point one should be ignored. There are only two workable formats which will work if you are going to sort by custom field date. They are:

    • Unix Timestamp (which I prefer out of personal opinion) which is an integer value and corresponds to the amount of seconds passed since 1 January 1970. Saving a date in this format is the most accurate in my opinion, and any timestamp can be easily converted into any format using the DateTime class. Just a note, only dates between 01 January 1970 and 19 January 2038 are currently supported

    • Y-m-d H:i:s or Y-m-d or H:i:s. Any other format will not work. The orderby parameter sorts literally, so basically, the first number of the given strings are compared and sorted ascending or descending according to the order parameter, if they match, the second number is compared, etc until non matching numbers are found and the sort order is returned. It is recommended to set the correct type parameter value in your meta_query according to your specific format. These type parameter values are DATE, DATETIME and TIME which will handle your exact format when querying this particular fields

The above is very important, so before you continue, make sure that you understand that part and that your values is in the correct format, otherwise the next section will not work

Now that the format is sorted, we need to write down in plain English what we need. I tend to use this method if something gets tricky or too complex. It does help a lot. So, in plain English, this is what your query should do

All posts with thumbnails should be shown. This option is saved as a custom field, _thumbnail_id. In addition, these posts should also have dates in the future, to be exact, within the next thirty days. This is also according to a custom field. These posts should also be sorted according to the dates in the custom field

Great, so this means, in our meta_query, our posts should have a thumbnail AND (this will be our relation between the custom fields) and must have custom field date value BETWEEN the current time and 30 days from the current time

Lets put that into code: (Note: Caveat: This is untested and requires at least PHP 5.3 to work due to the use of DateInterval. Also, this is set up with the Y-m-d H:i:s date format in your custom field. Keep your date formats uniformly, otherwise it will not work)

add_action( 'pre_get_posts', function( $q )
{
    if (     $q->is_home() // Target the home page only
          && $q->is_main_query() // Target only the main query
          || $q->is_category() // NO IDEA WHY YOU ADDED THIS! ARE YOU TARGETING CATEGORY PAGES AS WELL?
    ) {

        $current_time = new DateTime(); // Get the current date and time
            $current_time_format = $current_time->format( 'Y-m-d H:i:s' ); // This is the current date and time

        $after_thirty_days = new DateTime(); // Create the date after 30 days
            $after_thirty_days->add( new DateInterval( 'P30D' ) ); // Adds 30 days to the current date and time
            $date_after_thirty_days = $after_thirty_days->format( 'Y-m-d H:i:s' );

        $meta_query = array(
            //Default relation is AND, no need to set it. So posts should match both conditions set in the two sets of arrays
            array( // Our first array of conditions set to get posts with a thumbnail
                'key' => '_thumbnail_id'
            ),
            array( // Our second array of conditions. Posts should also have these conditions in addition to the first array of conditions
                'key' => 'event_date',
                'value' => array( $current_time_format, $date_after_thirty_days ), // Add todays date and date after 30 days as an array
                'compare' => 'BETWEEN', // Will look for valid values between the array of dates given in the value parameter
                'type' => 'DATETIME' // We will use the date and time format to compare our dates
            ),
        );
        $q->set( 'meta_query', $meta_query );
        $q->set( 'orderby', 'meta_value_num' );
        $q->set( 'meta_key', 'event_date' );
        $q->set( 'order', 'ASC' );
    }
});

LAST FEW NOTES:

  • As I have noted before, this is untested, so it might not work straight out of the box. It may need some adjustments.

  • I have used the format Y-m-d H:i:s here. You can just use Y-m-d here as well. Just adjust the query and dates accordingly. If are going to use timestamps, remember to convert your comparison dates into timestamps

  • Because you are using infinite scrolling, you will need to remember to adjust that function accordingly, otherwise page 2 and up will display wrong posts

EDIT

The above code is now tested and work as expected with date formats of Y-m-d H:i:s and Y-m-d. I had to fix one or two small bugs. Hope it helps