WP_Query and NULL meta keys
I have a bunch of custom posts with two meta keys - _claimed and _average_rating. The _average_rating is only added as a meta key if someone leaves a rating on the post and the _claimed meta key is only set to 1 if a user claims the listing - otherwise the listings have no values for _claimed or _average_rating.
What I would like to do is to arrange the listings as so: - Show all listings that are claimed first sorted by average review (highest to lowest) - Show all listings that are unclaimed after this, sorted by average review (highest to lowest)
The following MySQL code was provided to me and this seems to work when I test 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 use requires the filter to be created in a WP_Query - the plugin if you are interested is called FacetWP and below is a sample code of the filters I have at the moment to give you an idea of what is required:
--
function my_facetwp_sort_options( $options, $params ) {
$options['rating'] = array(
'label' = 'Ripoff',
'query_args' = array(
'meta_query' = array(
array(
'key' = '_average_rating',
'value' = 2,
'compare' = '=',
'type' = 'NUMERIC',
)
),
'orderby' = 'meta_value_num', // sort by numerical custom field
'meta_key' = '_average_rating', // required when sorting by custom fields
'order' = 'DESC' // descending order
)
);
$options['review_desc'] = array(
'label' = 'Average Review (highest)',
'sort_custom' = true,
'query_args' =
array(
'meta_query' = array(
'relation' = 'OR',
array(
'key' = '_claimed',
'value' = 1,
'compare' = '=',
'type' = 'NUMERIC'
),
array(
'key' = '_average_rating'
)
)
)
);
$options['recent_review'] = array(
'label' = 'Most Recent Reviews',
'query_args' = array(
'orderby' = 'meta_value_num',
'meta_key' = 'date_reviewed',
'order' = 'DESC',
)
);
return $options;
}
add_filter( 'facetwp_sort_options', 'my_facetwp_sort_options', 10, 2 );
-- Plugin doc here if interested: https://facetwp.com/documentation/facetwp_sort_options/
In order to generate the MySQL query that I require - which I know works I've added the following code:
function 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;
}
function 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 adds the custom orderby and also JOIN statements into the WP_Query. However I don't know how to recreate the following code in WP_Query:
SELECT p.`ID` , IFNULL( pm.`meta_value` , 0 ) AS claimed, IFNULL( pm2.`meta_value` , 0 ) AS averageRating
Note that in the above example it sets all values for _claimed and _average_rating as 0 - so that everything is displayed. How can I do this with WP_Query though in the confines of how the plugin works as per the requirements I've listed above which are:
- Show all listings that are claimed first sorted by average review (highest to lowest)
- Show all listings that are unclaimed after this, sorted by average review (highest to lowest)
Any input on this issue would be much appreciated.