CODEWITHSUNDEEP
pythonfiltersqlalchemymany-to-many
dougalg
dougalg

Reputation: 564

SQLAlchemy: filter many-to-many joinedload

I have the current table setup for a many to many association between "words" and "phrases":

association_table_wp = Table('words_phras', Base.metadata,
                             autoload=True,
                             extend_existing=True)

class Phrase(Base):
    __tablename__ = 'phrases'
    __table_args__ = {'autoload': True}

    def __init__(self, phrase, explanation=None, active=False):
        self.phrase = phrase
        self.explanation = explanation
        self.active = active

class Word(Base):
    __tablename__ = 'words'
    __table_args__ = {'autoload': True}

    def __init__(self, word, word_id=None, active=False):
        self.word = word
        self.word_id = word_id
        self.active = active

Word.phrases = relationship(Phrase,
                            secondary=association_table_wp,
                            backref="words")

Now normally when I want to look up some words and their phrases I use:

words = db_session.query(Word).filter(Word.id.in_(word_ids)).\
             options(joinedload(Word.phrases)).\
             all()

This allows me to access words[i].phrases to access the phrases associated with a given word.

Now, this works fine, but note that "active" property. I want to filter the words' phrases so that only ones with active == True are returned. How can this be done? I have tried several combinations of joins and eager_loading, but none of them have worked as I'd hope.

Thank you very much.

Upvotes: 1

Views: 2921

Answers (1)

Audrius Kažukauskas
Audrius Kažukauskas

Reputation: 13533

I think contains_eager() is what you're looking for:

words = db_session.query(Word).\
    join(Word.phrases).\
    options(contains_eager(Word.phrases)).\
    filter(Word.id.in_(word_ids)).\
    filter(Phrase.active == True)

Upvotes: 5

Related Questions