In wp_query, how can I order by a complex calculated or conditional fields?

Question

In wp_query, is it possible to build a complex query so I can order by a conditional or calculated fields? what I'm trying to do is something like the next query in MySql:

SELECT *, field1, field2
  case when field1+field2 > some_value then 1 else 2 end as my_alias
FROM my_table
ORDER BY my_alias ASC

I want to build queries like this one using wp_query, is this possible? if yes, how can I accomplish that?

Answer 1

Yeah, you need to

• add custom field using https://developer.wordpress.org/reference/hooks/posts_fields/

• add custom order by using https://developer.wordpress.org/reference/hooks/posts_orderby/

Answer 2

I don't see any way to do this with a single WP_Query as meta_query doesn't allow you such flexibility, though you can do three distinct queries then merge them (untested code):

// Get ongoing events
$ongoing = new WP_Query(array(
    'post_type' => 'event',
    'meta_key' => 'date_from',
    'orderby' => 'meta_value',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'date_from',
            'value' => date('Y-m-d'),
            'compare' => '<=',
            'type' => 'DATE'
        ),
        array(
            'key' => 'date_to',
            'value' => date('Y-m-d'),
            'compare' => '>=',
            'type' => 'DATE'
        )
    )
));
foreach($ongoing as $key => $ongoing_post) {
    $ongoing_post->event_status = 'ongoing';
    $ongoing[$key] = $ongoing_post;
}

// Get upcoming events
$upcoming = new WP_Query(array(
    'post_type' => 'event',
    'meta_key' => 'date_from',
    'orderby' => 'meta_value',
    'order' => 'ASC',
    'meta_query' => array(
        array(
            'key' => 'date_from',
            'value' => date('Y-m-d'),
            'compare' => '>',
            'type' => 'DATE'
        )
    )
));
foreach($upcoming as $key => $upcoming_post) {
    $upcoming_post->event_status = 'upcoming';
    $upcoming[$key] = $upcoming_post;
}

// Get past events
$past = new WP_Query(array(
    'post_type' => 'event',
    'meta_key' => 'date_from',
    'orderby' => 'meta_value',
    'order' => 'DESC',
    'meta_query' => array(
        array(
            'key' => 'date_to',
            'value' => date('Y-m-d'),
            'compare' => '<',
            'type' => 'DATE'
        )
    )
));
foreach($past as $key => $past_post) {
    $past_post->past_status = 'past';
    $past[$key] = $past_post;
}

// Merge'em all
$events = array_merge($ongoing, $upcoming, $past);

The thing is to use meta_query to compare the meta values with the actual date (you may want to change the date format depending of how they are stored in date_from and date_to fields), and do a little loop right after to add a property to all post object with the right event_status which you can work with when displaying posts.

Maybe there is a clever way to achieve this through WP_Query filters but it would need more in-depth investigation inside WP_Query source code as it is not really documented inside the codex.