过滤MySQL中的项目

Question

Now I'm developing a news website. Each news has a tags. User can filter news by tags. He has three fields(All fields are optional to fill)

1)At least one tag should be in the article

2)Tags that must be in every article

3)Tags that must not be in the articles

Tags linked to an article with table

`news_id`|`tag_id`
    1    |   2
    4    |   1
    4    |   4
    5    |   1
    5    |   5
   ...   |  ...

When user press Save filter I receive tags ids. How can I filter news_id's with one mysql query?

Now, I have this query

SELECT * FROM `app_news_newstag` GROUP BY `news_id` HAVING `tag_id` IN ('1', '4', '5') AND `tag_id` NOT IN ('2')

I suppose that it covers first and third fields, but I've stuck with second

Answer 1

I would suggest using group by and having, like this:

SELECT news_id
FROM `app_news_newstag`
GROUP BY news_id
HAVING sum(tag_id IN ('1', '4', '5')) = 3 AND
       sum(tag_id` IN ('2')) = 0;

The sum finds the number of matches. Assuming no duplicate tags on the articles, then you can just count the matches for each article. In a numeric context, MySQL treats a boolean expression as 1 for true and 0 for false, which is why this works (without a case).

The second condition simply checks that the tag never exists.

Note: if the ids are numeric, then you should not use single quotes for the constants. String constants for numeric comparisons are misleading.