Getting All Posts for a Given Category ID Matching Certain Parameters

Data & reporting
1

I have developed an SQL query that obtains all posts in my site that contain certain keywords, but I’d like to restrict the posts to a specific category id. Does anyone know how to do that given that (it appears) category_id is not one of the fields in the “posts” table? Here is my query (which I have run and it works fine but it pulls from ALL posts and I only want posts from one category):

-- [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 (
        -- 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
2

Just needed to extend the WHERE clause to search the topic_id against the topics table, which will have the category_id column.

-- [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
4 Likes
3

Thanks, this worked.

1 Like