aerich aerich upgrade 'Failed to open the referenced table' problem in one-to-many

When I use a one-to-many association, the SQL statement generated by calling 'aerich migrate' does not sort according to the association information, and an exception 'Failed to open the referenced table' will appear. Is it possible to add sorting based on the association information?

Aug 06 '24 10:08 yuWorm

I solved the problem by adding sorting, here is the relevant code

import graphlib
from aerich.migrate import Migrate
from aerich import utils


def get_models_describe_by_order(app: str) -> dict:
    ret = utils.get_models_describe(app=app)
    result = {}
    graph = {}
    for k, v in ret.items():
        graph[k] = []
        fk_fields = v.get("fk_fields", "")
        if len(fk_fields) == 0:
            continue
        for fk_field in fk_fields:
            python_type = fk_field.get("python_type")
            if python_type:
                graph[k].append(python_type)
    ts = graphlib.TopologicalSorter(graph)
    order_res = tuple(ts.static_order())
    for name in order_res:
        result[name] = ret[name]
    return result


async def migrate(cls, name) -> str:
    new_version_content = get_models_describe_by_order(cls.app)
    cls.diff_models(cls._last_version_content, new_version_content)
    cls.diff_models(new_version_content, cls._last_version_content, False)

    cls._merge_operators()

    if not cls.upgrade_operators:
        return ""

    return await cls._generate_diff_py(name)


Migrate.migrate = classmethod(migrate)


def get_cli():
    from aerich.cli import cli

    return cli


cli = get_cli()

__all__ = ["cli"]

Aug 06 '24 10:08 yuWorm

When I use a one-to-many association, the SQL statement generated by calling 'aerich migrate' does not sort according to the association information, and an exception 'Failed to open the referenced table' will appear. Is it possible to add sorting based on the association information?

Cloud you should how to reproduce?

Dec 02 '24 07:12 waketzheng

When I use a one-to-many association, the SQL statement generated by calling 'aerich migrate' does not sort according to the association information, and an exception 'Failed to open the referenced table' will appear. Is it possible to add sorting based on the association information?

Cloud you should how to reproduce?

from tortoise import fields
from database.models.base import BaseTable

class Player(BaseTable):
    uid: int = fields.IntField()
    pid: int = fields.IntField(unique=True)
    nickname: str = fields.CharField(unique=True, max_length=255)
    avatar: str = fields.CharField(max_length=255, null=True)
    profession: str = fields.CharField(max_length=255, null=True)
    hp: int = fields.IntField(default=0)
    mp: int = fields.IntField(default=0)
    equipments: dict = fields.JSONField(default={})
    skills: fields.ManyToManyRelation["PlayerSkill"] = fields.ManyToManyField(
        "models.PlayerSkill", related_name="players", through="player_skills"
    )
    tasks: fields.ManyToManyRelation["PlayerSkill"] = fields.ManyToManyField(
        "models.PlayerTask", related_name="players", through="player_tasks"
    )
    pets: fields.ManyToManyRelation["Pet"] = fields.ManyToManyField(
        "models.Pet", related_name="players", through="player_pets"
    )
    level: int = fields.IntField(default=1)
    title: list[str] = fields.JSONField(default=[])
    exp: int = fields.BigIntField(default=0)
    friends: fields.ManyToManyRelation["Player"] = fields.ManyToManyField(
        "models.Player",
        related_name="friend_of",
        through="player_friends",
        lazy=True,
    )
    attrs: dict = fields.JSONField(default={})
    organize = fields.CharField(max_length=255, null=True)
    team: str = fields.CharField(max_length=255, null=True)
    current_position: str = fields.CharField(max_length=255)
    is_instance_dungeon: bool = fields.BooleanField(null=True, default=False)
    ext_fields: dict = fields.JSONField(default={})

class PlayerSkill(BaseTable):
    name: str = fields.CharField(max_length=255)
    level: int = fields.IntField(default=1)
    exp: int = fields.BigIntField(default=0)
    ext_fields: dict = fields.JSONField(default={})

    class Meta:
        table = "player_skill"

class PlayerTask(BaseTable):
    task = fields.CharField(max_length=255)
    schedule = fields.JSONField(default={})
    status = fields.CharField(max_length=255)
    ext_fields: dict = fields.JSONField(default={})

    class Meta:
        table = "player_task"

class Organize(BaseTable):
    name = fields.CharField(max_length=255, unique=True, description="组织名称")
    desc = fields.TextField(description="组织介绍")
    # 组织的创建者/或者领导者
    leader = fields.ForeignKeyField(
        "models.Player", related_name="organizes", on_delete=fields.CASCADE
    )

