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

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

קבלת כל הפוסטים עבור מזהה קטגוריה נתונההתאם לפרמטרים מסוימים

קהילה נתונים ודיווח

JohnMcDonald (John McDonald) 17 במרץ,‏ 2025,‏ 5:22pm 3

תודה, זה עבד.

נושא		תגובות	צפיות
User-posted code blocks in a category Data & reporting sql-query	3	405	13 במאי,‏ 2024
Created a topic in a specific category with conditions Data & reporting sql-triggered-badge	0	213	26 בדצמבר,‏ 2022
Categories and their Subcategories using OR Data & reporting sql-query , sql-tutorial	0	508	25 באוגוסט,‏ 2023
Data explorer query: find posts without replies Data & reporting sql-query	4	1027	15 בפברואר,‏ 2021
Need help with an SQL badge query Data & reporting sql-triggered-badge	5	1472	3 בדצמבר,‏ 2019

קבלת כל הפוסטים עבור מזהה קטגוריה נתונההתאם לפרמטרים מסוימים

נושאים קשורים