如何预先加载可选地存在过滤关系 [英] How to eager load optionally exists filtered relation

问题描述

我正在开发 SQLAlchemy 0.9.8

这个问题与如何在SqlAlchemy中按joinloaded表过滤?"有关，但不一样.>

我有 Article 实体，并且它被 ArticleL10n 实体可选本地化.

Base = declarative_base()类文章(基础):__表名__ = '文章'id = 列(整数，primary_key=True)标题 = 列(字符串(255))l10n = 关系(文章L10n"，collection_class=attribute_mapped_collection('lang'),级联=所有，删除孤儿")类 ArticleL10n(Base):__tablename__ = '文章_10n'article_id = Column(Integer, ForeignKey('article.id'), primary_key=True)lang = Column(String(15), primary_key=True)标题 = 列(字符串(255))

我想通过预先加载特定语言的ArticleL10n来查询Article列表.

我试过了:

session.query(文章)\.outerjoin(Article.l10n) \.options(contains_eager(Article.l10n)) \.filter(ArticleL10n.lang == "en") \.all()

这不起作用，因为如果文章没有 "en" 本地化，它将被过滤掉.

我当前的工作代码是这样的:

session.query(文章)\.outerjoin(ArticleL10n, and_(ArticleL10n.article_id == Article.id, ArticleL10n.lang == "en")) \.options(contains_eager(Article.l10n)) \.all()

这似乎有效.但我不喜欢这个，因为 ArticleL10n.article_id == Article.id 部分看起来是多余的.我相信应该有更好的方法来做到这一点.

我该如何简化?

解决方案

您可以直接使用 relationship 而不是 join 的第一部分的连接条件:

.outerjoin(ArticleL10n, and_(Article.l10n, ArticleL10n.lang == "en"))

I'm working on SQLAlchemy 0.9.8

This question is related to "How to filter by joinloaded table in SqlAlchemy?" but not the same.

I have Article entity, and it's optionally localized by ArticleL10n entity.

Base = declarative_base()

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    l10n = relationship("ArticleL10n",
            collection_class=attribute_mapped_collection('lang'),
            cascade="all, delete-orphan")

class ArticleL10n(Base):
    __tablename__ = 'article_10n'
    article_id = Column(Integer, ForeignKey('article.id'), primary_key=True)
    lang = Column(String(15), primary_key=True)
    title = Column(String(255))

I want to query Article list with eager loading ArticleL10n of specific language.

I've tried:

session.query(Article) \
    .outerjoin(Article.l10n) \
    .options(contains_eager(Article.l10n)) \ 
    .filter(ArticleL10n.lang == "en") \
    .all()

This doesn't work, because if the article does not have "en" localization, it would be filtered out.

My current working code is this:

session.query(Article) \
    .outerjoin(ArticleL10n, and_(ArticleL10n.article_id == Article.id, ArticleL10n.lang == "en")) \
    .options(contains_eager(Article.l10n)) \ 
    .all()

This seems to work. But I don't like this because ArticleL10n.article_id == Article.id part looks like superfluous. I believe there should be a better way to do this.

How can I simplify this?

解决方案

You can use relationship directly instead of the join condition for the first part of your join:

.outerjoin(ArticleL10n, and_(Article.l10n, ArticleL10n.lang == "en"))

这篇关于如何预先加载可选地存在过滤关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！