0

I have a sql code for wordpress database that use for deleting posts from specific cat id for specific time duration and I always use this code and it is working fine.

DELETE
    a, -- DELETE from posts
    b, -- DELETE from term relationships
    c  -- DELETE from postmeta
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE e.term_id = 2 AND DATEDIFF(NOW(), post_date) > 180;

but the issue is I want to keep those posts from the specific cat id that have another cat id checked. I know my description is confusing you so I make an example : Imagine I want to delete posts from cat id [2] for time more than 180 days but some of these cat id [2] posts are in another cat id for example [1] and I don't want delete these posts. Just delete posts that belong only to cat id [2]. the posts that are in cat id 2 and 1 should not delete. So in the above code I need to add additional code to can keep those posts that are in cat id [1] anyway. Right now the above code delete all posts in cat id [2] and also the posts in cat id [2] and [1] Let me explain this in another way:

I need a correct code that any posts from cat id [1] never and should not be deleted. There are many posts that are in cat id [1] and another cat id that should remain and never delete with this code.

Improve this question
8
  • 1
    Is there a specific reason you're doing this using a raw or direct SQL query than using the relevant WordPress functions?
    – Sally CJ
    Commented Jul 23, 2023 at 8:19
  • @SallyCJ yes I need to delete these posts after months because the database need to clean and also these posts are nofollow and noindex and just need to keep them for a while and some of these posts are important that will add in cat id 1 and should keep for ever
    – akhbarejadid
    Commented Jul 23, 2023 at 8:24
  • You don't necessarily need to use direct SQL to do the database cleaning. You should instead use the WordPress function to properly delete a post and all of its associated data, e.g. meta, revisions and comments. Using that approach will also ensure the post/term/comment/etc. caches are correctly updated.
    – Sally CJ
    Commented Jul 23, 2023 at 12:32
  • 1
    If you have WP-CLI, you could create a custom command and run something like wp your-command --category_id=2. Even without WP-CLI, you could still create a PHP function/script and run it when necessary. But if you insist on using direct SQL, then be prepared to face issues like on the wp-admin page for managing categories, the post count are incorrect (until for example after you created a new post).
    – Sally CJ
    Commented Jul 23, 2023 at 13:56
  • 1
    it's much easier, safer, and more compatible to do this using PHP code, particularly via WP CLI. Note that you're volunteering for bitrot by doing this though, and that a MySQL/MariaDB database is designed to handle millions of rows in a table or more. Direct SQL statements don't flush caches, trigger actions/filters, and can cause problems. E.g. your category post counts will be incorrect after running this query
    – Tom J Nowell ♦
    Commented Jul 23, 2023 at 15:19

1 Answer 1

Reset to default
0

finally I find right code with helping by ChatGPT

DELETE p
FROM wp_posts p
INNER JOIN wp_term_relationships tr1 ON (p.ID = tr1.object_id AND tr1.term_taxonomy_id = 2)
LEFT JOIN wp_term_relationships tr2 ON (p.ID = tr2.object_id AND tr2.term_taxonomy_id = 20)
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND DATEDIFF(NOW(), p.post_date) > 180
AND tr2.term_taxonomy_id IS NULL;

Lets try another way to write new code. consider I want to delete all posts from category ID 2 in time duration more than 180 days except those post that are in category ID 20 too . I mean there are for example 2000 posts in category ID 2 in time duration +180 days and 500 posts of them are in category ID 20 too. so I expect just 1500 posts will delete and keep those 500 posts.

I try this code and it is working fine

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.