php - 关于多个相关标签和项目关系的 SQL 查询

Question

我在 SO 中搜索了以下一些查询，但找不到与我的搜索匹配的内容，而且我在 IN、INNER 之间有点迷路JOIN 和 LEFT JOIN 来编写它们。

当前数据库关系

<强>1。通过标签获取元素

TAG.url 是从 $_GET 中获取的字符串，因此不是 INT id。

例如，使用 url mydomain/items/tag1+tag2/，我们将有以下请求。

对于一个简单但对于两个或更多标签，以下内容似乎在 mySQL 中不起作用:

SELECT 
    ITEM.id AS 'item_id',
    ITEM.title AS 'item_title',
    ITEM.content AS 'item_content'
  FROM ITEM, ITEM_TAG, TAG
WHERE
  (ITEM.id=ITEM_TAG.id_item AND TAG.id=ITEM_TAG.id_dtag AND TAG.url='tag1')
AND
  (ITEM.id=ITEM_TAG.id_item AND TAG.id=ITEM_TAG.id_dtag AND TAG.url='tag2')
...
AND
  (ITEM.id=ITEM_TAG.id_item AND TAG.id=ITEM_TAG.id_dtag AND TAG.url='tagn')

这个有效:

SELECT 
  ITEM.id AS 'item_id',
  ITEM.title AS 'item_title',
  ITEM.content AS 'item_content'
FROM ITEM
  INNER JOIN TAG TAG1
   ON TAG1.url='tag1'
  INNER JOIN ITEM_TAG ITEM_TAG1
   ON ITEM_TAG1.id_item=ITEM.id AND ITEM_TAG1.id_tag=TAG1.id
  INNER JOIN TAG TAG2
   ON TAG2.url='tag2'
  INNER JOIN ITEM_TAG ITEM_TAG2
   ON ITEM_TAG2.id_item=ITEM.id AND ITEM_TAG2.id_tag=TAG2.id
  ...
  INNER JOIN TAG TAGn
   ON TAG2.url='tagn'
  INNER JOIN ITEM_TAG ITEM_TAGn
   ON ITEM_TAGn.id_item=ITEM.id AND ITEM_TAGn.id_tag=TAGn.id 

在这一点上，是否有比 INNER JOIN 和 ON 更好的方法来做到这一点？

<强>2。获取其他标签的相关标签

这里我想获取标签列表和匹配其他标签的项目数。

例如，让我们想象以下固定装置:

    ITEM_TAG             TAG
id_item | id_tag       id | url
1       | 1            1  | tag1
1       | 2            2  | tag2
1       | 3            3  | tag3
1       | 4            4  | tag4
2       | 1
2       | 2
2       | 3
3       | 1
3       | 2

始终使用以下搜索 mydomain/items/tag1+tag2/。

我希望显示与某些项目有关系的其他标签(如果这些关系存在的话)。

很抱歉，我不知道如何发出将输出以下内容的请求:

    TAG
url    count(id_item)
tag3 | 2
tag4 | 1

如何得到这个结果？

编辑

我尝试了以下似乎有效的方法:

SELECT 
  COUNT(id_item) AS 'nb_item', 
  TAG.url AS 'tag_url'
FROM ITEM_TAG, TAG
WHERE 
 url NOT IN ('tag1','tag2')
 AND id_item IN 
 (
   SELECT id_item FROM ITEM_TAG, TAG 
   WHERE url IN ('tag1','tag2') AND id_tag=id
 )
 AND id_tag=id
GROUP BY TAG.id ORDER BY nb_item DESC

任何对此进行优化的想法或建议将不胜感激。

Answer 1

<强>1。通过标签获取元素

select  i.id
,       i.title
,       i.content
from    item i
join    item_tag it
on      i.id = it.id_item
join    tag t
on      t.id = it.id_tag
where   t.url in ('tag1', 'tag2')

<强>2。获取其他标签的相关标签

select  t2.url
,       count(it2.id_item)
from    tag t1 -- this tag
join    item_tag it1
on      t1.id = it.id_tag
join    item_tag it2 -- other tag for same item
on      it2.item_id = it1.item_id
join    tag t2
on      t2.id = it2.id_tag
where   t1.url in ('tag1', 'tag2')
        and t2.url not in ('tag1', 'tag2')
group by
        t2.url