I need help with a PostgreSQL query.
I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to represent this: links_link and links_publicreply.
I am to delete aged rows from links_publicreply. That's easy enough:
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';
But some of these are referenced in the links_link table, hence the query fails. The reference in the links_link
table is:
latest_reply = models.ForeignKey
(
'links.Publicreply',
blank=True,
null=True,
on_delete=models.SET_NULL
)
My first question is: How do I delete these foreign key references too?
My hunch is:
begin;
UPDATE links_link SET latest_reply=NULL where latest_reply_id in
(SELECT id FROM links_publicreply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';
commit;
Does this look right?
If you read the update query above closely, you'll notice it sets latest_reply to null wherever it pointed to an aged links_publicreply row.
I don't actually want to do that. I ideally want to set latest_reply to the next available links_publicreply record (associated to the particular links_link
record), which didn't get deleted by the query:
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';
How do I do that? That's my second question.
Tables
PG tables, as described in my Django app's models.py, are as follows:
class Link(models.Model):
description = models.TextField(validators=[MaxLengthValidator(500)])
submitter = models.ForeignKey(User)
submitted_on = models.DateTimeField(auto_now_add=True)
reply_count = models.IntegerField(default=0)
latest_reply = models.ForeignKey
('links.Publicreply', blank=True, null=True, on_delete=models.SET_NULL)
class Publicreply(models.Model):
submitted_by = models.ForeignKey(User)
answer_to = models.ForeignKey(Link)
submitted_on = models.DateTimeField(auto_now_add=True)
description = models.TextField(validators=[MaxLengthValidator(250)])