Migrating data from "Many-To-Many" to "Many-To-Many Through" in django

Question

I've got a model

class Category(models.Model):
    title           = models.CharField(...)
    entry           = models.ManyToManyField(Entry,null=True,blank=True,
                                             related_name='category_entries',
                                             )

That I wish to refactor to have additional data with each relationship:

class Category(models.Model):
    title           = models.CharField(...)
    entry           = models.ManyToManyField(Entry,null=True,blank=True,
                                             related_name='category_entries',
                                             through='CategoryEntry',
                                             )

But south deletes the existing table. How can I preserve the existing m-t-m relationships?

Answer 1

In Django 1.7+ built-in migrations, the way the "code state" (i.e. the code definition of models) is calculated is different, and requires a different solution.

In South (Django pre-1.7), the entire "code state" is saved in each migration — but in Django 1.7+ built-in migrations, it's derived from looking at the whole set of migrations, so you need to specify the "code state" change in a migration without altering the database.

Like above, this will need to be done in a few steps.

1. Create an intermediate model like in the answer above:

   class CategoryEntry(models.Model):
       category = models.ForeignKey(Category, on_delete=models.CASCADE)
       entry = models.ForeignKey(Entry, on_delete=models.CASCADE)   
   
       class Meta:
            db_table = 'main_category_entries'   #change main_ to your application
            unique_together = ('category', 'entry')
   

2. Create an auto-migration with django-admin.py makemigrations and modify the code; move the operations list into the state_operations argument of a migrations.SeparateDatabaseAndState operation, and leave the database_operations list empty. It should look like:

   class Migration(migrations.Migration):
       operations = [
           migrations.SeparateDatabaseAndState(
               state_operations=[ 
                   migrations.CreateModel(CategoryEntry..)
                   ...
               ],
               database_operations=[]
           ),
       ]
   

3. Edit the CategoryEntry to contain what you want and create a new auto-migration with django-admin.py makemigrations

Answer 2

1. Create your intermediate model without any extra fields, for now. Give it a unique constraint to match the existing one and specify the table name to match the existing one:

   class CategoryEntry(models.Model):
       category = models.ForeignKey(Category)
       entry = models.ForeignKey(Entry)   
   
       class Meta:
           db_table='main_category_entries'   #change main_ to your application
           unique_together = (('category', 'entry'))
   

2. Run the South schema migration.

3. Edit the generated schema migration script and comment-out all the forwards and backwards entries, since you'll be re-using the existing intersection table. Add pass to complete the methods.

4. Run the migration.

5. Update any existing code. As it says in https://docs.djangoproject.com/en/dev/topics/db/models/#many-to-many-relationships, "Unlike normal many-to-many fields, you can't use add, create, or assignment to create relationships" so you'll need to modify any existing application code, e.g.

   c.entry.add(e)
   

   could become:

   try:
       categoryentry = c.categoryentry_set.get(entry = e)
   except CategoryEntry.DoesNotExist:
       categoryentry = CategoryEntry(category=c, entry=e)
       categoryentry.save()
   

   and:

   e.category_entries.add(c)
   

   could become:

   categoryentry = CategoryEntry(category=c, entry=e)  #set extra fields here
   categoryentry.save()                
   

   and:

   c.entry.remove(e)
   

   could become:

   categoryentry = c.categoryentry_set.get(entry = e)
   categoryentry.delete()
   

6. Once this initial pseudo migration has been done, you should then be able to add the extra fields to the CategoryEntry and create further migrations as normal.

Answer 3

I'd do it in the following way:

1. Add the CategoryEntry class to the model, and do an auto schema migration. This will add an empty table containing the properties of CategoryEntry. To be noted, the older M2M table remains untouched since through='CategoryEntry' has not yet been added.

2. Do a data migration to copy all data from the existing M2M table to the table created in step 1. To do so, run the datamigration command, and edit methods forward() and backward() in the auto generated migration script accordingly.

3. Now add through='CategoryEntry' part (just the way you wanted), and do a schemamigration. this will drop the old M2M table.

Answer 4

The Django documentation has this exact case as an appplication of the migrations.SeparateDatabaseAndState operation. I did exactly what the documentation said but Django kept throwing an exception saying that the table used for the M2M mapping didn't exist.

I was assigning a table name through the "db_table" attribute of the Meta class of the intermediary model and this was causing the problem (don't know why). I then understood that the SQL code shown in the example in the Django documentation is to change the name assigned by Django to the M2M relationship table in the standard M2M relationship to the new name assigned by Django to the table corresponding to the intermediary model used.

            database_operations=[
            # Old table name from checking with sqlmigrate, new table
            # name from AuthorBook._meta.db_table.
            migrations.RunSQL(
                sql='ALTER TABLE core_book_authors RENAME TO core_authorbook',
                reverse_sql='ALTER TABLE core_authorbook RENAME TO core_book_authors',
            ),
        ],

In this example, "core_book_authors" is the old name and "core_authorbook" is the new name of the M2M relationship table. If you do not include this code in the migration, you won't be able to add extra fields to the intermediary model (and I assume this is the main reason to have a custom M2M relationship) as Django will look for the new table name.

To sum up what I did to change a standard M2M relationship to a custom one using 'through':

1. Created intermediary model without the extra fields (only the two foreign keys) and specified that the M2M relationship was now to be made via that model (using 'through').
2. Run command py manage.py makemigrations. I changed this autogenerated migration to look like the one in the documentation referenced above.
3. Run command py manage.py migrate.
4. Added all the extra fields that I needed in the intermediary model.
5. Run command py manage.py makemigrations.
6. Run command py manage.py migrate.

The table that previously represented the standard M2M relationship will now have a different name and all the new columns. It is important that these columns have a default value if you already had data in the table. I find this to be the most straightforward way to do this WITHOUT losing any data.