Here is a use-case I solved with the query you’ll find below.
I have a special category where users create their “personal pages” (for their goat farms).
Now we’re creating a map with all these farms placed on it. Once a farm is put on the map, a particular staff note is attached to the user owning a corresponding personal page for the farm.
Now, I need a query to detect all topics in that special category for which no staff note can be found attached to the topics’ authors.
WITH kozowners AS ( SELECT user_id, t.id as topic_id FROM topics t LEFT JOIN users u ON u.id = t.user_id WHERE category_id = ( SELECT id FROM categories WHERE name = 'ABC' LIMIT 1 ) AND username NOT IN ('aaa', 'bbb') ), users_on_map AS ( SELECT substring(key from 'notes:#"%#"' for '#')::int as user_id FROM plugin_store_rows WHERE plugin_name = 'staff_notes' AND value ILIKE '%your_match_string_or_regex%' ) SELECT k.user_id, k.topic_id as topic_id FROM kozowners k LEFT JOIN users_on_map m ON m.user_id = k.user_id WHERE m.user_id IS NULL ORDER BY k.user_id
ABC- category name
bbb- usernames to exclude from the final list
%your_match_string_or_regex%- a string the staff note should match, for example:
%added to the farms map%
This might be useful for teams which use staff notes actively