python - SQLAlchemy渴望加入关系不起作用
问题描述
class Post(Base):
__tablename__ = 'posts'
id = Column(BigInteger, primary_key=True, autoincrement=True)
names = relationship('PostTranslation', backref='posts')
tags = relationship('PostTag', backref='posts')
class Language(Base):
__tablename__ = 'languages'
id = Column(BigInteger, primary_key=True, autoincrement=True)
lang = Column(Unicode(255), nullable=False)
class Tag(Base):
__tablename__ = 'tags'
id = Column(BigInteger, primary_key=True, autoincrement=True)
name = Column(Unicode(255), nullable=False)
class PostTranslation(Base):
__tablename__ = 'post_translation'
id = Column(BigInteger, primary_key=True, autoincrement=True)
post_id = Column(BigInteger, ForeignKey('posts.id'))
language_id = Column(BigInteger, ForeignKey('languages.id'))
title = Column(Unicode(255), nullable=False)
post = relationship('Post', uselist=False)
language = relationship('Language', uselist=False)
class PostTag(Base):
__tablename__ = 'post_tags'
id = Column(BigInteger, primary_key=True, autoincrement=True)
post_id = Column(BigInteger, ForeignKey('posts.id'))
tag_id = Column(BigInteger, ForeignKey('tags.id'))
language_id = Column(BigInteger, ForeignKey('languages.id'))
post = relationship('Post', uselist=False)
language = relationship('Language', uselist=False)
tag = relationship('Tag', uselist=False)
我将表posts
与post_translation
多语言数据库设计分开。
还将表分隔为tags
和post_tags
。
并将 tag_id(FK) 链接到tags
表。
在这种情况下,我必须提取所有包含特定标签名称的帖子。
query = session.query(PostTag).options(
joinedload(PostTag.post).joinedload(Post.names)
).join(Tag).filter(Tag.name == 'python').options(
contains_eager(PostTag.tag)
).all()
for q in query:
print(q.post.tags)
上面的代码完美运行。但是当我记录查询时,
2020-01-30 20:25:45,854 INFO sqlalchemy.engine.base.Engine SELECT tags.id AS tags_id, tags.name AS tags_name, post_tags.id AS post_tags_id, post_tags.post_id AS post_tags_post_id, post_tags.tag_id AS post_tags_tag_id, post_tags.language_id AS post_tags_language_id, post_translation_1.id AS post_translation_1_id, post_translation_1.post_id AS post_translation_1_post_id, post_translation_1.language_id AS post_translation_1_language_id, post_translation_1.title AS post_translation_1_title, posts_1.id AS posts_1_id
FROM post_tags INNER JOIN tags ON tags.id = post_tags.tag_id LEFT OUTER JOIN posts AS posts_1 ON posts_1.id = post_tags.post_id LEFT OUTER JOIN post_translation AS post_translation_1 ON posts_1.id = post_translation_1.post_id
WHERE tags.name = %(name_1)s
2020-01-30 20:25:45,854 INFO sqlalchemy.engine.base.Engine {'name_1': 'python'}
2020-01-30 20:25:45,856 INFO sqlalchemy.engine.base.Engine SELECT post_tags.id AS post_tags_id, post_tags.post_id AS post_tags_post_id, post_tags.tag_id AS post_tags_tag_id, post_tags.language_id AS post_tags_language_id
FROM post_tags
WHERE %(param_1)s = post_tags.post_id
2020-01-30 20:25:45,856 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
[<model.PostTag object at 0x10951a710>, <model.PostTag object at 0x109522b90>]
2020-01-30 20:25:45,857 INFO sqlalchemy.engine.base.Engine SELECT post_tags.id AS post_tags_id, post_tags.post_id AS post_tags_post_id, post_tags.tag_id AS post_tags_tag_id, post_tags.language_id AS post_tags_language_id
FROM post_tags
WHERE %(param_1)s = post_tags.post_id
2020-01-30 20:25:45,857 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
如您所知,访问标签时发生了N+1个问题。( q.post.tags
)
尽管已经加入(并且急切加载)标签表,但我不知道为什么再次发生查询。
这里有什么解决办法吗?
谢谢。
解决方案
解决了
使用子查询解决
from sqlalchemy import func
sub1 = session.query(Tag.id).filter(Tag.name == 'python')
sub2 = session.query(PostTag.post_id).filter(PostTag.tag_id.in_(sub1))
q = session.query(
func.group_concat(PostTranslation.title.distinct()).label('title'),
PostTag.post_id.label('id'),
func.group_concat(Tag.name).label('tags'),
).join(
Tag, Tag.id == PostTag.tag_id
).join(
PostTranslation,
PostTranslation.post_id == PostTag.post_id,
).filter(
PostTag.post_id.in_(sub2),
).group_by(
PostTag.post_id,
PostTranslation.title,
).all()
推荐阅读
- go - 如何在 Kubernetes 上配置分布式哈希表?
- asp.net - System.Net.Http 引用冲突可能是由于 .NET Standard 2.0
- python - 如果它存在于Python中,如何附加类的属性值
- html - 使用邮件功能的 HTML5 表单未在托管服务器上的我的 gmail 帐户中接收邮件
- javascript - showDiv 函数 js 的小错误
- javascript - 如何禁用整个 Primefaces 数据表
- html - 背景父容器较小,图像部分位于外部
- tensorflow - Tensorflow:带有向量条件的 tf.while_loop()
- mysql - 标签的 MySQL 数据库设计
- php - Cakephp 迁移 1.3 到 3.0