How to introduce unique together with conflicting data?

Question

I worte a little qna script for my website and to prevent users from starting a discussion I want every user only to be able to reply once.

class Q(models.Model):
  text = models.TextField()
  user = models.ForeignKeyField('auth.User')

class A(models.Model):
  text = models.TextField()
  user = models.ForeignKeyField('auth.User')
  q = models.ForeignKeyField('Q')
  class Meta:
    unique_together = (('user','q'),)

Now the the migration gives me:

return Database.Cursor.execute(self, query, params)
  django.db.utils.IntegrityError: columns user_id, q_id are not unique

Of course the unique clashes with existing data. What I need to know now is how to tell the migration to delete the conflicting answers. A stupid solution like keeping the first one found would already be a big help. Even better would be a way to compare conflicting A by a custom function.

I'm running Django-1.7 with the new migration system - not South.

Thanks you for your help!

Answer 1

You just need to create a data migration, where you can indeed write a custom function to use any logic you want. See the documentation for the details.

As an example, a data migration that kept only the lowest Answer id (which should be a good proxy for the earliest answer), might look like this:

from django.db import models, migrations

def make_unique(apps, schema_editor):
    A = apps.get_model("yourappname", "A")

    # get all unique pairs of (user, question)
    all_user_qs = A.objects.values_list("user_id", "q_id").distinct()

    # for each pair, delete all but the first
    for user_id, q_id in all_user_qs:
        late_answers = A.objects.filter(user_id=user_id, q_id=q_id).order_by('id')[1:]
        A.objects.filter(id__in=list(late_answers)).delete()

class Migration(migrations.Migration): 

    dependencies = [ 
        ('yourappname', '0001_initial'),
    ]

    operations = [ 
        migrations.RunPython(make_unique), 
    ]

(This is off the top of my head, and is of course destructive, so please just take this as an example. You might want to look into backing up your data before doing all this deleting.)

To recap: Delete the migration you're trying to run and get rid of the unique constraint. Create an empty data migration as described in the documentation. Write a function to delete the non-unique data that currently exists in the database. Then add the unique constraint back in and run makemigrations. Finally, run both migrations with migrate.