WP_Query and NULL meta keys

I’ve a bunch of customized posts with two meta keys – _claimed and _average_rating. The _average_rating is barely added as a meta key if somebody leaves a ranking on the put up and the _claimed meta secret is solely set to 1 if a person claims the itemizing – in any other case the listings haven’t any values for _claimed or _average_rating.

What I wish to do is to rearrange the listings as so:
– Present all listings which are claimed first sorted by common overview (highest to lowest)
– Present all listings which are unclaimed after this, sorted by common overview (highest to lowest)

The next MySQL code was supplied to me and this appears to work once I check in phpMyAdmin:

SELECT p.`ID` , IFNULL( pm.`meta_value` , 0 ) AS claimed, IFNULL( pm2.`meta_value` , 0 ) AS averageRating
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.`post_id` 
AND pm.`meta_key` =  '_claimed'
LEFT JOIN wp_postmeta pm2 ON p.ID = pm2.`post_id` 
AND pm2.`meta_key` =  '_average_rating'
ORDER BY claimed DESC , averageRating DESC  

The plugin I take advantage of requires the filter to be created in a WP_Query – the plugin if you’re is known as FacetWP and under is a pattern code of the filters I’ve in the meanwhile to provide you an concept of what’s required:

—

perform my_facetwp_sort_options( $choices, $params ) {
    $choices['rating'] = array(
        'label' => 'Ripoff',
        'query_args' => array(
         'meta_query' => array(
                array(
                    'key' => '_average_rating',
                    'worth' => 2,
                    'evaluate' => '<=',
                    'sort' => 'NUMERIC',
                )
            ),
            'orderby' => 'meta_value_num', // type by numerical customized discipline
            'meta_key' => '_average_rating', // required when sorting by customized fields
            'order' => 'DESC' // descending order
        )
    );

    $choices['review_desc'] = array(
        'label' => 'Common Assessment (highest)',
        'sort_custom' => true,
        'query_args' => 
        array(
  'meta_query' => array(
  'relation' => 'OR',
    array(
      'key' => '_claimed',
      'worth' => 1,
    'evaluate' => '=',
     'sort' => 'NUMERIC'
    ),
    array(
      'key' => '_average_rating'
    )
  )
)
    );

    $choices['recent_review'] = array(
        'label' => 'Most Current Critiques',
        'query_args' => array(
            'orderby' => 'meta_value_num',
            'meta_key' => 'date_reviewed',
            'order' => 'DESC',
        )
    );

    return $choices;

}
add_filter( 'facetwp_sort_options', 'my_facetwp_sort_options', 10, 2 );

—
Plugin doc right here if : https://facetwp.com/documentation/facetwp_sort_options/

With the intention to generate the MySQL question that I require – which I do know works I’ve added the next code:

---

perform mysite_custom_sort( $orderby, $wp_query ) {
  if ( isset( $wp_query->query_vars['sort_custom'] ) ) {
    $orderby = 'mt1.meta_value ASC, mt2.meta_value DESC';
  }
  return $orderby;
}
perform edit_posts_join_paged($join_paged_statement) {
    if ( isset( $wp_query->query_vars['sort_custom'] ) ) {
    $join_paged_statement = "LEFT JOIN wp_postmeta pm ON p.ID = pm.`post_id` 
AND pm.`meta_key` =  '_claimed'
LEFT JOIN wp_postmeta pm2 ON p.ID = pm2.`post_id` 
AND pm2.`meta_key` =  '_average_rating'";
    }
    return $join_paged_statement;   
}

add_filter( 'posts_orderby', 'mysite_custom_sort', 10, 2 );

add_filter(‘posts_join_paged’,’edit_posts_join_paged’);

My understanding is that this provides the customized orderby and likewise JOIN statements into the WP_Query. Nonetheless I do not know recreate the next code in WP_Query:

SELECT p.`ID` , IFNULL( pm.`meta_value` , 0 ) AS claimed, IFNULL( pm2.`meta_value` , 0 ) AS averageRating

Be aware that within the above instance it units all values for _claimed and _average_rating as 0 – in order that every part is displayed. How can I do that with WP_Query although within the confines of how the plugin works as per the necessities I’ve listed above that are:

• Present all listings which are claimed first sorted by common overview (highest to lowest)
• Present all listings which are unclaimed after this, sorted by common overview (highest to lowest)

Any enter on this problem could be a lot appreciated.