Order posts by 2 custom fields and if one custom field is zero orderby another custom field

Question

I have a really tricky function to implement and couldn't find a way by searching

Please see details below

I have a custom post type called "accommodation", and for this post type, there are 2 custom fields, one is "max_sleep_1", another one is "max_sleep_2"

Now the thing is for some posts under this post type, "max_sleep_1" is set and for other posts "max_sleep_2" is set, But I need to order all posts by max sleep number(the maximum one between this 2 fields)

for example

post1 has "max_sleep_1" set to 1 and "max_sleep_2" set to 0

post2 has "max_sleep_2" set to 2 and "max_sleep_1" set to 0

post3 has "max_sleep_1" set to 3 and "max_sleep_2" set to 0

so the final order of post should be post1->post2->post3, so as you can see the problem, I am ordering by 2 fields, when "max_sleep_1" is set to 0 then I need to use "max_sleep_2" to sort

Hope you can understand what I am saying

I have been searching but couldn't find a proper solution, I also tried to merge this 2 fields after I get the results of WP_query, but also this didn't work.

Now I can only order by one field using meta key and the result is post1->post3->post2, like below

$args = array(
'post_type'   => 'accommodation',
'paged'       => get_query_var('paged'),
'post_parent' => $parent,
'meta_key'    => 'max_sleep_1',
'orderby'     => 'meta_value_num',
'order'       => 'ASC'  
);
$the_query = new WP_Query( $args );

I will really appreciate it if anyone can help me out

Answer 1

Well let me answer my own question, this may not be perfect, but it works

First I query the posts using WP_query normally, and then I build an associative array by myself including 2 values, one is post ID another one is the order(I merge 2 order fields into one here)

then I use "uasort" to sort the associative array I built based on merged order, so now I have an array with ordered post ID value

then I use WP_query to query again, this time I query use "post_in" array that I have sorted and then orderby => "post_in", so I got the results I want(in correct order)

This is a very specific case and hope this may help someone future

the key is the query variable "post_in" and you can order by "post_in" array, so in this way, you can query posts whatever order you want as long as you build a "post_in" array, like below

$args = array(
'post_type' => 'accommodation',
'post_parent' => $parent,
'post__in' => $ordered_array, 
'orderby' => 'post__in',

)

thanks