Django filter on OneToOne field appending "_id" and failing

Question

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)

Possible duplicate: [https://stackoverflow.com/questions/13116130/django-suffix-foreignkey-field-with-id](https://stackoverflow.com/questions/13116130/django-suffix-foreignkey-field-with-id) — Rob Moll, Mar 26 '20 at 00:23
@RobMoll This is different because here Django is not adding "_id" in the database, but rather in the query. I tried adding `db_column=user_id` but after migrating the column is still named `user` in mysql and the issue persists. — Luciano, Mar 26 '20 at 01:43
How does your `CustomUser` model look like? And how does `request.user` look like? Is it the ID of a user? Have you tried retrieving the instance before you query? E.g. `Provider.objects.filter(user=CustomUser.objects.get(pk=request.user))`? You can also use lookups which span relationships like `Provider.objects.filter(user__id=request.user)`. For both assuming you have the ID of the user. — yvesonline, Mar 26 '20 at 09:33
@yvesonline request.user is a `>`, not an id, so those queries don't work. Unfortunately, even `Provider.objects.filter(user__id=request.user.id)` fails with the same error I originally had. Elsewhere I'm able to say `user=request.user` for tables where `user` is a ForeignKey, because Django appends "_id", but it doesn't work with the OneToOne field. I added my `CustomUser` model to the question. — Luciano, Mar 26 '20 at 15:49

score 0 · Accepted Answer · answered Mar 26 '20 at 19:00

Here's how I solved this issue:

Undo all migrations: rm -rf appname/migrations/0* appname/__pycache__ and in mysql: DELETE FROM django_migrations;
Delete the Provider table in mysql: DROP TABLE appname_provider;
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);
Reset migrations for builtin apps: python manage.py migrate --fake
Make migrations for this app: python manage.py makemigrations appname
Fake run the migrations since all the tables are still in the database: python manage.py migrate --fake
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.

Django filter on OneToOne field appending "_id" and failing

1 Answers1