# other file
class OrganizeMember(BaseTable):
    oid: int = fields.IntField(description="所属的组织")
    # 组织成员
    player = fields.ForeignKeyField(
        "models.Player", related_name="organize_member", on_delete=fields.CASCADE
    )
    job = fields.CharField(max_length=50, description="成员的职位")

    class Meta:
        table = "organize_member"

Many of my tables are associated with players, and they are defined in different py files. When I call migration, the problem of 'Failed to open the referenced table' will appear

Dec 11 '24 15:12 yuWorm

What's your aerich version and tortoise-orm version?

Dec 11 '24 15:12 waketzheng

What's your aerich version and tortoise-orm version?

my version： tortoise-orm = "0.21.5" aerich = "0.7.2" I have now changed to mogodb, so I may have to wait until I have time to test it again. Now this is the version under poetry.lock in my previous commit

Dec 11 '24 15:12 yuWorm

Can't reproduce by the following code:

Python files

settings.py

DB_URL = "mysql://root:[email protected]:3306/aerich_dev"
print(f'{DB_URL=}')
TORTOISE_ORM = {
    "connections": {"default": DB_URL},
    "apps": {"models": {"models": ["models", "aerich.models"]}},
}

models.py

from tortoise import Model, fields


class BaseTable(Model):
    id = fields.IntField(pk=True)

    class Meta:
        abstract = True


class Pet(BaseTable):
    nickname: str = fields.CharField(max_length=55)


class Player(BaseTable):
    uid: int = fields.IntField()
    pid: int = fields.IntField(unique=True)
    nickname: str = fields.CharField(unique=True, max_length=255)
    avatar: str = fields.CharField(max_length=255, null=True)
    profession: str = fields.CharField(max_length=255, null=True)
    hp: int = fields.IntField(default=0)
    mp: int = fields.IntField(default=0)
    equipments: dict = fields.JSONField(default={})
    skills: fields.ManyToManyRelation["PlayerSkill"] = fields.ManyToManyField(
        "models.PlayerSkill", related_name="players", through="player_skills"
    )
    tasks: fields.ManyToManyRelation["PlayerSkill"] = fields.ManyToManyField(
        "models.PlayerTask", related_name="players", through="player_tasks"
    )
    pets: fields.ManyToManyRelation["Pet"] = fields.ManyToManyField(
        "models.Pet", related_name="players", through="player_pets"
    )
    level: int = fields.IntField(default=1)
    title: list[str] = fields.JSONField(default=[])
    exp: int = fields.BigIntField(default=0)
    friends: fields.ManyToManyRelation["Player"] = fields.ManyToManyField(
        "models.Player",
        related_name="friend_of",
        through="player_friends",
        lazy=True,
    )
    attrs: dict = fields.JSONField(default={})
    organize = fields.CharField(max_length=255, null=True)
    team: str = fields.CharField(max_length=255, null=True)
    current_position: str = fields.CharField(max_length=255)
    is_instance_dungeon: bool = fields.BooleanField(null=True, default=False)
    ext_fields: dict = fields.JSONField(default={})


class PlayerSkill(BaseTable):
    name: str = fields.CharField(max_length=255)
    level: int = fields.IntField(default=1)
    exp: int = fields.BigIntField(default=0)
    ext_fields: dict = fields.JSONField(default={})

    class Meta:
        table = "player_skill"


class PlayerTask(BaseTable):
    task = fields.CharField(max_length=255)
    schedule = fields.JSONField(default={})
    status = fields.CharField(max_length=255)
    ext_fields: dict = fields.JSONField(default={})

    class Meta:
        table = "player_task"


class Organize(BaseTable):
    name = fields.CharField(max_length=255, unique=True, description="组织名称")
    desc = fields.TextField(description="组织介绍")
    # 组织的创建者/或者领导者
    leader = fields.ForeignKeyField(
        "models.Player", related_name="organizes", on_delete=fields.CASCADE
    )


# other file
class OrganizeMember(BaseTable):
    oid: int = fields.IntField(description="所属的组织")
    # 组织成员
    player = fields.ForeignKeyField(
        "models.Player", related_name="organize_member", on_delete=fields.CASCADE
    )
    job = fields.CharField(max_length=50, description="成员的职位")

    class Meta:
        table = "organize_member"

Create db

DROP DATABASE IF EXISTS aerich_dev;
CREATE DATABASE IF NOT EXISTS aerich_dev DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Shell command

aerich init -t settings.TORTOISE_ORM
aerich init-db

Dec 21 '24 17:12 waketzheng

My environment is ubuntu22+Python3.10+tortoise-v0.22.2+dev branch of aerich

Dec 21 '24 17:12 waketzheng