Notes, Annotations, and Events

Expand or collapse content 1. List of User Comments (for Notes, Annotations, and Events)

USE CASE:

  • Retrieve the list of User comments
  • This query will return all comments for Notes, Annotations, and Events
SELECT uc.element_id As ElementID, de.name AS Element_Name, de.type AS Element_Type, sv.value_display_name As Dimension_value, uc.scope AS First_comment_type, un.text AS First_comment, u_un.username AS First_comment_user, un.created_time As First_comment_time, uc.text AS Second_comment, u_uc.username AS Second_comment_user, uc.last_updated_time AS Second_comment_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN user_note AS un ON(uc.user_note_id=un.user_note_id)
JOIN user AS u_un ON(un.user_id=u_un.user_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='note' 

UNION

SELECT uc.element_id, de.name, de.type, sv.value_display_name,  uc.scope, ua.annotation_text, u_ua.username, ua.annotation_time, uc.text, u_uc.username, uc.last_updated_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN user_annotation AS ua ON(uc.user_annotation_id=ua.user_annotation_id)
JOIN user AS u_ua ON(ua.user_id=u_ua.user_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='annotation'

UNION

SELECT uc.element_id, de.name, de.type, sv.value_display_name, uc.scope, ne.name, ne.last_updated_by, ne.last_notable_event_activity_time, uc.text, u_uc.username, uc.last_updated_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN notable_event AS ne ON(uc.notable_event_id=ne.notable_event_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='event'

ORDER BY 2
Click to copy

Should this query take too long to process, fetch Notes, Annotations and Events separately.

Expand or collapse content 2. List of User Comments (by type)

2.1. Fetch Notes

SELECT uc.element_id As ElementID, de.name AS Element_Name, de.type AS Element_Type, sv.value_display_name As Dimension_value, uc.scope AS First_comment_type, un.text AS First_comment, u_un.username AS First_comment_user, un.created_time As First_comment_time, uc.text AS Second_comment, u_uc.username AS Second_comment_user, uc.last_updated_time AS Second_comment_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN user_note AS un ON(uc.user_note_id=un.user_note_id)
JOIN user AS u_un ON(un.user_id=u_un.user_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='note' 
Click to copy

2.2. Fetch Annotations

SELECT uc.element_id, de.name, de.type, sv.value_display_name,  uc.scope, ua.annotation_text, u_ua.username, ua.annotation_time, uc.text, u_uc.username, uc.last_updated_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN user_annotation AS ua ON(uc.user_annotation_id=ua.user_annotation_id)
JOIN user AS u_ua ON(ua.user_id=u_ua.user_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='annotation'
Click to copy

2.3. Fetch events

SELECT uc.element_id, de.name, de.type, sv.value_display_name, uc.scope, ne.name, ne.last_updated_by, ne.last_notable_event_activity_time, uc.text, u_uc.username, uc.last_updated_time
FROM user_comment AS uc
JOIN dashboard_element AS de ON(de.element_id=uc.element_id)
LEFT JOIN segment_value AS sv ON(sv.segment_value_id=uc.segment_value_id)
JOIN notable_event AS ne ON(uc.notable_event_id=ne.notable_event_id)
JOIN user AS u_uc ON(uc.user_id=u_uc.user_id)
WHERE uc.scope='event'
Click to copy