Unable to use ManyToManyField if OneToOneField passed as Primary Key

[kaushal-goswami-godeskless]

I don't know if its a issue or something else.

I have a table in which there is no id field i am using OneToOneField as primary key. Everything working but when i use another field with ManyToManyField i am continuously getting this error

tortoise.exceptions.OperationalError: syntax error at or near "None".

Somehow this looks like ManyToManyField is trying to override the primary_key = True of OneToOneField.

Below is my Tortoise ORM Model:

class UserProfile(Model):
    class Gender(Enum):
        MALE = 'Male'
        FEMALE = 'Female'
        OTHERS = 'Others'

        @classmethod
        def as_tuple(cls):
            return [(item.value, item.name.replace('_', ' ')) for item in cls]

    user: fields.OneToOneRelation[User] = fields.OneToOneField(
        'models.User', 
        on_delete=fields.CASCADE, 
        related_name='user_profile',
        primary_key=True,
    )
    address: fields.ManyToManyRelation[Address] = fields.ManyToManyField(
        'models.Address',
        related_name='user_profile_address',
        null = True,
    )
    gender = fields.CharEnumField(Gender, max_length=20, null=True)

Here is my Django ORM Model:

class UserProfile(models.Model):
    """
    Base Class for storing User Profile related information.
    This class hold one to one relation from auth user table
    """

    class Gender(Enum):
        MALE = 'Male'
        FEMALE = 'Female'
        OTHERS = 'Others'

        @classmethod
        def as_tuple(cls):
            return ((item.value, item.name.replace('_', ' ')) for item in cls)

    user = models.OneToOneField(User, primary_key=True,on_delete=models.CASCADE)
    address = models.ManyToManyField(Address, blank=True)
    gender = models.CharField(null=True, max_length=20, choices=Gender.as_tuple())

Please help i am not able to understand whats wrong, My problem is i cannot change anything at table schema level because everything is working fine in Django. If i remove the primary_key=True the OneToOneField and ManyToManyField works. But i am unable to save it because it says tortoise.exceptions.OperationalError: column "id" of relation "user_profile" does not exist

[waketzheng]

Seems that it's a bug cause by combine one2one primary key with many2many field, one file style to reproduce:

from __future__ import annotations

from enum import Enum

from tortoise import Model, fields, run_async
from tortoise.contrib.test import init_memory_sqlite

class User(Model):
    name = fields.CharField(max_length=10)

class UserProfile(Model):
    class Gender(Enum):
        MALE = "Male"
        FEMALE = "Female"
        OTHERS = "Others"

        @classmethod
        def as_tuple(cls) -> list[tuple[str, str]]:
            return [(item.value, item.name.replace("_", " ")) for item in cls]

    user: fields.OneToOneRelation[User] = fields.OneToOneField(
        "models.User",
        on_delete=fields.CASCADE,
        related_name="user_profile",
        primary_key=True,
    )
    address: fields.ManyToManyRelation[Address] = fields.ManyToManyField(
        "models.Address",
        related_name="user_profile_address",
        null=True,
    )
    gender = fields.CharEnumField(Gender, max_length=20, null=True)

class Address(Model):
    name = fields.CharField(max_length=10)

    user_profile_address: fields.ReverseRelation[UserProfile]

@init_memory_sqlite
async def run() -> None:
    user = await User.create(name="hero")
    profile = await UserProfile.create(user=user, gender=UserProfile.Gender.MALE)
    print(dict(profile))
    print(await profile.address.all())
    address = await Address.create(name="sea")
    await profile.address.add(address)

run_async(run())

Error info:

{'gender': <Gender.MALE: 'Male'>, 'user_id': 1}
[]
Traceback (most recent call last):
  File "/Users/mac10.12/github/tortoise-orm/tortoise/backends/sqlite/client.py", line 44, in translate_exceptions_
    return await func(self, query, *args)
  File "/Users/mac10.12/github/tortoise-orm/tortoise/backends/sqlite/client.py", line 151, in execute_query
    rows = await connection.execute_fetchall(query, values)
  File "/Users/mac10.12/Library/Caches/pypoetry/virtualenvs/tortoise-orm-iT379CwK-py3.8/lib/python3.8/site-packages/aiosqlite/core.py", line 212, in execute_fetchall
    return await self._execute(self._execute_fetchall, sql, parameters)
  File "/Users/mac10.12/Library/Caches/pypoetry/virtualenvs/tortoise-orm-iT379CwK-py3.8/lib/python3.8/site-packages/aiosqlite/core.py", line 132, in _execute
    return await future
  File "/Users/mac10.12/Library/Caches/pypoetry/virtualenvs/tortoise-orm-iT379CwK-py3.8/lib/python3.8/site-packages/aiosqlite/core.py", line 115, in run
    result = function()
  File "/Users/mac10.12/Library/Caches/pypoetry/virtualenvs/tortoise-orm-iT379CwK-py3.8/lib/python3.8/site-packages/aiosqlite/core.py", line 93, in _execute_fetchall
    cursor = self._conn.execute(sql, parameters)
sqlite3.OperationalError: foreign key mismatch - "userprofile_address" referencing "userprofile"

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "run.py", line 53, in <module>
    run_async(run())
  File "/Users/mac10.12/github/tortoise-orm/tortoise/__init__.py", line 648, in run_async
    loop.run_until_complete(coro)
  File "/usr/local/opt/python@3.8/Frameworks/Python.framework/Versions/3.8/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "/Users/mac10.12/github/tortoise-orm/tortoise/contrib/test/__init__.py", line 490, in runner
    return await func(*args, **kwargs)
  File "run.py", line 50, in run
    await profile.address.add(address)
  File "/Users/mac10.12/github/tortoise-orm/tortoise/fields/relational.py", line 197, in add
    await db.execute_query(str(query))
  File "/Users/mac10.12/github/tortoise-orm/tortoise/backends/sqlite/client.py", line 46, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: foreign key mismatch - "userprofile_address" referencing "userprofile"

[waketzheng]

many2many through table schema is:

CREATE TABLE IF NOT EXISTS "userprofile_address" (
    "userprofile_id" None NOT NULL REFERENCES "userprofile" ("user") ON DELETE CASCADE,
    "address_id" INT NOT NULL REFERENCES "address" ("id") ON DELETE CASCADE
);

Point: "userprofile_id" field type should be INT, but got None