# Searching for historical assignments
**URL:** https://meta.discourse.org/t/searching-for-historical-assignments/228716
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [June 1, 2022, 9:10am UTC](https://meta.discourse.org/t/searching-for-historical-assignments/228716 "2022-06-01T09:10:14Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Colin\_Mueller](https://meta.discourse.org/u/Colin_Mueller)
#### Post date: [June 1, 2022, 9:10am UTC](https://meta.discourse.org/t/searching-for-historical-assignments/228716/1 "2022-06-01T09:10:14Z")
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.
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [June 1, 2022, 6:44pm UTC](https://meta.discourse.org/t/searching-for-historical-assignments/228716/3 "2022-06-01T18:44:07Z")
You can extract this data using [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin.
Hope the query below helps you.
###### 1. Lists all topics that are currently assigned to a group
```sql
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
```sql
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
```
---
### Author:  [@Colin\_Mueller](https://meta.discourse.org/u/Colin_Mueller)
#### Post date: [June 1, 2022, 7:31pm UTC](https://meta.discourse.org/t/searching-for-historical-assignments/228716/6 "2022-06-01T19:31:02Z")
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:
```plaintext
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. ❤
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [July 1, 2022, 7:31pm UTC](https://meta.discourse.org/t/searching-for-historical-assignments/228716/7 "2022-07-01T19:31:08Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.