根据特定参数获取指定类别ID的所有帖子

[JohnMcDonald]

我开发了一个 SQL 查询，可以获取我网站上包含某些关键字的所有帖子，但我希望将帖子限制在特定的类别 ID。鉴于（似乎）category_id 不是“posts”表中的字段之一，有人知道如何做到这一点吗？这是我的查询（我已经运行过并且运行正常，但它会从所有帖子中提取，而我只想要一个类别的帖子）：

-- [params]
-- string_list :first_list_of_keywords
-- null string_list :second_list_of_keywords

WITH FirstFilter AS (
    SELECT *
    FROM posts
    WHERE EXISTS (
        SELECT 1
        FROM UNNEST(ARRAY[:first_list_of_keywords]) AS s(word)
        WHERE POSITION(LOWER(s.word) IN LOWER(posts.raw)) > 0
    )
), SecondFilter AS (
    SELECT *
    FROM posts
    WHERE NOT EXISTS (
        -- 确保帖子文本中没有缺失的关键字
        SELECT 1
        FROM UNNEST(ARRAY[:second_list_of_keywords]) AS t(word)
        WHERE POSITION(LOWER(t.word) IN LOWER(posts.raw)) = 0
    )
)

SELECT p.*
FROM posts p
INNER JOIN FirstFilter f ON p.id = f.id
INNER JOIN SecondFilter s ON p.id = s.id

[Alteras (Steven Chang)]

只需扩展 WHERE 子句，即可根据 topics 表搜索 topic_id，该表将具有 category_id 列。

-- [params]
-- int :category_id
-- string_list :first_list_of_keywords
-- null string_list :second_list_of_keywords

WITH FirstFilter AS (
    SELECT *
    FROM posts
    WHERE topic_id IN (
        SELECT id FROM topics WHERE category_id = :category_id
    ) AND EXISTS (
        SELECT 1
        FROM UNNEST(ARRAY[:first_list_of_keywords]) AS s(word)
        WHERE POSITION(LOWER(s.word) IN LOWER(posts.raw)) > 0
    )
), SecondFilter AS (
    SELECT *
    FROM posts
    WHERE NOT EXISTS (
        -- 确保帖子文本中没有缺失的关键字
        SELECT 1
        FROM UNNEST(ARRAY[:second_list_of_keywords]) AS t(word)
        WHERE POSITION(LOWER(t.word) IN LOWER(posts.raw)) = 0
    )
)

SELECT p.*
FROM posts p
INNER JOIN FirstFilter f ON p.id = f.id
INNER JOIN SecondFilter s ON p.id = s.id

[JohnMcDonald]

谢谢，这个效果很好。