Keeping the items of a many-to-many relationship in the order they were inserted

Let's assume we're running a gaming club where we play a bunch of boardgames every meeting and we want to maintain a history of all the games we're playing, using a database tool. We have a table with all the games the players have, from which we chose each time the games we will play:

   >>> g1 = Game("Settlers of Catan")
   >>> g2 = Game("Carcassonne")
   >>> g3 = Game("La Citta")
   >>> g4 = Game("Tikal")

For the next meeting, we decide we want to play at first Settlers and then Tikal:

   >>> meeting = Meeting()
   >>> meeting.games = [g1, g4]

After a bunch of discussions, it is decided to insert La Citta as the second game of the evening:

   >>> meeting.games.insert(1, g3)
   >>> meeting.games
   [<Game "Settlers of Catan">, <Game "La Citta">, <Game "Tikal">]

Now that we have described the expected behavior, let's describe what is needed to accomplish this:

First, we need to use the orderinglist SQLAlchemy plugin, which will maintain the order in which items were inserted (by automatically assigning a value to an attribute of your choice, for example "position" in this recipe). The problem is that you need the position column to be on the intermediary table of the many-to-many relationship, so we can't use the prefab ManyToMany relationship but have to define an intermediate Entity manually instead. Let's call that intermediary entity "GameReference". But since we still want to be able to access the games of a particular meeting directly, and not have to go through an extra relationship each time, we use the AssociationProxy SQLAlchemy plugin which does that for us. In the end, our model looks like this:

from elixir import Entity, OneToMany, ManyToOne
from elixir.fields import Field
from sqlalchemy.ext.associationproxy import AssociationProxy
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.types import Unicode, Integer

class Game(Entity):
    name = Field(Unicode(250))
    def __repr__(self):
        return '<Game "%s"> % self.name

class Meeting(Entity):
    game_references = OneToMany("GameReference", order_by="position", 
                                collection_class=ordering_list('position'))
    games = AssociationProxy("game_references", "game")

class GameReference(Entity):
    def __init__(self, game):
        self.game = game
    meeting = ManyToOne("Meeting")
    game = ManyToOne("Game")
    position = Field(Integer)

Please also note the __init__ method on GameReference. It is required because AssociationProxy needs either a creator argument with a function to create an instance of the intermediate entity from an instance of the target entity OR that the intermediate entity's constructor accepts an instance of the target entity as its only argument. The later option looked cleaner in this case.

See the AssociationProxy documentation and the orderinglist documentation for more details.

---

Recipe contributed by Tiberiu Ichim, with the help and guidance of Gaëtan de Menten.