Question 3266 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

MySql Get WooCommerce order IDs only for multiple comment match

I need a query that returns order IDs that has mentioned in the comments certain word. I have three separate queries I want to merge then into one. So the result will be that I’ll get orders IDs only after a certain date and only if they have in comments more than 2 matches for the searched string.
How do I join them together?

First query getting orders IDs after a certain date

SELECT ID FROM `wp_posts` WHERE `post_date` > '2022-02-01 10:14:50' AND `post_status` = 'wc-completed' AND `post_type` = 'shop_order'

Second that returns only IDs when the comment contains searched string

SELECT comment_post_ID FROM `wp_comments` WHERE `comment_post_ID` in ( SELECT ID FROM `wp_posts` WHERE `post_date` > '2022-02-01 10:14:50' AND `post_status` = 'wc-completed' AND `post_type` = 'shop_order') AND `comment_content` LIKE '%searched string%'

Third return ID only if an order has at least 2 comments with searched string

SELECT comment_post_ID FROM `wp_comments` WHERE `comment_post_ID` = 466416 AND `comment_content` LIKE '%searched string%' having count(*) > 1

Fourth, combining all three previous doesn’t work

SELECT *  FROM `wp_comments` WHERE `comment_post_ID` in ( SELECT ID FROM `wp_posts` WHERE `post_date` > '2022-02-01 10:14:50' AND `post_status` = 'wc-completed' AND `post_type` = 'shop_order' GROUP BY `comment_post_ID`) AND `comment_content` LIKE '%searched string%' having count(*) > 1
Tags:

2

Answers


  1. 0

    Try and struture your SQL for easy reading;

    SELECT C.*  
FROM wp_comments AS C
JOIN wp_posts AS P ON P.ID = C.comment_post_ID
WHERE 
    P.post_date > '2022-02-01 10:14:50' AND 
    P.post_status = 'wc-completed' AND 
    P.post_type = 'shop_order' AND
    C.comment_content LIKE '%na Nová%' //Used stock in my search
having count(*) > 1
    Login or Signup to reply.
  2. 0

    How about a subquery?

    SELECT a.*
FROM wp_comments AS a
WHERE a.comment_post_ID IN (
    SELECT b.ID
    FROM wp_posts AS b
             JOIN wp_comments AS c ON b.ID = c.comment_post_ID
    WHERE b.post_date > '2022-02-01 10:14:50'
      AND b.post_status = 'wc-completed'
      AND b.post_type = 'shop_order'
      AND c.comment_content LIKE '%na Nová%'
    GROUP BY b.ID
    HAVING COUNT(*) >= 2
)
  AND a.comment_content LIKE '%na Nová%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search