SQLAlchemy中有.in_的反向函数吗? [英] Is there a reverse function of .in_ in SQLAlchemy?

问题描述

我正在尝试通过标签"来组织我的博客帖子，这只是一串用逗号分隔的单词.我希望能够选择一个标签，该标签仅显示在标签字符串中某处具有该标签的帖子.

I'm trying to organize my blog posts by "tags", which is just a string of words separated by commas. I want to be able to select a tag which would only show posts that have that tag somewhere in the string of tags.

例如: 帖子1 -标签:世界政治，技术"

For example: Post1 - tags: "world politics, technology"

Post2 -tags:技术"

Post2 -tags: "technology"

选择世界政治"-我只想要Post1.选择技术"，我想要Post1和Post2.

Selecting "world politics" - I only want Post1. Selecting "technology" I want Post1 and Post2.

我正在尝试使用.in_过滤器功能，但到目前为止，它什么也不会选择

I'm trying to use the .in_ filter function, but as of right now it won't select anything

@app.route('/index/<tag>')
def taggedindex(tag):
    posts = Post.query.filter(Post.tags.in_(tag)).all()

如果我使用

posts = Post.query.filter(tag == tag).all()

很明显，它将只选择直接匹配.

It will only select direct matches, obviously.

推荐答案

您当前的模型不在第一正常形式，因为Post.tags不是原子的，或者换句话说，它不包含其域的单个值.这使得对其进行查询更具挑战性.例如，@ blakebjorn提供的解决方案遭受误报.假设您有带有标签"nice, boat"和"ice, cool"的帖子1和2，并且正在查找带有标签"ice"的帖子.谓词tags LIKE '%ice%'也将匹配nice，因此您同时获得帖子1和2:

Your current model is not in 1st normal form, since Post.tags is not atomic, or in other words does not contain a single value of its domain. This makes querying against it more challenging. For example the solution offered by @blakebjorn suffers from false positives. Say you have posts 1 and 2 with tags "nice, boat" and "ice, cool", and you're looking for posts with tag "ice". The predicate tags LIKE '%ice%' will match nice as well, and so you get both posts 1 and 2 as a result:

In [4]: session.add_all([Post(tags="nice,boat"), Post(tags="ice,cool")])

In [5]: session.commit()

In [6]: session.query(Post).filter(Post.tags.like("%ice%")).all()
Out[6]: [<__main__.Post at 0x7f83b27e5b70>, <__main__.Post at 0x7f83b27e5be0>]

正确的解决方案是将标签拆分为单个标签.为了避免重复Post中的其他字段，您必须将标签拆分到它们自己的表中:

The proper solution is to split the tags into single tags. In order to avoid repeating the other fields in Post you must then split tags to their own table:

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode, unique=True)

因为一个帖子可以有很多标签，并且一个标签可以与很多帖子相关，所以您需要一个关联表来将两者连接起来，在SQLAlchemy中也称为辅助"表:

Because a post can have many tags and a tag can be related to many posts, you need an association table to connect the two, also called a "secondary" table in SQLAlchemy:

post_tag = db.Table(
    "post_tag",
    db.Column("post_id", db.ForeignKey("post.id"), primary_key=True),
    db.Column("tag_id", db.ForeignKey("tag.id"), primary_key=True)
)

然后，您可能希望将模型中的这种关系映射为许多关系很多:

You would then probably want to map this relationship in your models as a many to many relationship:

class Post(db.Model):
    ...
    tags = db.relationship("Tag", secondary="post_tag")

该关系可用于查询带有某些标签的帖子:

The relationship can be used to query for posts with certain tag(s):

In [15]: session.query(Post).filter(Post.tags.any(name="ice")).all()
Out[15]: [<__main__.Post at 0x7fb45d48a518>]

In [24]: session.query(Post).filter(Post.tags.any(Tag.name.in_(["boat", "ice"]))).all()
Out[24]: [<__main__.Post at 0x7fb45d3d0470>, <__main__.Post at 0x7fb45d48a518>]

使用关联代理，您可以隐藏事实这些标签不仅是字符串，而且是其自身的模型:

Using an association proxy you can hide the fact that tags are not just strings, but models in their own right:

class Post(db.Model):
    ...
    tag_objects = db.relationship("Tag", secondary="post_tag")
    tags = db.association_proxy("tag_objects", "name",
                                creator=lambda name: Tag(name=name))

关联代理还支持基本功能查询:

In [22]: session.query(Post).filter(Post.tags.any(Tag.name == "ice")).all()
Out[22]: [<__main__.Post at 0x7fb45d48a518>]

和(有点不直观，因为它代理了标量属性):

and (a bit non intuitively, since it proxies to a scalar attribute):

In [23]: session.query(Post).filter(Post.tags == "ice").all()
Out[23]: [<__main__.Post at 0x7fb45d48a518>]

请注意，我们使Tag.name唯一，因此，如果使用现有标签名称，则插入将失败.例如，可以使用唯一对象"模式来解决.

Note that we made Tag.name unique, so inserting will fail if using an existing tag name. This can be solved for example using the "unique object" pattern.

这篇关于SQLAlchemy中有.in_的反向函数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！