ManyToMany - circuitsacul/apgorm GitHub Wiki

Note

Nearly every decent ORM supports simple ManyToMany (SQLAlchemy, TortoiseORM, etc.). apgorm supports it to some extent, but it isn't as simple as making a field a ManyToManyField that references another column. There are a few reasons for this:

1. Requiring users to create the middle table allows control over the middle table (you can have more than just the FK fields)
2. Allowing a simple games = apgorm.ManyToMany(Game) would make it difficult for the Game table to have users without breaking static type checking
3. It would be difficult to support both methods; A simple ManyToMany and a more customizable one. If I were to switch to a simpler version, it would come at the cost of being able to control other aspects of it.

Usage

ManyToMany's can be used like this:

class User(apgorm.Model):
    id_ = Serial().field()

    games = apgorm.ManyToMany["Player", "Game"](
        "id_",  # the field on *this* model
        "players.userid",  # the field on the middle table that references users.id_
        "players.gameid",  # the field on the middle table that references games.id_
        "games.id_",  # the field on the final table that is reference by players
    )

    primary_key = (id_)


class Game(apgorm.Model):
    id_ = Serial().field()

    users = apgorm.ManyToMany["Player", "User"](
        "id_", "players.gameid", "players.userid", "users.id_"
    )

    primary_key = (id_)


class Player(apgorm.Model):  # this is the "middle table"
   userid = Int().field()
   gameid = Int().field()

   uidfk = apgorm.ForeignKey(userid, User.id_)  # although not *required*, this is highly recommended
   gidfk = apgorm.ForeignKey(gameid, Game.id_)  # ^^

   primary_key = (userid, gameid,)


class Database(apgorm.Database):
    users = User
    games = Game
    players = Player

Now, the ManyToMany "fields" can be used.

Fetch all of "Circuit"'s games:

circuit = await User.fetch(id_=0)  # just pretend circuit's id is 0
circuits_games = await circuit.games.fetchmany()

Fetch all of the users in a game:

some_game = await Game.fetch()  # just returns the first row found
users = await some_game.users.fectchmany()

Create a game and add Circuit to it:

new_game = await Game().create()  # id_ is a Serial, so postgres gives it a default value
await circuit.games.add(new_game)  # actually just creates a Player. In fact, this even returns the created player

Remove circuit from new_game

await new_game.users.remove(circuit)

Note: This is exactly the same as calling circuit.games.remove(new_game). This method returns list[Player], but if you use ForeignKeys properly that list will always contain exactly one Player.

There is also a working ManyToMany example under examples/.