5

I am trying to order the search results page of my site by a specific meta_key. The catch is I need to return all results even when there's no value in the meta_key

The posts are custom post types that are events.

The results are already being sorted to a specific set of custom post types using this:

function SearchFilter($query) {
    if (!is_admin() && $query->is_search() ) {
        $query->set('post_type', array( 'event', 'university', 'organization'));
    }
    return $query;
}

add_action('pre_get_posts','SearchFilter', 9999);

There is some other filtering going on in order to filter out events that have passed or those that are set to a custom field of "off". I can post the code used to do that as well, if needed.

Essentially, I can use

$query->set('meta_key', 'wpcf-start-date');
$query->set('orderby', array('meta_value' => 'ASC'));

to order the events by event date. This works well, but won't return any results that have '' (what I assume is null) in the wpcf-start-date field.

I have tried using the answer in this question:

Order posts by custom field and if custom field is empty return remaining posts

But either I don't understand what's going on well enough to customize it for my use, or it just doesn't work for me.

I can provide any additional information upon request.

Any help would be greatly appreciated!


UPDATE:

I was looking again at the other Stack question I refer to. I have this that is working:

function sortResults( $query ) {
    if ( is_admin() || ! $query->is_main_query() ) {
        return;
    }

    $query->set( 'meta_key', 'wpcf-start-date' );
    $query->set( 'orderby', 'meta_value');
    $query->set( 'order', 'DESC');

    add_filter( 'get_meta_sql', 'filterResults' );
}
add_action( 'pre_get_posts', 'sortResults' );

function filterResults( $clauses ) {
    remove_filter( 'get_meta_sql', 'filterResults' );

    // Change the inner join to a left join,
    // and change the where so it is applied to the join, not the results of the query.
    $clauses['join']  = str_replace( 'INNER JOIN', 'LEFT JOIN', $clauses['join'] ) . $clauses['where'];
    $clauses['where'] = '';

    return $clauses;
}

But like the other question, this puts the events without dates at the top and the events with dates at the bottom. The other question suggests using two loops. I can't seem to get two loops working on my site. The search results page is highly customized.

  • Just an idea, if you set the date of events without dates to something far far back, and then wherever you display the actual date in the code you check whether the event's date is this date, then hide it, you can avoid the whole super complicated coding. – Emil Nov 25 '16 at 11:33
  • 1
    Hmm, excellent idea! That actually might work! I will have to experiment with it and see. I'll write back with the results! – Nick Taylor Nov 29 '16 at 15:44
  • I do believe this would work. But the issue is we have 100's of event pages that would need to be changed. I think we will start doing this moving forward but it will take some time for this method to play out. – Nick Taylor Jan 9 '17 at 15:10
1

You can accomplish this with multiple meta queries.

$query->set( 'meta_query', [
  'relation' => 'OR',
  'wpcf-start-date' => [
    'key' => 'wpcf-start-date',
    'compare' => 'EXISTS',
  ],
 'no-start-date' => [
    'key' => 'wpcf-start-date',
    'compare' => 'NOT EXISTS'
  ],
] );
$query->set( 'orderby', 'wpcf-start-date' );
$query->set( 'order', 'ASC' );

This will tell WP to create a query that will match every post, whether or not is has the key, setting it up as a LEFT JOIN, and then sorts on the value of that key, including those with NULL values.

  • I am not sure exactly what's going on with your code, but it doesn't seem to work at all for me. I could possibly be placing it in the wrong place. Also, would this place the events with NULL at the bottom? The query I mentioned above in my update works perfectly, besides NULL events being at the top. – Nick Taylor Jan 27 '17 at 17:30
  • @NickTaylor Do note that the [ and ] is alternative syntax for array(). I may be mistaken but even though this is getting both values that exist and do not exist, it wouldn't necessarily group them with wpcf-start at the top. – Howdy_McGee Jan 27 '17 at 23:04
  • The two separate meta queries are there so that WP will find all posts, whether or not they have the wpcf-start-date meta. The main point of including these is so that WP will make the JOINs that power the orderby clause. As you say, you can also accomplish this by filtering the query clauses and directly editing the SQL. But I prefer not to do so if there's an alternative. – Jonathan Brinley Jan 28 '17 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.