如何获得查询的差异?

Question

class PostTemplate(BaseModel):
content = TextField(unique=True)

class VkGroup(BaseModel):
group_id = IntegerField(unique=True)

class PostTemplateVkGroup(BaseModel):
"""
http://charlesleifer.com/blog/a-tour-of-tagging-schemas-many-to-many-bitmaps-and-more/
"""
group = ForeignKeyField(VkGroup)
post_template = ForeignKeyField(PostTemplate)

def get_posted_templates_for_group(group_id: int) -> Iterable:
"""Get posted templates.
Args:
group_id (int): id группы
"""
queries = (PostTemplate
.select()
.join(PostTemplateVkGroups)
.join(VkGroup)
.where(VkGroup.group_id == group_id))
return queries
all_post_templates = PostTemplate.select()

多对多关系。
对于PostTemplateVkGroup此记录中的每条记录，此记录中的帖子模板将在此记录的组中使用。

all_post_templates = not_posted_templates | posted_templates

我怎样才能得到not_posted_templates？

Answer 1

如果您的数据库支持"EXCEPT"操作，则可以：

all_post_templates = PostTemplate.alias().select()
post_templates = get_posted_templates_for_group(...)
difference = all_post_templates - post_templates

Sqlite 示例：

class Post(Base):
title = TextField()
class Tag(Base):
tag = TextField()
class PostTag(Base):
post = ForeignKeyField(Post)
tag = ForeignKeyField(Tag)
db.create_tables([Post, Tag, PostTag])
data = (
('pa', ('ta1', 'ta2')),
('pb', ('tb1', 'tb2')),
('pc', ()))
for title, tags in data:
post = Post.create(title=title)
for tag in tags:
tag = Tag.create(tag=tag)
PostTag.create(post=post, tag=tag)
# Create some tags that aren't associated with any post.
Tag.create(tag='tx1')
Tag.create(tag='tx2')
pa1_tags = (Tag
.select()
.join(PostTag)
.join(Post)
.where(Post.title == 'pa'))
all_tags = Tag.alias().select()
diff = all_tags - pa1_tags
for t in diff:
print(t.tag)
# Prints
# tb1
# tb2
# tx1
# tx2