WP_Query and NULL meta keys

Question

WP_Query and NULL meta keys

Kosta Kondratenko

2022年5月11日 14:04

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.

Topic wp-query plugins Wordpress

Category Web

Ben Casey · Accepted Answer · 2017年3月13日 04:53

I do not believe there is a way to do both queries in one within WordPress.

If the filters you have in place are working and aren't injecting raw user input into the database query then what is the issue? that is exactly what the filters are there for.

If you are uncomfortable using the filters, then you could do two seperate queries to the database to achieve the same result. It would mean a small performance hit (depending on the size of the database) but this could be mitigated by using the transients API to cache the results.

EDIT

If you need to add a key to all your posts already published, something like the below should do the trick it will loop through all products that have no meta key _average_rating and add one of 0. UNTESTED

I normally do this based on a secret $_GET parameter in admin the delete it after it has run.

$query = new WP_Query( [
    'post_type'      => 'product',
    'posts_per_page' => -1,
    'post_status'    => 'publish',
    'meta_query'     => [
        [
            'key'     => '_average_rating',
            'compare' => 'NOT EXISTS'
        ]
    ]
] );

foreach( $query->posts as $post ){
    update_post_meta( $post->ID, '_average_rating', 0 );
}

WP_Query and NULL meta keys

add_filter('posts_join_paged','edit_posts_join_paged');

About