Absence of Staff Notes in a particular category

data & reporting
,
1

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
  • aaa, 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

5 Likes