Obteniendo todas las publicaciones para un ID de categoría dado que coinciden con ciertos parámetros

[JohnMcDonald]

He desarrollado una consulta SQL que obtiene todas las publicaciones de mi sitio que contienen ciertas palabras clave, pero me gustaría restringir las publicaciones a un ID de categoría específico. ¿Alguien sabe cómo hacerlo dado que (aparentemente) category_id no es uno de los campos de la tabla “posts”? Aquí está mi consulta (que he ejecutado y funciona bien, pero extrae de TODAS las publicaciones y solo quiero publicaciones de una categoría):

-- [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 (
        -- Asegura que NO falte ninguna palabra clave en el texto de la publicación
        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)]

Solo necesitaba extender la cláusula WHERE para buscar el topic_id en la tabla topics, que tendrá la columna 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 (
        -- Asegúrate de que NO falte ninguna palabra clave en el texto de la publicación
        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]

Gracias, esto funcionó.