Wordpress Filter post by product_cat

Question

I’m having trouble filtering posts (product) by product category. Tried several ways, I’m a newbie.

On my archive-product template I’ve come to execute a report (via SQLREPORTS plugin) with this query:

SELECT `post_title` AS Alumno, `comment_author` AS 'Profesor',  `comment_date`  AS 'Fecha', `comment_content` AS 'Nota' 
FROM `xi00_3_comments` 
INNER JOIN `xi00_3_posts` ON `comment_post_ID`=`ID` 
WHERE `comment_author` != 'WooCommerce'
AND DATE(comment_date) BETWEEN DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 MONTH), INTERVAL 0 DAY) AND DATE(NOW())
AND comment_approved = '1'
ORDER BY `xi00_3_comments`.`comment_date` DESC 

It runs just fine and I get all comments from all posts. (my page is filtered by product category by now).

I’ve managed to get the value for the current category: (say value ‘3b’)

   [query_vars] => Array
    (
        [product_cat] => 3b

$category =  get_query_var('product_cat','msg if not set');

Can you help on how should I structure my query to include taxonomy and limit posts to those with a given product category? I"m aware i need to include taxonomy and terms but can't figure it out.

Thank you so much.

Answer 1

You need to make 2 changes :

1. Inner Join posts table(xi00_3_posts) to comments table (xi00_3_comments).
2. Inner Join posts table(xi00_3_posts) to term relationship table (xi00_3_term_relationships), for category selection.

Note I have used 10 as term_taxonomy_id in example below :

SELECT `post_title` AS Alumno, `comment_author` AS 'Profesor',  `comment_date`  AS 'Fecha', `comment_content` AS 'Nota'  FROM `xi00_3_posts`  INNER JOIN `xi00_3_comments` ON `ID`=`comment_post_ID` INNER JOIN `xi00_3_term_relationships` ON (`ID` = `object_id`)  WHERE `comment_author` != 'WooCommerce'  AND ( `term_taxonomy_id` IN (10) )  AND DATE(comment_date) BETWEEN DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 MONTH), INTERVAL 0 DAY) AND DATE(NOW()) AND comment_approved = '1' ORDER BY `xi00_3_comments`.`comment_date` DESC