How to set custom through table_name without creating a new model?

[maitham]

I'm currently trying to migrate some models away from sqlalchemy and currently have a many to many relation with an association as follows:

 team_testkits = Table(
"team_testkits",
 Base.metadata,  # type: ignore
Column("team_id", ForeignKey("team.id")),
 Column("testkit_id", ForeignKey("testkit.id")),
 )


class Team(Base):
    id = Column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=sqlalchemy.text("uuid_generate_v4()"),
        index=True,
    )
    name = Column(String, nullable=True, index=True)

class Testkit(Base):
    id = Column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=sqlalchemy.text("uuid_generate_v4()"),
        index=True,
    )
    slug = Column(String, index=True)

What does the ormar equivalent look like as I can't customize the association tables name without creating a new model. If I do this I can't put more than one primary_key, postgres treats association tables as having two primary keys.

[igormorgado]

You can use the parameter related_name in ormar.ForeignKey and through in ormar.ManyToMany to specifiy the reference name in the foreign table. Also you can specify the table name in the class Meta inside a ormar.Model.

Something like (adjust naming as wish)

from typing import Optional, List
import ormar as orm
import sqlalchemy as sa
import databases as db

DB_URL = 'sqlite:///test.db'

database: db.Database = db.Database(DB_URL)
metadata: sa.MetaData = sa.MetaData()

class TeamTestKits(orm.Model):
    class Meta:
        tablename = "team_testkits"
        metadata = metadata
        database = db


class TestKit(orm.Model):
	id: int = orm.UUID(primary_key=True)
	slug: str = orm.String(index=True)
	class Meta(orm.ModelMeta):
		metadata = metadata
		database = database
		tablename = 'testkits'


class Team(orm.Model):
    id: int = orm.UUID(primary_key=True)
	name: Optional[str]= orm.String(nullable=True, index=True)
	team_testkits: List[TestKit] = orm.ManyToMany(TestKit, through=TeamTestKits)

	class Meta(orm.ModelMeta):
		metadata = metadata
		database = database
		tablename = 'teams'


async def create():
    engine = sa.create_engine(DB_URL)
    await database.connect()
    metadata.drop_all(engine)
    metadata.create_all(engine)
    await databae.disconnect()


if __name__ == '__main__':
    import asyncio
    asyncio.run(create())

Here I'm not using alembic,.

I think if you completely omit the class TeamTestKits and the parameter through in the Team.team_testkits, ormar will create that relationship automatically. You can set the name of the reverse foreign key using the kwargs related_name.

AFAIK, the automatic name of the through table is classA.name.lower()+s + '_' classB.name.lower()+ s, could not find the canonical reference for that info though.

[collerek]

You can do it like @igormorgado said but that will not help you in your case.
You need a composite primary key (multiple columns in the pk) and ormar does not support this as of now.

There is an issue #137 for it and it's in development but it's a huge change in regard to the scope of the changes, so it will take some more time to finish.

As of now, you should add an additional pk column that ormar will maintain for you and leave the foreign keys out of the pk.