Retrieving information about anonymous posts

[agschmid]

Here are example queries for retrieving information about anonymous posts. They will need to be modified for your CD2 instance and you may want to join these tables with other tables if you're going to create a report.

Tables in Canvas

This query returns the topic ID, which is needed to get the discussion entries. The user_id here is the user who created the topic, (in most cases it would be the instructor). The type needs to be null or this query will pull all announcements too.

SELECT discussion_topics_id, message, created_at, updated_at, deleted_at, user_id, workflow_state, title
FROM discussion_topics
WHERE context_id = <course ID>
AND context_type = 'Course'
AND type IS NULL

This query returns the user_id of the person posting to the topic.

SELECT discussion_entries_id, message, created_at, updated_at, deleted_at, user_id, workflow_state, discussion_topic_id, parent_id
FROM discussion_entries
WHERE discussion_topic_id = <discussion_topics_id>

This query returns the user_id and workflow_state of who has read the topic. The workflow_state will be 'read' if the user read it.

SELECT discussion_topic_participants_id, user_id, workflow_state, subscribed, unread_entry_count, discussion_topic_id
FROM discussion_topic_participants
WHERE discussion_topic_id = <discussion_topics_id>

This query returns the user_id and workflow_state of who has read a specific post. The workflow_state will be 'read' if the user read it.

SELECT discussion_entry_participants_id, user_id, workflow_state, forced_read_state, discussion_entry_id
FROM discussion_entry_participants
WHERE discussion_entry_id = <discussion_entries_id>

To retrieve information about the user (using the user_id), join the users and  pseudonyms tables to retrieve name and unique_id.

users table, pseudonyms table