How to config Many-to-many with condition, in Sqlalchemy

Question

I'm use sqlalchemy 0.6.4.

I have 2 classes: Question and Tag, they are many-to-many.

class Question(Base):
    __tablename__ = "questions"

    id = Column(Integer, primary_key=True)
    deleted = Column(Boolean)
    ...
    tags = relationship('Tag', secondary=r_questions_tags)

class Tag(Base):
    __tablename__ = "tags"

    id = Column(BigInteger, primary_key=True)
    questions = relationship('Question', secondary=r_questions_tags)

So, tag.questions will get all the questions belong to a tag.

But now, since the Question has a deleted column, I hope to do like this:

class Tag(Base):
   ...

   # get non-deleted questions
   questions = relationship('Question', secondary=r_questions_tags, 
                           condition='Question.deleted==False')
   # get deleted questions
   deleted_questions = relationship('Question', secondary=r_questions_tags,
                           condition='Question.deleted==True')

But unfortunately, there is no such condition parameter. What can I do now?

Answer 1

You can impose extra conditions on the join by replacing SQLAlchemy's default condition via the secondaryjoin parameter to relationship. Since you're replacing the default (not just adding to it), you'll need to manually re-specify the original condition along with the new one:

from sqlalchemy import sql

# ...

class Tag(Base):
    __tablename__ = "tags"

    id = Column(BigInteger, primary_key=True)

    questions = relationship('Question',
        secondary=r_questions_tags,
        secondaryjoin=sql.and_(
            r_questions_tags.c.question_id == Question.id,
            Question.deleted == False))

    deleted_questions = relationship('Question',
        secondary=r_questions_tags,
        secondaryjoin=sql.and_(
            r_questions_tags.c.question_id == Question.id,
            Question.deleted == True))