tortoise makemigration generated duplicate table

[miss85246]

Describe the bug
makemigration will generate duplicate table：

class Role(BaseApplicationModel):
    name = CharField(max_length=16, description="角色名称")
    description = CharField(max_length=128, default="", blank=True, description="角色描述")
    snap = JSONField(default=dict, description="角色权限快照【{resource:code}】")
    reports = JSONField(default=[], description="用户报表权限")

    permissions = ManyToManyField("models.Permission", related_name="roles", through="auth_role_perms")

    class Meta:
        table = "auth_role"
        table_description = "角色表"

class Permission(BaseModel):
    name = CharField(max_length=64, db_index=True, description="权限名称")
    resource = CharField(max_length=64, unique=True, db_index=True, description="权限资源")
    description = CharField(max_length=128, blank=True, default="", description="权限描述")
    level = IntField(default=0, description="应用等级（大于等于该等级的应用才会展示该权限）")
    allow = JSONField(default=list(allow_opt.keys()), null=True, description="支持操作")

    class Meta:
        table = "auth_permission"
        table_description = "权限表"
        ordering = []


class RolePermission(BaseModel):
    role = ForeignKey("models.Role", related_name="ref_permission", description="角色")
    permission = ForeignKey("models.Permission", related_name="ref_role", to_field="resource", description="权限")
    role_name = CharField(max_length=32, default="", blank=True, description="角色名称【冗余显示用】")
    permission_name = CharField(max_length=32, default="", blank=True, description="权限名称【冗余显示用】")
    mask = IntField(default=0, description="权限掩码")

    class Meta:
        table = "auth_role_perms"
        table_description = "角色权限表"
        unique_together = [("role", "permission")]

there after execute command

tortoise makemigration
tortoise migratesql

and output the sql :

CREATE TABLE "auth_role_perms" (
    "auth_role_id" BIGINT NOT NULL REFERENCES "auth_role" ("id") ON DELETE CASCADE,
    "permission_id" BIGINT NOT NULL REFERENCES "auth_permission" ("id") ON DELETE CASCADE
);
CREATE UNIQUE INDEX "uidx_auth_role_p_auth_ro_ade6c0" ON "auth_role_perms" ("auth_role_id", "permission_id");


CREATE TABLE "auth_role_perms" (
    "id" BIGSERIAL NOT NULL PRIMARY KEY,
    "deleted" BOOL NOT NULL,
    "utime" TIMESTAMPTZ,
    "ctime" TIMESTAMPTZ,
    "system" BOOL NOT NULL,
    "comment" VARCHAR(255),
    "role_name" VARCHAR(32) NOT NULL,
    "permission_name" VARCHAR(32) NOT NULL,
    "mask" INT NOT NULL,
    "permission_id" VARCHAR(64) NOT NULL REFERENCES "auth_permission" ("resource") ON DELETE CASCADE,
    "role_id" BIGINT NOT NULL REFERENCES "auth_role" ("id") ON DELETE CASCADE,
    CONSTRAINT "uid_auth_role_p_role_id_9994fe" UNIQUE ("role_id", "permission_id")
);

The SQL script generated duplicate CREATE TABLE statements for the same table.

To Reproduce
Steps to reproduce the behavior, preferably a small code snippet.

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

[waketzheng]

What is BaseApplicationModel and BaseModel?

[miss85246]

nothing special. they are defined like this:

class BaseModel(Model):
"""
BaseModel, define some common fields
"""
 
id = BigIntField(primary_key=True, description="主键ID")
deleted = BooleanField(default=False, description="是否被删除")
utime = DatetimeField(auto_now=True, null=True, description="更新时间")
ctime = DatetimeField(auto_now_add=True, index=True, null=True, description="创建时间")
system = BooleanField(default=False, description="是否为系统内置数据")
comment = CharField(max_length=255, default="", null=True, description="备注信息")
 
obj = Manager()
 
class Meta:
abstract = True
indexes = [("-utime", "id")]



class BaseApplicationModel(BaseModel):
"""
BaseApplicationModel, additional app field on the BaseModel
"""
 
app = ForeignKey("models.Application", to_field="app_id", on_delete=SET_NULL, null=True, description="应用")
 
class Meta:
abstract = True

here are my two basic models. thanks for your reply!

[panla]

@miss85246

1，Try this and see the effect
permissions = ManyToManyField("models.Permission", related_name="roles", through="models.RolePermission")
2，Your RolePermission model now includes other fields, so you should not continue to use ManyToMany

[miss85246]

@miss85246

1，Try this and see the effect1，试试这个，看看效果 permissions = ManyToManyField("models.Permission", related_name="roles", through="models.RolePermission")permissions = ManyToManyField（“models.许可“，related_name=”角色“，通过=”models。RolePermission“） 2，Your RolePermission model now includes other fields, so you should not continue to use ManyToMany2，你的 RolePermission model 现在包含了其他字段，因此你不应继续使用 ManyToMany

sure, I will try it. but when I use aerich , it's works well, doesn't report any error.

[miss85246]

@waketzheng @panla After reviewing the source code of MigrationAutodetector, I found the root cause of this bug and why the through parameter fails to prevent duplicate table generation.

In MigrationAutodetector._current_state(), Tortoise ORM builds the current project state by iterating over all models.

When scanning the main model (e.g., Role) that defines the ManyToManyField, the ORM automatically and unconditionally triggers an implicit ModelState generation for the M2M intermediate table.

Meanwhile, since we also explicitly declared the RolePermission model in our code, another explicit ModelState is created.

Both states share the exact same db_table name. When new_state is handed over to the OperationGenerator:

operations = OperationGenerator(old_state, new_state).generate(app_labels=[app_labels])

The generator treats them as two separate table requests and naively outputs two distinct CREATE TABLE blocks in the SQL schema output, causing a database-level crash.

I was wondering if it would be feasible to add a deduplication filter inside MigrationAutodetector.changes() right after self._current_state() is fetched? For example, could we scan new_state.models and pop() out any implicitly generated M2M model states whose db_table names conflict with user-defined explicit models?
I completely understand that maintaining this is voluntary, so please take your time. If anyone is willing to look into this or consider a fix, I would be deeply grateful for your time and amazing help! Thank you so much for this wonderful project.