SQLAlchemy:筛选多对多joindload

本文介绍了SQLAlchemy:筛选多对多joindload的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我具有用于单词"和短语"之间多对多关联的当前表设置:

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()

这使我可以访问words[i].phrases来访问与给定单词相关的短语.

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

现在，这可以正常工作，但是请注意该活动"属性.我想过滤单词的短语，以便仅返回带有active == True的单词.如何才能做到这一点?我已经尝试了联接和eager_loading的几种组合，但没有一个能如我所愿.

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.

非常感谢您.

推荐答案

我认为 contains_eager() 是您要寻找的东西:

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)