Django filter on OneToOne field appending “_id” and failing

In my Django application I have the following model:

class Provider(models.Model):
    user = models.OneToOneField(CustomUser, on_delete=models.CASCADE, primary_key=True)
    company = models.ForeignKey(Company, on_delete=models.CASCADE)

    def __str__(self):
        return str(self.user)

Note user is a OneToOneField to my CustomUser table and also the primary key of this table.

In views.py, I attempt to query this table with Provider.objects.filter(user=request.user) and get django.db.utils.OperationalError: (1054, "Unknown column 'appname_provider.user_id' in 'field list'"). Checking my MySql database, I see the columns are (user, company_id). So why is Django adding “_id” to user when I try to filter?

My CustomUser model:

class CustomUser(AbstractUser):
    is_provider = models.BooleanField()
    is_admin = models.BooleanField()
    first_name = models.CharField(max_length=128)
    last_name = models.CharField(max_length=128)
    email = models.EmailField(unique=True)
    REQUIRED_FIELDS = ["is_provider", "email"]
    objects = CustomUserManager()

    def __str__(self):
        return str(self.username)

Answer

Here’s how I solved this issue:

1. Undo all migrations: rm -rf appname/migrations/0* appname/__pycache__ and in mysql: DELETE FROM django_migrations;
2. Delete the Provider table in mysql: DROP TABLE appname_provider;
3. Recreate the Provider table in mysql with column user_id instead of user: CREATE TABLE appname_provider (user_id INT PRIMARY KEY, company_id INT);
4. Reset migrations for builtin apps: python manage.py migrate --fake
5. Make migrations for this app: python manage.py makemigrations appname
6. Fake run the migrations since all the tables are still in the database: python manage.py migrate --fake
7. I then needed to make company_id a foreign key because I hadn’t done that in step 3, so in Provider I did company = models.IntegerField(), made and ran migrations, then set it back to company = models.ForeignKey(Company, on_delete=models.CASCADE), made and ran migrations again. This is a bit hacky, but without this change back and forth, Django did not detect that it had to make company_id a foreign key.

Now Provider contains a column user_id in the database instead of user, so the query works! Note with this approach you lose any data in the Provider table, but the rest of the database is preserved. I got some of the steps from this answer: https://stackoverflow.com/a/29898483/7632019.