SQL - Pull products of particular category from a remote WordPress woocommerce db

Question

I connected to the db and successfully pulled products data. Now I want to to display products from specific category. The store is using woocommerce. The landing page is in different location so I can't import wp functions.

Here's what I come up with so far:

SELECT ID, `post_date` ,  `post_title` ,  `post_content` ,  `guid` FROM  `wp_posts` as post

INNER JOIN wp_term_relationships rs ON rs.object_id = post.ID 
INNER JOIN wp_terms t ON t.term_id = rs.term_taxonomy_id 


WHERE  `post_type` =  "product"  
AND  `post_status` =  "publish"     
AND rs.term_taxonomy_id  = 117

ORDER BY post_date DESC LIMIT 5

Nothing happens. If I remove AND rs.term_taxonomy_id = 117 it works as it did before. 5 oldest product appears. Not the products from specific category. What am I missing here?

Answer 1

I check your query and it's working fine without any errors or empty results. But in addition if you remove INNER JOIN from wp_terms table its also working because you are not getting anything from that table and it is not used in WHERE clause also.

SELECT ID, `post_date` ,  `post_title` ,  `post_content` ,  `guid` FROM  `wp_posts` as post

INNER JOIN wp_term_relationships rs ON rs.object_id = post.ID 

WHERE  `post_type` =  "post" //post type post for my example
AND  `post_status` =  "publish"
AND rs.term_taxonomy_id  = 1 //term_taxanomy_id is 1 for my example

ORDER BY post_date DESC LIMIT 5

Answer 2

SELECT 
        `ID`, 
        `post_title`,
        `guid`      
FROM 
        posts 
WHERE 
        post_type='product' and 
        post_status='publish' and 
        ID IN(
                SELECT 
                        object_id 
                FROM 
                        term_relationships 
                WHERE 
                        term_taxonomy_id IN(YOUR_CATEGORY_ID_OR_MULTIPLE_IDS_COMMA_SEPARATED ) and
                        term_taxonomy_id IN(
                                            SELECT 
                                                term_taxonomy_id 
                                            FROM 
                                                term_taxonomy 
                                            where 
                                                taxonomy='product_cat' )
                                           )