如何在MySQL查询中排除用户已标记屏蔽的文章（基于Toxi标签系统）

Got it, let's figure out how to exclude those articles users don't want to see. Your existing query pulls articles tagged with 'bookmark', 'webservice', or 'semweb'—we just need to add a filter to skip any entries in userExcludedArticles for the target user. Here are two reliable approaches:

方法1：LEFT JOIN + IS NULL #

This method uses a left join to connect your article results with the exclusion table, then filters out any rows that have a matching exclusion entry. It's straightforward and easy to read:

SELECT a.* 
FROM tagmap at
JOIN articles a ON a.id = at.articleID
JOIN tag t ON at.tag_id = t.tag_id
LEFT JOIN userExcludedArticles uea 
  ON a.id = uea.articleID 
  AND uea.userID = [目标用户ID] -- 替换成你要筛选的用户ID
WHERE t.name IN ('bookmark', 'webservice', 'semweb')
  AND uea.id IS NULL -- 只保留没有被排除的文章
GROUP BY a.id

逻辑说明： #

• The LEFT JOIN keeps all articles from your original query, even if they don't have a matching entry in userExcludedArticles.
• The uea.id IS NULL condition filters out any articles that do have a matching exclusion record for the user.
• Don't forget to replace [目标用户ID] with the actual user ID you're targeting—this is critical, otherwise you'll exclude articles marked by any user.

方法2：NOT EXISTS子查询 #

If you prefer a more explicit logical check, using NOT EXISTS makes the intent crystal clear: "only select articles where there's no exclusion entry for this user".

SELECT a.* 
FROM tagmap at
JOIN articles a ON a.id = at.articleID
JOIN tag t ON at.tag_id = t.tag_id
WHERE t.name IN ('bookmark', 'webservice', 'semweb')
  AND NOT EXISTS (
    SELECT 1 
    FROM userExcludedArticles uea
    WHERE uea.articleID = a.id
      AND uea.userID = [目标用户ID] -- 替换成目标用户ID
  )
GROUP BY a.id

逻辑说明： #

• The subquery checks if the current article exists in the user's exclusion list.
• NOT EXISTS returns true only when the subquery finds no matching rows, so those articles are kept.
• This is often optimized well by databases, especially if you have indexes on userExcludedArticles.userID and userExcludedArticles.articleID.

额外注意事项 #

• Indexing: To speed up these queries, consider adding a composite index on userExcludedArticles(userID, articleID)—this will make the exclusion check much faster, especially with large datasets.
• Duplicate Exclusions: If a user has marked the same article multiple times in userExcludedArticles, both methods will still work correctly—they only care if there's at least one exclusion entry.
• GROUP BY: Your original GROUP BY a.id is still necessary to avoid duplicate article rows (since an article can be tagged with multiple entries from your IN list).

内容的提问来源于stack exchange，提问作者Shaun