I have two db tables: wp_comments and wp_ratings. I'm trying to create a query using $wpdb which has to do the following:
from wp_comments I have to get the comment_ID's where the comment_post_ID is equal to the current post id to get all the ratings from wp_ratings where comment_id is equal to the comment_ID's i got from wp_comments.
wp_comments table is the standard wp table for comments. wp_ratings is custom and looks like this:
wp_ratings:
- id
- date
- comment_id
- user_id
- rating
I'm struggling to determine wether to use JOIN or a subquery to achive this. I'm rather new to writing queries. So I only have a first part of a query so far:
$my_query = $wpdb -> get_results( "SELECT rating FROM wp_ratings WHERE comment_id =( SELECT comment_ID FROM comments WHERE comment_post_ID = " . the_ID () . " ) " );
I want the ratings to calculate (and show) the average value and to show the number of ratings.
EDIT: got the substringpart working:
$my_query = $wpdb -> get_results( "SELECT comment_ID FROM wp_comments WHERE comment_post_ID = $post->ID" );
found a comment somwhere that when you use double quotes, you can insert a variable directly without closing the string!
$wpdb
call to be able to tell you what's wrong.WHERE comment_id =( SELECT
- you needin
:where comment_id in (
. I doubt it makes any difference to the database if you do a join or a subquery like this. It should be easy enough to get the count and average in separate queries if that's good enough, but you can probably do it in a single query with the right syntax. And depending how expensive these are you probably want to cache the results in transients, and / or compute a lot of these at once if that's more efficient.