Foreign Key Columns does not exist

[AnimeshRy]

Describe the bug A clear and concise description of what the bug is. So I've been working with a pretty complicated system. We use a custom sanic wrapper and tortoise for our models, the catch is we don't run migrations, EVER!. We manually write SQL for our tables and just write a similar models based on it in python.

Recently I encountered this bug and don't have any idea how should I go about solving it. So I have a user_roles models which looks similar to this where the app column FK references a master app table which contains a name column as the source_field. Similarly it is done on the role column as well.

The problem is these source_field="name" does not exist as per the ORM when I'm trying to query. The field does exist as when I query the master App or roles table, I get the data.

The user_roles table also fetched the username from the user table correctly but does not in this case.

Why is this behaviour happening ? Is it because I've also added a Foreign Key constraint on the DB level ?

I've tried changing versions and cleaning up database and loading up new data but nothing seems to work here.

Also as the user_roles Table on the DB contains data, when I switch to a TextField() for these two everything starts working great.

    username = fields.ForeignKeyField(
        "{}.{}".format(CONFIG.config.get("NAME"), "User"),
        related_name="roles",
        on_delete=fields.CASCADE,
        source_field="username",
        pk=True,
    )
    app = fields.ForeignKeyField("{}}".format(CONFIG.config.get("NAME"), "Application"), related_name="app", source_field="name")
    role = fields.ForeignKeyField("{}.{}".format(CONFIG.config.get("NAME"), "Role"), related_name="role", source_field="name")
    # app = CustomTextField(index=True)
    # role = CustomTextField(index=True)
    enabled = fields.BooleanField(default=True, null=False)
    permission = fields.TextField(null=True)

This is the actual error I got.

To Reproduce I have a complicated setup so won't be able to share a correct way but I've described the error as clearly as possible

Expected behavior

Normally I would expect this to work as any normal FK would.

[emilycodestar]

> Is it because I've also added a Foreign Key constraint on the DB level ?

Yes i guess, tortoise can only link for tortoise models, not for unknown foreign key. The argument for foreign key it's string, but in fact looks like it working with model. Here some source code: