1 – The Review Filters addon only works for reviews, not for other post types.
2 – To an extent. What you are asking is to use a ranking value that is calculated based on the ratings of only a selection of reviews that have been assigned to the post. This is not possible to do (at least not easily), since Site Reviews saves the ranking value based on the ratings of all of the reviews assigned to a post.
To query the database for all Clinics that have assigned reviews where the reviews are also assigned to a specific category. The PHP function and SQL query would look something like this:
function get_assigned_posts_sorted_by_date (string $postType, int $termId, int $page = 1, int $perPage = 10) {
global $wpdb;
$perPage = max(1, $perPage);
$offset = max(0, ($page * $perPage) - $perPage);
$sql = "
SELECT p.*
FROM {$wpdb->posts} AS p
INNER JOIN {$wpdb->prefix}glsr_assigned_posts AS apt ON (apt.post_id = p.ID)
INNER JOIN {$wpdb->prefix}glsr_assigned_terms AS att ON (att.rating_id = apt.rating_id)
WHERE 1=1
AND p.post_type = %s
AND p.post_status = 'publish'
AND att.term_id = %d
GROUP BY p.ID
ORDER BY p.post_date DESC
LIMIT %d
OFFSET %d
";
$sql = $wpdb->prepare($sql, $postType, $termId, $perPage, $offset);
return $wpdb->get_results($sql);
}
// Where "clinic" is the post_type and 388 is the term ID of the Site Reviews "Makeup" category
$posts = get_assigned_posts_sorted_by_date('clinic', 388);
And to sort the results by the Clinic’s average rating:
function get_assigned_posts_sorted_by_rating (string $postType, int $termId, int $page = 1, int $perPage = 10) {
global $wpdb;
$perPage = max(1, $perPage);
$offset = max(0, ($page * $perPage) - $perPage);
$sql = "
SELECT p.*, pm.meta_value AS rating
FROM {$wpdb->posts} AS p
INNER JOIN {$wpdb->prefix}glsr_assigned_posts AS apt ON (apt.post_id = p.ID)
INNER JOIN {$wpdb->prefix}glsr_assigned_terms AS att ON (att.rating_id = apt.rating_id)
INNER JOIN {$wpdb->postmeta} AS pm ON (pm.post_id = p.ID)
WHERE 1=1
AND p.post_type = %s
AND p.post_status = 'publish'
AND att.term_id = %d
AND pm.meta_key = '_glsr_average'
GROUP BY p.ID, rating
ORDER BY rating DESC, p.post_date DESC
LIMIT %d
OFFSET %d
";
$sql = $wpdb->prepare($sql, $postType, $termId, $perPage, $offset);
return $wpdb->get_results($sql);
}
// Where "clinic" is the post_type and 388 is the term ID of the Site Reviews "Makeup" category
$posts = get_assigned_posts_sorted_by_rating('clinic', 388);
And to sort the results by the Clinic’s ranking:
function get_assigned_posts_sorted_by_ranking (string $postType, int $termId, int $page = 1, int $perPage = 10) {
global $wpdb;
$perPage = max(1, $perPage);
$offset = max(0, ($page * $perPage) - $perPage);
$sql = "
SELECT p.*, pm.meta_value AS ranking
FROM {$wpdb->posts} AS p
INNER JOIN {$wpdb->prefix}glsr_assigned_posts AS apt ON (apt.post_id = p.ID)
INNER JOIN {$wpdb->prefix}glsr_assigned_terms AS att ON (att.rating_id = apt.rating_id)
INNER JOIN {$wpdb->postmeta} AS pm ON (pm.post_id = p.ID)
WHERE 1=1
AND p.post_type = %s
AND p.post_status = 'publish'
AND att.term_id = %d
AND pm.meta_key = '_glsr_ranking'
GROUP BY p.ID, ranking
ORDER BY ranking DESC, p.post_date DESC
LIMIT %d
OFFSET %d
";
$sql = $wpdb->prepare($sql, $postType, $termId, $perPage, $offset);
return $wpdb->get_results($sql);
}
// Where "clinic" is the post_type and 388 is the term ID of the Site Reviews "Makeup" category
$posts = get_assigned_posts_sorted_by_ranking('clinic', 388);
3 – Using Site Reviews Categories will give you slightly faster SQL queries. However, the Site Reviews SQL queries do not currently support WPML-translated category IDs (unlike assigned posts). This is why you are having problems with translated categories.
I am looking into a fix for this in Site Reviews v7.0.
