Sometimes when building a query with the data explorer you’ll find codes are used in the results rather than their textual equivalents. The field often has a key in the data explorer tree, but these don’t tend to be as useful when it comes to the results (unless you’ve managed to memorise them all )
There are a couple of things you can use to make these easier on the eye, and much more widely shareable with others.
This first query was created to pull up all the notifications a user had received for a particular topic but, rather than output just the
VALUES is used to create a temporary table and map each to their much more human-readable counterparts:
-- Define parameters for user_id and topic_id -- [params] -- user_id :user_id -- topic_id :topic_id -- Create a Common Table Expression (CTE) named ntypes -- This CTE maps notification type names to their corresponding IDs WITH ntypes(notification_type, notification_type_id) AS ( VALUES -- Define pairs of notification types and their corresponding IDs ('mentioned', 1), ('replied',2), ('quoted',3), ('edited', 4), ('liked', 5), ('private_message', 6), ('invited_to_private_message', 7), ('invitee_accepted', 8), ('posted', 9), ('moved_post', 10), ('linked', 11), ('granted_badge', 12), ('invited_to_topic', 13), ('custom', 14), ('group_mentioned', 15), ('group_message_summary', 16), ('watching_first_post', 17), ('topic_reminder', 18), ('liked_consolidated', 19), ('post_approved', 20), ('code_review_commit_approved', 21), ('membership_request_accepted', 22), ('membership_request_consolidated', 23), ('bookmark_reminder', 24), ('reaction', 25), ('votes_released', 26), ('event_reminder', 27), ('event_invitation', 28), ('chat_mention', 29), ('chat_message', 30), ('chat_invitation', 31), ('chat_group_mention', 32), ('chat_quoted', 33), ('assigned', 34), ('question_answer_user_commented', 35), ('watching_category_or_tag', 36), ('new_features', 37), ('admin_problems', 38), ('following', 800), ('following_created_topic', 801), ('following_replied', 802), ('circles_activity', 900) ) -- Query the notifications table (alias n), joining it with the ntypes CTE based on the notification_type SELECT user_id, ntypes.notification_type, read, topic_id, created_at FROM notifications n JOIN ntypes ON n.notification_type = ntypes.notification_type_id -- Filter the notifications to only include those that belong to a specific user and a specific topic WHERE user_id = :user_id AND topic_id = :topic_id -- Sort the resulting notifications in descending order by the created_at timestamp ORDER BY created_at DESC
VALUES is useful when the list is a long one as it can make the query easier to read. It also allows you to link the new temporary table it creates to another one, which can be pretty handy. The temporary table can also help improve the efficiency compared with a series of
Which leads to the second…
Alternatively, if the list is much smaller, you can also use
CASE for a similar effect. Here’s one for
-- [params] -- topic_id :topic_id SELECT p.id AS post_id, -- Begin CASE statement to map post_type numerical codes to string descriptions CASE WHEN post_type = 1 THEN 'regular' WHEN post_type = 2 THEN 'moderator_action' WHEN post_type = 3 THEN 'small_action' WHEN post_type = 4 THEN 'whisper' END FROM posts p WHERE p.topic_id = :topic_id ORDER BY created_at DESC
These sample queries may not be of much use on their own, but are more to demonstrate the principles. Hopefully they help, but if you have any questions please ask them below.