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

**URL:** <https://meta.discourse.org/t/getting-all-posts-for-a-given-category-id-matching-certain-parameters/357475>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2025年三月16日 23:17 UTC](https://meta.discourse.org/t/getting-all-posts-for-a-given-category-id-matching-certain-parameters/357475 "2025-03-16T23:17:39Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![JohnMcDonald](https://avatars.discourse-cdn.com/v4/letter/j/8edcca/32.png) [@JohnMcDonald](https://meta.discourse.org/u/JohnMcDonald)\
**Post date:** [2025年三月16日 23:17 UTC](https://meta.discourse.org/t/getting-all-posts-for-a-given-category-id-matching-certain-parameters/357475/1 "2025-03-16T23:17:39Z")

</div>

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

```plaintext
-- [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

```

---

_[View the full topic](https://meta.discourse.org/t/getting-all-posts-for-a-given-category-id-matching-certain-parameters/357475)._