SQL to delete relationships in a junction table based on other table date-CodePudding

So I have three tables and now I need to remove all posts starting from 7 days old of date. I also need to remove the relationship inside post_category to the category with that post that gets deleted.

Then I need to do a calculation and see if there is no posts in relationship with that category, if so, I will remove the category too.

post(id, name, created_at, updated_at)
category(id, name)
post_category(id, post_id, category_id)

I have the post_category table as a junction table so it can couple together relationships between the two tables post and category. Since the junction table doesn't have a field with created_at it gets a little more harder to solve this issue.

This is how far I've come, and it's not a lot. How can I write this in SQL with the explanation above?

DELETE FROM
    post
WHERE
    created_at < CURRENT_DATE - interval '7 days'

CodePudding user response：

How is your foreign key constraint for the post_id column in post_category defined?

If they are defined as ON DELETE RESTRICT (which is the default) then the sql statement you started with will not work - your dbms will complain that there is still an entry in post_category that references the post you are trying to delete. In this case, you need to first delete the entries from post_category and then you can delete the posts:

WITH posts_to_delete AS (
    SELECT id
      FROM post
     WHERE created_at < CURRENT_DATE - '7 days'::INTERVAL
),
deleted_refs AS (
    DELETE FROM post_category pc
          WHERE pc.post_id IN (SELECT * FROM posts_to_delete)
)
DELETE FROM post p
      WHERE p.id IN (SELECT * FROM posts_to_delete);

If they are defined as ON DELETE CASCADE then all entries in post_category that match a post you are deleting will also be deleted:

DELETE FROM post
      WHERE created_at < CURRENT_DATE - '7 days'::INTERVAL;

Now all posts and their relationships to the category have been deleted. To delete empty categories you can do this:

DELETE FROM category c
      WHERE NOT EXISTS (SELECT 1
                          FROM post_category
                         WHERE category_id = c.id);

Here is a dbfiddle showing how it works.

On another note, you do not need an id column for your junction table. Make a combination key out of the foreign key columns and it will prevent you from accidentally inserting duplicate records.