查询中每一行的 SQLA lchemy 关系列表

Question

>MODELS：

class Post(Base):
    id = Column(Integer, primary_key=True)
    ...
    topics = relationship('PostTopic', back_populates='post')
class PostTopic(Base):
    post_id = Column(Integer, ForeignKey("post.id"), primary_key=True)
    topic_id = Column(Integer, ForeignKey("topic.id"), primary_key=True)
    topic = relationship('Topic', foreign_keys=topic_id,
                     back_populates="posts_per_topic")
    post = relationship('Post',
                     foreign_keys=post_id,
                     back_populates="topics")
class Topic(Base):
    id = Column(Integer, primary_key=True)
    topic_name = Column(Text)
    posts_per_topic = relationship('PostTopic', back_populates='topic')

我正在尝试从帖子中查询实体列表，并且每个帖子都在一个查询中查询一对多topic_names的列表。

post_bundle = Bundle('post', Post.id, Post.title, Post.image, Post.date_created)
creator_bundle = Bundle('user', User.first_name, User.last_name, User.photo_link, User.user_id)
subject_bundle = Bundle('subject', Subject.icon)
topic_bundle = Bundle('topic', Topic.topic_name)
return request.dbsession.query(post_bundle, creator_bundle, subject_bundle, topic_bundle)
        .join(Post.creator, Post.subject)
        .join("topics", "topic")
        .filter(Post.is_archived == False, Post.is_draft == False)
        .filter(User.is_banned == False)

但是我收到了每个主题的同一帖子的重复。例如。

('post_title_1', 'topic_title_1'),
('post_title_1', 'topic_title_2'),
('post_title_1', 'topic_title_3'),
('post_title_1', 'topic_title_4'),

我想要什么：

('post_title_1', ('topic_title_1', 'topic_title_2', 'topic_title_3', )),    
('post_title_2', ('topic_title_4', 'topic_title_5', 'topic_title_6', )),

Answer 1

您可以使用一个小循环来转换这些结果，如下所示：

法典：

titles = {}
for post, topic in data:
    titles.setdefault(post, []).append(topic)
new_data = list(titles.items())

测试代码：

data = (
    ('post_title_1', 'topic_title_1'),
    ('post_title_1', 'topic_title_2'),
    ('post_title_1', 'topic_title_3'),
    ('post_title_2', 'topic_title_4'),
    ('post_title_2', 'topic_title_5'),
    ('post_title_2', 'topic_title_6'),
)
print(data)
titles = {}
for post, topic in data:
    titles.setdefault(post, []).append(topic)
new_data = list(titles.items())
print(new_data)

结果：

(
    ('post_title_1', 'topic_title_1'), 
    ('post_title_1', 'topic_title_2'), 
    ('post_title_1', 'topic_title_3'), 
    ('post_title_2', 'topic_title_4'), 
    ('post_title_2', 'topic_title_5'), 
    ('post_title_2', 'topic_title_6')
)
[
    ('post_title_1', ['topic_title_1', 'topic_title_2', 'topic_title_3']),
    ('post_title_2', ['topic_title_4', 'topic_title_5', 'topic_title_6'])
]