python - 在多对多关系中搜索项目

Question

我目前正在编写一个允许存储图像然后标记这些图像的应用程序。我正在使用 Python 和 Peewee ORM (http://charlesleifer.com/docs/peewee/)，它与 ​​Django 的 ORM 非常相似。

我的数据模型如下所示(简化):

class Image(BaseModel):
    key = CharField()

class Tag(BaseModel):
    tag = CharField()

class TagRelationship(BaseModel):
    relImage = ForeignKeyField(Image)
    relTag   = ForeignKeyField(Tag)

现在，我从概念上理解了如何查询具有给定标签集的所有图像:

SELECT Image.key
  FROM Image
INNER JOIN TagRelationship
    ON Image.ID = TagRelationship.ImageID
INNER JOIN Tag
    ON TagRelationship.TagID = Tag.ID
 WHERE Tag.tag
       IN ( 'A' , 'B' )     -- list of multiple tags
GROUP BY Image.key
HAVING COUNT(*) = 2         -- where 2 == the number of tags specified, above

不过，我也希望能够进行更复杂的搜索。具体来说，我希望能够指定“所有标签”列表 - 即图像必须具有要返回的所有指定标签，以及“任何”列表和“无”列表。

编辑:我想稍微澄清一下。具体来说，上面的查询是一个“所有标签”式的查询。它返回具有所有给定标签的图像。我希望能够指定如下内容:“给我所有具有标签(绿色、山脉)、任何一个标签(背景、风景)但没有标签(数字、绘图)的图像”。

现在，理想情况下，我希望这是一个 SQL 查询，因为使用 LIMIT 和 OFFSET 后分页变得非常容易。我实际上有一个实现可以工作，我只需将所有内容加载到 Python 集中，然后使用各种交集运算符。我想知道是否有一种方法可以一次完成所有这些操作？

此外，对于那些感兴趣的人，我已经通过电子邮件向 Peewee 的作者发送了关于如何使用 Peewee 表示上述查询的邮件，他回复了以下解决方案:

Image.select(['key']).group_by('key').join(TagRelationship).join(Tag).where(tag__in=['tag1', 'tag2']).having('count(*) = 2')

或者，一个更短的版本:

Image.filter(tagrelationship_set__relTag__tag__in=['tag1', 'tag2']).group_by(Image).having('count(*) = 2')

提前感谢您的宝贵时间。

Answer 1

SELECT Image.key
  FROM Image
  JOIN TagRelationship
    ON Image.ID = TagRelationship.ImageID
  JOIN Tag
    ON TagRelationship.TagID = Tag.ID
 GROUP BY Image.key
HAVING SUM(Tag.tag IN (<i>mandatory tags</i> )) = <i>N</i>  /*the number of mandatory tags*/
   AND SUM(Tag.tag IN (<i>optional tags</i>  )) > 0
   AND SUM(Tag.tag IN (<i>prohibited tags</i>)) = 0

更新

上述查询的更普遍接受的版本(使用 CASE 表达式将 IN 谓词的 bool 结果转换为整数):

SELECT Image.key
  FROM Image
  JOIN TagRelationship
    ON Image.ID = TagRelationship.ImageID
  JOIN Tag
    ON TagRelationship.TagID = Tag.ID
 GROUP BY Image.key
HAVING SUM(CASE WHEN Tag.tag IN (<i>mandatory tags</i> ) THEN 1 ELSE 0 END) = <i>N</i>  /*the number of mandatory tags*/
   AND SUM(CASE WHEN Tag.tag IN (<i>optional tags</i>  ) THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN Tag.tag IN (<i>prohibited tags</i>) THEN 1 ELSE 0 END) = 0

或使用 COUNT 而不是 SUM:

SELECT Image.key
  FROM Image
  JOIN TagRelationship
    ON Image.ID = TagRelationship.ImageID
  JOIN Tag
    ON TagRelationship.TagID = Tag.ID
 GROUP BY Image.key
HAVING COUNT(CASE WHEN Tag.tag IN (<i>mandatory tags</i> ) THEN 1 END) = <i>N</i>  /*the number of mandatory tags*/
   AND COUNT(CASE WHEN Tag.tag IN (<i>optional tags</i>  ) THEN 1 END) > 0
   AND COUNT(CASE WHEN Tag.tag IN (<i>prohibited tags</i>) THEN 1 END) = 0