riddle? MYSQL - LIMIT in subquery using JOIN. Want to delete all posts except minimum 100 posts per category but some posts in several categories

Question

Hi for wordpress with mysql8 (8.0.23):

I want to delete all posts except a minimum of 100 posts per category. So on the frontend, I want any category clicked to show a minimum of 100 posts.

This seems easy except many posts exist in more than one category.

So for category A, if I delete all posts >100, post row number 112 might also exist in category B, but is only post row 80, and therefore gets deleted.

see the riddle?

so using 4 categories in a test, how can I

• delete category A posts >100
• but not <100 in category B
• but not <100 in category C
• but not <100 in category D

For this example below, categories A,B,C,D have IDs (71,72,73,74) which is using LIMIT in a subquery (had to use JOIN because LIMIT is not allowed in a subquery?)

SELECT * FROM wp_posts
 
    JOIN 
    (
        SELECT ID 
        FROM wp_posts 
        WHERE NOT 
        (wp_posts.ID IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (72) ) ) 
        LIMIT 100
    ) d
    ON wp_posts.ID 
    IN (d.ID)
    
    JOIN 
       (
        SELECT ID 
        FROM wp_posts 
        WHERE NOT 
        (wp_posts.ID IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (73) ) ) 
         LIMIT 100
         ) e
        ON wp_posts.ID 
        IN (e.ID)
   
    JOIN 
       (
        SELECT ID 
        FROM wp_posts 
        WHERE NOT 
        (wp_posts.ID IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (74) ) ) 
         LIMIT 100
         ) f
        ON wp_posts.ID 
        IN (f.ID)

 
  WHERE 
        (wp_posts.ID IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
        AND (wp_posts.post_type = 'post' ) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC
LIMIT 99999999
OFFSET 100

it comes up with zero rows

help appreciated!!

Answer 1

solved it.. but not using LIMIT with JOIN.

Thanks to @nbk 's comment I am using ROW_NUMBER() OVER instead. This is allowed in a subquery. I have it here as a "select" statement instead of delete (for testing).

Using four categories here with category IDs 71,72,73 and 74.

SELECT wp_posts.*
FROM  wp_posts 
   
WHERE 

(wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')   
   
AND

-- posts discluded if under 100 most recent per category:
(

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

AND

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (72) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

AND

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (73) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

AND

( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
  FROM wp_posts
  WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (74) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)

)