如何约束三个表之间的标签

I'm working on a tags system, what it should do is you can have a query and selected tags, e.g. jquery and tags of javascript, library. It should only show related scripts with the query AND only that have the tags. This is the database layout:

我正在做一个标签系统，它应该做的是你可以有一个查询和选择的标签，例如jquery和javascript的标签，库。它应该只显示与查询相关的脚本，并且只显示有标记的脚本。这是数据库布局:

Scripts table:

脚本表:

+-----------+---------------+
| script_id | name          |
+-----------+---------------+
|         1 | jQuery        |
|         2 | Sencha Touch  |
|         3 | Codeigniter   |
|         4 | Google Chrome |
|         5 | memcached     |
|         6 | PHP           |
|         7 | MooTools      |
|         8 | node.js       |
|         9 | jQuery Mobile |
+-----------+---------------+

Tags table:

标签表:

+--------+-------------+-------------+
| tag_id | name        | url_name    |
+--------+-------------+-------------+
|      1 | JavaScript  | javascript  |
|      2 | Library     | library     |
|      3 | PHP         | php         |
|      4 | MySQL       | mysql       |
|      5 | Cache       | cache       |
|      6 | HTML        | html        |
|      7 | Open source | open-source |
+--------+-------------+-------------+

Tagged table:

标记表:

+-----------+--------+-----------+
| tagged_id | tag_id | script_id |
+-----------+--------+-----------+
|         1 |      1 |         1 |  # javascript -- jQuery
|         2 |      2 |         1 |  # library    -- jQuery
|         3 |      1 |         9 |  # javascript -- jQuery mobile
+-----------+--------+-----------+

When I run my SQL, it still picks up jQuery Mobile but it shouldn't because it doesn't contain the library tag where jQuery does, I need it to constrain results that must meet the tags selected.

当我运行SQL时，它仍然选择jQuery Mobile，但它不应该这样做，因为它不像jQuery那样包含库标记，我需要它来约束必须满足所选标记的结果。

This is my SQL:

这是我的SQL:

SELECT scripts.script_id,
       scripts.name
FROM
(
    scripts scripts

    LEFT OUTER JOIN tagged tagged ON tagged.script_id = scripts.script_id

    LEFT OUTER JOIN tags tags ON tags.tag_id = tagged.tag_id
)
WHERE MATCH(scripts.name) AGAINST ('jquery*' IN BOOLEAN MODE) AND ( tags.url_name = 'javascript' OR tags.url_name = 'library' )
GROUP BY script_id
ORDER BY scripts.name
LIMIT 0, 25

It returns:

它返回:

+-----------+---------------+
| script_id | name          |
+-----------+---------------+
|         1 | jQuery        |
|         9 | jQuery Mobile |
+-----------+---------------+

If I change OR to AND, it won't return anything at all or if I remove the brackets from the tags ( and ), it won't return anything too.

如果我更改或改为AND，它将不会返回任何内容，或者如果我从标记(AND)中删除括号，它也不会返回任何内容。

How do I make the query constrain the tags?

如何使查询约束标签?

4 个解决方案

#1

Give this a try:

给这一个尝试:

select s.script_id, s.name from scripts s
join tagged tj on s.script_id = tj.script_id
join tags t on t.tag_id = tj.tag_id
where s.name like 'jQuery%' and t.url_name in ('javascript', 'library')
group by s.script_id, s.name
having count(*) = 2
order by s.name
limit 25

The only consideration with this is that the 2 must be replaced with the amount of elements in the in clause.

唯一要考虑的是，必须用in子句中的元素数量替换2。

Here is the fiddle.

这是小提琴。

#2

The main problem with your solution is that it creates multiple row when joined together, but each row has only one tag value. If you use the OR condition, then any of the matched tag is enough to return. With an AND no row can have two different value.

您的解决方案的主要问题是在连接到一起时创建多个行，但是每一行只有一个标记值。如果您使用OR条件，那么任何匹配的标记都足以返回。有和无行可以有两个不同的值。

Until you group them together, three separate row is generated:

在将它们组合在一起之前，会生成三个单独的行:

+-----------+-----------+---------------+
|     tag_id| script_id | name          |
+-----------+-----------+---------------+
|         1 |         1 | jQuery        |
|         2 |         1 | jQuery        |
|         1 |         9 | jQuery Mobile |
+-----------+-----------+---------------+

To solve this, you have to use the OR condition, but after the group, use

要解决这个问题，您必须使用OR条件，但是组之后，使用

HAVING count(tag_id) = 2

This tells the sql, to evaluate each group based on how many tags they have, and leave only those which have exactly 2 tags.

这告诉sql，根据每个组有多少个标记来计算每个组，只留下那些只有2个标记的。

So the solution based on your code:

基于你的代码的解决方案:

SELECT scripts.script_id,
       scripts.name
FROM
(
    scripts scripts

    LEFT OUTER JOIN tagged tagged ON tagged.script_id = scripts.script_id

    LEFT OUTER JOIN tags tags ON tags.tag_id = tagged.tag_id
)
WHERE MATCH(scripts.name) AGAINST ('jquery*' IN BOOLEAN MODE) AND ( tags.url_name = 'javascript' OR tags.url_name = 'library' )
GROUP BY script_id
HAVING count(tag_id) = 2
ORDER BY scripts.name
LIMIT 0, 25

#3

SELECT s.script_id,
       s.name
FROM
        scripts AS s
    INNER JOIN 
        tagged AS st1
      ON  st1.script_id = s.script_id
    INNER JOIN tags AS tag1
      ON  tag1.tag_id = st1.tag_id
      AND tag1.url_name = 'javascript'
    INNER JOIN 
        tagged AS st2
      ON  st2.script_id = s.script_id
    INNER JOIN tags AS tag2
      ON  tag2.tag_id = st2.tag_id
      AND tag2.url_name = 'library'
ORDER BY s.name
LIMIT 0, 25

#4

My attempt:

我的尝试:

SELECT s.script_id, s.name, so_tags.url_name 
  FROM so_scripts s 
       LEFT JOIN so_tagged USING (script_id) 
       LEFT JOIN so_tags USING (tag_id)
  WHERE s.script_id IN 
  (
    SELECT td.script_id FROM so_tagged td 
      LEFT JOIN so_tags t USING (tag_id) 
    WHERE t.url_name = "javascript" OR t.url_name = "library"
  ) 
GROUP BY url_name HAVING COUNT(s.name) = 2

It's a bit of a hack, it first looks for all entries matching any of the tags, then groups by name and returns only ones those entries who have number of matches equal to number of tags supplied :)

它有点像黑客，它首先查找匹配任何标记的所有条目，然后按名称分组，然后只返回那些有数量匹配的条目，等于提供的标签数量:)

Not sure how fast it would be for 3, 4 or more tags, but at least it works :)

不知道对于3、4或更多的标签有多快，但至少它可以工作:)

#1