Using VALUES or CASE to make your results more readable

Data & reporting
1

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 :slight_smile:)

There are a couple of things you can use to make these easier on the eye, and much more widely shareable with others.

First up…

VALUES

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 notification_type code, VALUES is used to create a temporary table and map each to their much more human-readable counterparts:

notification key
notification key479×725 11.7 KB

-- 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 example results
VALUES example results1035×363 28.6 KB

Using 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 CASE statements.

Which leads to the second…

CASE

Alternatively, if the list is much smaller, you can also use CASE for a similar effect. Here’s one for post_type:

-- [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. :slight_smile:

13 Likes