SQLAlchemy conditional many to many relationship with an additional column

Question

This is the way that I usually use for m2m relationship implementation. (Brought from docs.sqlalchemy.org)

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table,
                    backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

Is there any way for using additional columns at the association_table table?

So it should be like

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id')),
    Column('is_valid', Boolean, default=True)  # Add the validation column
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table,
                    backref="parents")

    # How can I do implement this??
    valid_children = relationship("Child",
                    secondary="and_(association_table.left_id == Parent.id, association_table.right_id == Child.id)"

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

I want to do query depends on is_valid column. How can I modify "secondary" attr in Parent table? Or should I fix the other part?

In this question, time_create column has the same value for all children. But in this case, I need a flag that makes able to retrieve whether this connection is still alive or not.

For example, if you implement a one-on-one chatting, there will be a chatting room consist of two-person, right?
And the table should be like as below:

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id')),
    Column('is_left', Boolean, default=False)  # Whether the user left or not
)

class Match(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    user = relationship("User",
                    secondary=association_table,
                    backref="matches")

    # How can I do implement this??
    exist_user = relationship("User",
                    secondary="and_(association_table.left_id == Parent.id, association_table.right_id == Child.id)"

class User(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    nickname = Column(String, unique=True)

How can I do for this?

Possible duplicate of [SQLAlchemy ManyToMany secondary table with additional fields](https://stackoverflow.com/questions/7417906/sqlalchemy-manytomany-secondary-table-with-additional-fields) — SuperShoot, Jan 11 '19 at 09:46
Also consider why you need a valid flag. If the relationship is not valid, would it be acceptable to just delete the record from your secondary table? Otherwise, use the above with association proxies. — bjdduck, Jan 11 '19 at 16:27
@bjdduck Bc I still need the record after I deleted (or deactivated) the connection. — Henry Kim, Jan 16 '19 at 05:14

SQLAlchemy conditional many to many relationship with an additional column

0 Answers0