在多对多关系中搜索项目

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

这个查询的意思是：你需要找出所有的图片，这些图片同时被标记为'A'和'B'，并且还要被标记为'C'或者'D'，但是不能被标记为'E'和'F'。

SELECT Image.key
FROM Image
INNER JOIN TagRelationship
    ON Image.ID = TagRelationship.ImageID
INNER JOIN Tag tag1
    ON TagRelationship.TagID = tag1.ID
INNER JOIN Tag tag2
    ON TagRelationship.TagID = tag2.ID
WHERE tag1.tag
    IN ( 'A' , 'B' )
AND tag2.tag NOT IN ('E', 'F')

GROUP BY Image.key
HAVING COUNT(*) = 2 

UNION

SELECT Image.key
FROM Image
INNER JOIN TagRelationship
    ON Image.ID = TagRelationship.ImageID
INNER JOIN Tag tag1
    ON TagRelationship.TagID = tag1.ID
INNER JOIN Tag tag2
    ON TagRelationship.TagID = tag2.ID
WHERE tag1.tag
   IN ( 'C' , 'D' )
AND tag2.tag NOT IN ('E', 'F')

Answer 2

上半部分是找出那些符合必须标签的词。下半部分则是找出至少要有一个标签的情况。下半部分的查询没有使用GROUP BY，因为我想知道一张图片是否出现了两次。如果出现了两次，那就说明它同时有背景和风景这两个标签。使用ORDER BY count(*)可以让同时带有背景和风景标签的图片排在最前面。所以像“绿色、山脉、背景风景”这样的图片会是最相关的。接下来是“绿色、山脉、背景或风景”的图片。

SELECT Image.key, count(*) AS 'relevance' 
FROM
     (SELECT Image.key
      FROM
        --good image candidates
        (SELECT Image.key
         FROM Image
         WHERE Image.key NOT IN 
            --Bad Images
            (SELECT DISTINCT(Image.key)   --Will reduce size of set, remove duplicates
             FROM Image
             INNER JOIN TagRelationship
                ON Image.ID = TagRelationship.ImageID
             INNER JOIN Tag
                ON TagRelationship.TagID = Tag.ID
              WHERE Tag.tag
                   IN ('digital', 'drawing' )))
    INNER JOIN TagRelationship
        ON Image.ID = TagRelationship.ImageID
    INNER JOIN Tag
        ON TagRelationship.TagID = Tag.ID
    WHERE Tag.tag
           IN ('green', 'mountain')
    GROUP BY Image.key
    HAVING COUNT(*) = count('green', 'mountain')
    --we need green AND mountain

    UNION ALL

    --Get all images with one of the following 2 tags
    SELECT * 
    FROM
        (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 ( 'background' , 'landscape' ))
)
GROUP BY Image.key
ORDER BY relevance DESC

Answer 3

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 (mandatory tags )) = N  /*the number of mandatory tags*/
   AND SUM(Tag.tag IN (optional tags  )) > 0
   AND SUM(Tag.tag IN (prohibited tags)) = 0

更新

上面这个查询的一个更普遍接受的版本（使用CASE表达式将IN条件的布尔结果转换为整数）：

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 (mandatory tags ) THEN 1 ELSE 0 END) = N  /*the number of mandatory tags*/
   AND SUM(CASE WHEN Tag.tag IN (optional tags  ) THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN Tag.tag IN (prohibited tags) 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 (mandatory tags ) THEN 1 END) = N  /*the number of mandatory tags*/
   AND COUNT(CASE WHEN Tag.tag IN (optional tags  ) THEN 1 END) > 0
   AND COUNT(CASE WHEN Tag.tag IN (prohibited tags) THEN 1 END) = 0