1

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)])

1 Answer 1

1

Just add an ON DELETE CASCADE option to your foreign key:

ALTER TABLE links_link 
DROP CONSTRAINT constraint_name,   
ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id)
      REFERENCES links_publicreply(id) ON DELETE CASCADE;

Apparently in django the above is translated as:

on_delete=models.CASCADE
5
  • in Django App he should use configure it within a model, or do extra migration May 1, 2016 at 10:11
  • 1
    @MladenUzelac: If I do it the way Mihai recommends, wouldn't that work as well? Or do I need to do it within the model (or do a migration)? Do add how do add it to the model as an answer. May 1, 2016 at 10:32
  • @HassanBaig I`m not familiar with python,but foreign constraint are a staple of dbs. why would you even use a framework for something that any db does native,probably a lot faster?
    – Mihai
    May 1, 2016 at 10:36
  • Mihai, I prefer it your way, but I might additionally need to update my Django model too, to sync with the change in the DB. Thus asking @MladenUzelac whether there's more to the answer than what you've provided. May 1, 2016 at 10:44
  • I had one project with Django so I wasn't able to give you full answer immediately. And glad that my comment lead to a solution. Working with Django and migrations is elegant way to deal with DB. May 1, 2016 at 15:17

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.