# 지정된 카테고리 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:** [3월 16, 2025, 11: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:** 2

<div class="post-metadata">

**Author:** ![Alteras](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/alteras/32/582398_2.png) [@Alteras](https://meta.discourse.org/u/Alteras)\
**Post date:** [3월 17, 2025, 12:13오전 UTC](https://meta.discourse.org/t/getting-all-posts-for-a-given-category-id-matching-certain-parameters/357475/2 "2025-03-17T00:13:17Z")

</div>

`WHERE` 절을 확장하여 `topic_id`를 `category_id` 컬럼이 있는 topics 테이블과 대조하여 검색해야 했습니다.

```sql
-- [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 (
        -- Ensure there is NO missing keyword in the post text
        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)._