In wp_query, how can I sort by complex computed or conditional fields?

Question

In wp_query, is it possible to build a complex query so that I can sort by conditional or calculated fields? what I'm trying to do is something like the following 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 create queries like this using wp_query, is this possible? if so, how can I do this?

Answer 1

Yes you need

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

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

Answer 2

I see no way to do this with one WP_Query , since meta_query does not allow you that much flexibility, although you can make three different queries and then combine 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('Ym-d'), 'compare' => '<=', 'type' => 'DATE' ), array( 'key' => 'date_to', 'value' => date('Ym-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('Ym-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('Ym-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 point is to use meta_query to compare metadata values ​​with the actual date (you can change the date format depending on how they are stored in the date_from and date_to ), and do a short cycle immediately after adding the property to all post objects with the correct event_status , with which you can work with when displaying messages.

There may be a smart way to achieve this through WP_Query filters , but this will require a more detailed study inside the WP_Query source code as it really is not documented inside the code.

Answer 3

I use and try to do it,

 <?php global $wpdb; $result = $wpdb->get_results("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"); print_r($result); ?> 

Look at Click here.