1

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.

0

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 );
}
  • Hi Ben, thanks for the response. Not sure if you're understanding my issue. The posts that are 'NULL' are not showing up with WP_Query - I want a way to display the NULL values too. That's all. – Kosta Kondratenko Mar 11 '17 at 6:27
  • @KostaKondratenko Perhaps a hook on save_post to add the meta value of 0 when creating a new post. that way the key would always exist, and you wouldn't have to worry about meta keys the don't exist.? – Ben Casey Mar 11 '17 at 10:44
  • Hi Ben, that's a great idea. However that still leaves the problem of the current 1,000+ posts that don't have the meta key values. I can't just delete them. Any input you have on this issue would be much appreciated. – Kosta Kondratenko Mar 12 '17 at 7:23
  • @KostaKondratenko Example added, you can query for keys that don't exist in WP_Query. Have a look through the documentation on WP_Query, especially the part about meta_query – Ben Casey Mar 12 '17 at 11:01
  • Thank you Ben - it is much appreciated. I look forward to trying this out later in the day. – Kosta Kondratenko Mar 12 '17 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.