28: Simplifying Queries With Views

Most Popular Users

"Find the most popular users - users who were tagged the most."

We could do another union between post_tags and caption_tags like we have done prior. Then we could join between users and tags.

SELECT username, COUNT(*) AS num_tags
FROM users
JOIN (
    SELECT user_id
    FROM post_tags
    UNION
    SELECT user_id
    FROM caption_tags
) AS tags
ON tags.user_id = users.id
GROUP BY username
ORDER BY COUNT(*) DESC
copy

If we run it, what is awkward about it? The awkward part is that once again we needed to do a UNION between these two tags tables.

How can we improve this situation?

A possible solution for merging

What are the possible solutions?

Create a VIEW

To create a new view.

CREATE VIEW tags AS (
    SELECT id, created_at, user_id, post_id, 'photo_tag' AS type FROM photos_tags
    UNION ALL
    SELECT id, created_at, user_id, post_id, 'caption_tag' AS type FROM photos_tags
);

SELECT * FROM tags; -- will return all type
copy

When to use a view

Another example: "Find the 10 most recent posts".

CREATE VIEW recent_posts AS (
    SELECT *
    FROM posts
    ORDER BY created_at DESC
    LIMIT 10
);

SELECT * FROM recent_posts;
copy

Now if we want to 10 most recent users from the recent posts:

SELECT username
FROM recent_posts
JOIN users ON users.id = recent_posts.user_id
copy

Deleting and changing views

CREATE OR REPLACE VIEW recent_posts AS (
    SELECT *
    FROM posts
    ORDER BY created_at DESC
    LIMIT 15
);
copy

Deleting a view:

DROP VIEW recent_posts;
copy