coxomblr-views.sql

-- Visão: Posts with Actions
-- Descrição: Retorna a tabela posts com as colunas número de likes, shares (reblogs) e replies

CREATE OR REPLACE VIEW posts_with_actions AS 
 SELECT p.id, p.title, p.content, p.blog_id, p.creator_id, p.published_at, 
    p.url, p.image_path, p.created_at, p.updated_at, 
    sum(
        CASE
            WHEN a.kind = 1 THEN 1
            ELSE 0
        END) AS likes, 
    sum(
        CASE
            WHEN a.kind = 2 THEN 1
            ELSE 0
        END) AS shares, 
    sum(
        CASE
            WHEN a.kind = 3 THEN 1
            ELSE 0
        END) AS replies
   FROM posts p
   LEFT JOIN actions a ON a.post_id = p.id
  GROUP BY p.id;