Searching for historical assignments

Data & reporting
1

Hey there.

We have a workflow on our forum where we assign threads to a group, and once that group has done what they feel they can do with a given thread, they unassign it from themselves. This makes sure those threads fall off the list of threads they are still responsible for.

And, for our own reporting purposes (“what kind of topics typically get assigned to ____ group”) we’d like to be able to search our forum for any instances of a group assignment.

Is there some search syntax I’m unfamiliar with, or any ideas about how to pull such reports? I like to think I’m just missing some syntax, since all the information about group assignments (past and present) are visible when browsing a thread.

1 Like
3

You can extract this data using Data Explorer plugin.

Hope the query below helps you.

1. Lists all topics that are currently assigned to a group
SELECT 
    g.name group_name,
    a.topic_id,
    a.assigned_by_user_id,
    a.created_at
FROM assignments a
INNER JOIN groups g ON g.id = a.assigned_to_id
WHERE assigned_to_type = 'Group'
ORDER BY g.name, created_at DESC
2. Lists the history of assigning topics to groups
SELECT 
    pcf.value AS group,
    p.topic_id,
    p.user_id AS "assigned_by_user_id",
    pcf.created_at::date
FROM posts p
INNER JOIN post_custom_fields pcf ON pcf.post_id = p.id
WHERE action_code = 'assigned_group'
    AND pcf.name = 'action_code_who'
ORDER BY pcf.value, pcf.created_at DESC
4 Likes
6

Wow @michebs – thanks for your help!

We had started to cobble a similar query together by the end of today (finding the post_custom_fields table blew our minds), but yours is much cleaner in the end.

We did notice there were some missing entries when topics had been directly reassigned rather than unassigned and then assigned, so I changed a little bit the WHERE condition to account for this. So finally we landed on:

SELECT 
    pcf.value AS group,
    p.topic_id,
    p.user_id AS "assigned_by_user_id",
    pcf.created_at::date
FROM posts p
INNER JOIN post_custom_fields pcf ON pcf.post_id = p.id
WHERE (action_code = 'assigned_group' OR action_code = 'reassigned_group') 
    AND pcf.name = 'action_code_who'
ORDER BY pcf.value, pcf.created_at DESC

Thanks again for your help. :heart:

3 Likes
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.