Wordpress meta query

Question

I have created a theme with a custom post type of reports. I installed a rating plugin that interacts with this post type, allowing users to rate the reports. It stores the post rating in two fields, sum and count where sum is the total for all ratings, and count is the number of individual ratings.

Example: If a 5 people rated a post as 1, 2, 3, 4, and 5, the sum would be 15 and the count would be 5.

When a user visits the reports archive page, they see a list of all posts of the report post type. However, I want to add a query parameter to filter down to posts with an average rating of 4 or higher. I'm currently trying to use the pre_get_posts hook as follows:

add_filter( 'pre_get_posts', 'filterReports' );

function filterReports( $query ) {
  if( is_post_type_archive( 'reports' ) && $_GET['top'] ) {
    global $wpdb;
    $query = $wpdb->prepare(
      "SELECT * 
      FROM 
        wp_postmeta AS sum
        wp_postmeta AS count
      WHERE
        sum.meta_key = 'sum' AND
        count.meta_key = 'count' AND
        sum.meta_value / count.meta_value >= 4"
    );
  }
}

I'm not entirely sure how to construct my custom query in the above. Any advice would be greatly appreciated.

score 0 · Answer 1 · answered Dec 31 '19 at 06:56

Use below code will work as per your scenario.

add_filter( 'pre_get_posts', 'filterReports' );

function filterReports( $query ) {
  if( is_post_type_archive( 'reports' ) && $_GET['top'] ) {
     $reports_meta_query = $query->get('meta_query');

     //Add our meta query to the original meta queries
     $reports_meta_query[] = array(
                    'key'=>'count',
                    'value'=> 4,
                    'compare'=>'>=',
                );
     $query->set('meta_query',$reports_meta_query);
     // somehow construct a query that checks if sum / count >= 4
  }
}

It looks to me like this will get all reports that have been rated more than four times. I need to filter on all reports where dividing the `sum` by the `count` results in a number that is `>= 4` — Hughes, Dec 31 '19 at 12:47

Thiago de Oliveira Cruz · Answer 2 · 2020-01-07T16:53:54.640

Pretty sure the query you are looking for is something like this:

SELECT 
 sum.post_id,
 sum.meta_value,
 count.meta_value,
 (sum.meta_value / count.meta_value) as result
  FROM 
    wp_postmeta sum
  LEFT JOIN wp_postmeta count USING(post_id)
  WHERE
    sum.meta_key = 'sum' AND
    count.meta_key = 'count'
  HAVING 
    result >= 4

You are basically joining twice the same table based on the post_id, so you can then query by the meta_key of both sum and count, then you look for the result of your math in a Having clause to check if the result would be bigger than 4 as requested.

Hope with this you can get what you were looking for.

Cheers

Wordpress meta query

2 Answers2