如何在SQLAlchemy中聚合连接表中的不同值？

Here is the schema:

这是架构:

post_tag = Table("post_tag", Base.metadata,
                 Column("post_id", Integer, ForeignKey("post.id")),
                 Column("tag_id ", Integer, ForeignKey("tag.id")))


class Post(Base):
    id = Column(Integer, primary_key=True)
    tags = relationship("Tag", secondary=post_tag, backref="post", cascade="all")
    collection_id = Column(Integer, ForeignKey("collection.id"))


class Tag(Base):
    id = Column(Integer, primary_key=True)
    description = Column("description", UnicodeText, nullable=False, default="")
    post_id = Column(Integer, ForeignKey("post.id"))


class Collection(Base):
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(128), nullable=False) 
    posts = relationship("Post", backref="collection", cascade="all,delete-orphan")

    tags = column_property(select([Tag])
                           .where(and_(Post.collection_id == id, Tag.post_id == Post.id))
                           .correlate_except(Tag))

Basically, Post to Tag is many-to-many and Collection to Post is one-to-many.

基本上,Post to Tag是多对多的,而Collection to Post是一对多的。

I want to Collection.tags return a distinct set of tags of posts in collection.

我想Collection.tags在集合中返回一组不同的帖子标签。

However, I get the following error when I access Collection.tags:

但是,当我访问Collection.tags时,我收到以下错误:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) only a single result allowed for a SELECT that is part of an expression

sqlalchemy.exc.OperationalError:(sqlite3.OperationalError)只有一个结果允许SELECT作为表达式的一部分

EDIT

The SQL its generate

它生成的SQL

SELECT (SELECT tag.id, tag.description, tag.post_id 
FROM tag, post 
WHERE post.collection_id = collection.id AND tag.post_id = post.id) AS anon_1, collection.id AS collection_id, collection.title AS collection_title 
FROM collection 
WHERE collection.id = 1

I believe that post_id = Column(Integer, ForeignKey("post.id")) is wrong as post_id is in post_tag. However, if I change it to post_tag.post_id, it throws AttributeError: 'Table' object has no attribute 'post_id'

我相信post_id = Column(Integer,ForeignKey(“post.id”))是错误的,因为post_id在post_tag中。但是,如果我将其更改为post_tag.post_id,则会抛出AttributeError:'Table'对象没有属性'post_id'

EDIT2

I change it to

我改成了

tags = column_property(select([Tag])
                       .where(and_(Post.collection_id == id, post_tag.c.post_id == Post.id,
                                   post_tag.c.tag_id == Tag.id)))

While this works

虽然这很有效

SELECT tag.id, tag.description, tag.category_id, tag.post_id 
FROM tag, post, post_tag 
WHERE post.collection_id = 1 AND post_tag.post_id = post.id AND post_tag.tag_id = tag.id

but the query generate by SQLAlchemy does not

但是SQLAlchemy生成的查询却没有

SELECT (SELECT tag.id, tag.description, tag.category_id, tag.post_id 
FROM tag, post, post_tag 
WHERE post.collection_id = collection.id AND post_tag.post_id = post.id AND post_tag.tag_id = tag.id) AS anon_1
FROM collection 
WHERE collection.id = 1

1 个解决方案

#1

Instead of a column_property() you need a relationship() with a composite "secondary". A column property is handy for mapping some (scalar) SQL expression as a "column" that is loaded along other attributes. On the other hand you seem to want to map a collection of related Tag objects:

而不是column_property(),您需要一个与复合“辅助”的关系()。列属性可以方便地将某些(标量)SQL表达式映射为沿其他属性加载的“列”。另一方面,您似乎想要映射相关Tag对象的集合:

class Collection(Base):
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(128), nullable=False)
    posts = relationship("Post", backref="collection", cascade="all,delete-orphan")

    tags = relationship(
        "Tag", viewonly=True,
        primaryjoin="Collection.id == Post.collection_id",
        secondary="join(Post, post_tag)",
        secondaryjoin="Tag.id == post_tag.c.tag_id")

If you want to eager load the relationship, a bit like the column property would have, you could default to lazy="join". It's also possible to define the eager load strategy on a per query basis using Query.options():

如果你想加载关系,有点像列属性,你可以默认为lazy =“join”。也可以使用Query.options()在每个查询的基础上定义预先加载策略:

session.query(Collection).\
    options(joinedload(Collection.tags)).\
    all()

Please note that your example has a typo(?) in the definition of the secondary table post_tags. The column tag_id has trailing whitespace in the name.

请注意,您的示例在辅助表post_tags的定义中有拼写错误(?)。列tag_id在名称中有尾随空格。