WooCommerce Average Category Rating Based On Products

Looking to get the average category review rating based on the products inside the WooCommerce category? All you may need is to query the comments table.

Get the average category review rating

To get the average category review rating, you need to query all the categories and their reviews as per below.

global $wpdb;
$args = array(
     'taxonomy' => 'product_cat',
     'posts_per_page' => -1,
     'hide_empty' => 0,
     'orderby' => 'title',
     'order' => 'ASC',
);
foreach ( get_categories( $args ) as $category ) :
     $post_ids = get_posts( array(
          'post_type' => 'product',
          'posts_per_page' => -1,
          'tax_query' => array(
               array(
                    'taxonomy' => 'product_cat',
                    'field' => 'id',
                    'terms' => $category->term_id,
               ),
          ),
          'fields' => 'ids',
     ) );

     $review_ratings = 0;
     $product_count = 0;

     foreach ( $post_ids as $pId ) :
          $rating = $wpdb->get_var("SELECT SUM(meta_value) FROM $wpdb->commentmeta LEFT JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID WHERE meta_key = 'rating' AND comment_post_ID = $pId AND comment_approved = '1'");
          $review_ratings += $rating;
          $product_count++;
     endforeach;

     $average_category_rating = number_format( $review_ratings/$product_count, 1 );
     echo $average_category_rating;
endforeach;