CODEWITHSUNDEEP
pythonpython-3.xsqlalchemyrelationship
Mahi
Mahi

Reputation: 21883

Two one-to-many relationships between the same two models

I have a Player class and each player owns X amount of Character instances:

class Player(Model):
    characters = relationship('Character', back_populates='owner')

class Character(Model):
    owner = relationship('Player', back_populates='characters')
    owner_id = Column('player_id', Integer, ForeignKey('player.id'))

But now I want to split these characters into two separate lists, ones which are in the graveyard and the normal characters list:

class Player(Model):
    characters = relationship('Character', back_populates='owner')
    graveyard = relationship('Character', back_populates='owner')

class Character(Model):
    owner = relationship('Player', back_populates='characters')
    owner_id = Column('player_id', Integer, ForeignKey('player.id'))

But as you can see, there's no way for SQLAlchemy to differentiate between the two lists. How can I enable such behaviour?

Upvotes: 2

Views: 477

Answers (1)

Hannu
Hannu

Reputation: 12205

I make a bold assumption there is a way to distinguish whether a character is in graveyard or not. Otherwise this would make no sense. If "Character" table contains information about the location of the character, then what you need is "alternate join" as described here: http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#specifying-alternate-join-conditions

Here is a silly but working example (ignore the gobbledygook with sessions etc, I just copypasted a new model on piece of old code) if I understood your problem right. It will create a model, insert a player, one alive and one dead character and then display them. Is this what you are looking for?

from sqlalchemy import Integer, ForeignKey, String, Column, Boolean, create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import relationship

Base = declarative_base()

engine = create_engine("postgresql://test:test@localhost/test", echo=False)
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)


class Player(Base):
    __tablename__ = 'player'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    characters = relationship('Character', primaryjoin="and_(Character.player_id == Player.id, \
                              Character.alive == True)")
    graveyard = relationship('Character', primaryjoin="and_(Character.player_id == Player.id, \
                              Character.alive == False)")


class Character(Base):
    __tablename__ = 'character'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    alive = Column(Boolean)
    player_id = Column(Integer, ForeignKey('player.id'))


Base.metadata.create_all(engine)

try:
    p = Player(name="foo")
    Session.add(p)
    Session.commit()
except IntegrityError:
    Session.rollback()

try:
    p = Session.query(Player).filter(Player.name == "foo").one()
    c1 = Character(name="c1", alive=True, player_id=p.id)
    c2 = Character(name="c2", alive=False, player_id=p.id)
    Session.add(c1)
    Session.add(c2)
    Session.commit()
except IntegrityError:
    Session.rollback()

player = Session.query(Player).filter(Player.name == "foo").one()

print player
for c in player.characters:
    print c.name

for c in player.graveyard:
    print c.name

Upvotes: 2

Related Questions