如何在sqlalchemy中查询无重复的一对多关系

Question

我正试图查询帖子，我想得到所有的评论也

这是我的父和子模型

父母

class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key=True, index=True)
title = Column(String, nullable=False)
content = Column(String, nullable=False)
published = Column(Boolean, default=False)
created_at = Column(TIMESTAMP(timezone=True), nullable=False, default=text("NOW()"))
owner_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
comments = relationship("Comment", backref="post")

class Comment(Base):
__tablename__ = "comments"
id = Column(Integer, primary_key=True, nullable=False)
email = Column(String, nullable=True)
name = Column(String, nullable=True)
content = Column(String, nullable=False)      commented_at=Column(TIMESTAMP(timezone=True),nullable=False,server_default=text("NOW()"))
post_id = Column(Integer, ForeignKey("posts.id", ondelete="CASCADE"), nullable=False)

和im查询join likethis:

posts = db.query(models.Post, models.User, models.Comment).outerjoin(
models.User).order_by(models.Post.owner_id).outerjoin(models.Comment).order_by(models.Comment.post_id).all()

这是结果但是这里它给我2倍的id为1的帖子对于每个评论如果帖子有5条评论它会给我5倍的相同的帖子

{
"Post": {
"title": "string",
"id": 1,
"published": false,
"owner_id": 1,
"content": "string",
"created_at": "2022-01-30T21:49:17.851582+02:00"
},
"User": {
"username": "TheBossmanLab",
"created_at": "2022-01-30T21:47:20.416480+02:00",
"email": "[e-mail]",
"is_super_user": true,
"id": 1,
"password": "$2b$12$UEt0mzRp2qiLweJXwTztFuyWGcAfx6h5UFRE3avf3bt82wLzPZxTC",
"is_admin": true
},
"Comment": {
"commented_at": "2022-05-15T21:59:29.830714+02:00",
"id": 1,
"name": "666",
"email": "[e-mail]",
"content": "granda post",
"post_id": 1
}
},
{
"Post": {
"title": "string",
"id": 1,
"published": false,
"owner_id": 1,
"content": "string",
"created_at": "2022-01-30T21:49:17.851582+02:00"
},
"User": {
"username": "TheBossmanLab",
"created_at": "2022-01-30T21:47:20.416480+02:00",
"email": "[e-mail]",
"is_super_user": true,
"id": 1,
"password": "$2b$12$UEt0mzRp2qiLweJXwTztFuyWGcAfx6h5UFRE3avf3bt82wLzPZxTC",
"is_admin": true
},
"Comment": {
"commented_at": "2022-05-16T11:45:33.501141+02:00",
"id": 2,
"name": "666",
"email": "[e-mail]",
"content": "nqowrbvowrb",
"post_id": 1
}
},

结果:

{
"Post": {
"title": "string",
"id": 1,
"published": false,
"owner_id": 1,
"content": "string",
"created_at": "2022-01-30T21:49:17.851582+02:00"
},
"User": {
"username": "TheBossmanLab",
"created_at": "2022-01-30T21:47:20.416480+02:00",
"email": "[e-mail]",
"is_super_user": true,
"id": 1,
"password": "$2b$12$UEt0mzRp2qiLweJXwTztFuyWGcAfx6h5UFRE3avf3bt82wLzPZxTC",
"is_admin": true
},
"Comments": [{
"commented_at": "2022-05-15T21:59:29.830714+02:00",
"id": 1,
"name": "666",
"email": "[e-mail]",
"content": "granda post",
"post_id": 1}, 
{"commented_at": "2022-05-16T11:45:33.501141+02:00",
"id": 2,
"name": "666",
"email": "[e-mail]",
"content": "nqowrbvowrb",
"post_id": 1
}]
},

我想我把情况解释得太清楚了，但我真的很绝望，我想我被困住了，我想不出任何解决办法，请帮帮我。

Answer 1

根据你的问题，我的理解是你想查询一个帖子列表，其中包括与该帖子相关的User和Comment的信息。

如果是这种情况，那么您可以选择在Post和User(由owner_id引用)之间创建关系，而不是加载所有三个实体

既然你的问题没有提供Post是否可以在没有User的情况下存在的细节，你可以使用以下查询来获取与用户相关的帖子。

from sqlalchemy.orm import joinedload, contains_eager
posts: Post = (
db.query(Post)      
.join(Post.user, User.id == Post.owner_id)
.options(contains_eager(Post.user))
.options(joinedload(Post.comments))
.order_by(Post.id.desc())
.all()
)

如果在Post和User之间创建了关联，那么User.id == Post.owner_id将是冗余的，可以跳过。

注释仅仅是基于关系加载的。只有在没有定义显式关系时才需要连接。

如果您是sqlalchemy的新手，那么contains_eager和joinedload都可能有点棘手。

TLDR版本是，您将使用contains_eager，您已经指示SqlAlchemy使用连接加载数据。在我们的例子中，这将是join(Post.user, User.id == Post.owner_id)，而joinedload只是使用定义的关系来急切加载关联。

1. contains_eager

2. joinedload

更新我看到你提到了Posts到User关系的foreign_key上的nullable约束。在这种情况下，这应该绰绰有余。

posts: Post = (
db.query(Post)            
.options(
joinedload(Post.user),
joinedload(Post.comments),
)
.order_by(Post.id.desc())
.all()
)