coxomblr-query.sql

-- Consulta: News Feed
-- Descrição: Busca todos os posts dos blogs que o usuário segue

SELECT posts.* 
FROM posts_with_actions posts 
JOIN blogs ON blogs.id = posts.blog_id 
JOIN relationships ON relationships.followed_id = blogs.id AND relationships.follower_id = 7 
ORDER BY created_at DESC

-- Consulta: Blog posts
-- Descrição: Busca todos os posts do blog inclusive aqueles que foram reblogados (share)

SELECT posts.* 
FROM (
  (SELECT posts_with_actions.* 
   FROM posts_with_actions 
   WHERE posts_with_actions.blog_id = 9) 
  UNION 
  (SELECT posts_with_actions.* 
   FROM posts_with_actions 
   JOIN blogs ON posts_with_actions.blog_id = blogs.id 
   JOIN actions ON actions.post_id = posts_with_actions.id AND actions.blog_id = 9)
  ) posts 
ORDER BY created_at DESC

-- Consulta: Notificações
-- Descrição: Busca todas as notificações dos posts que um usuário fez

SELECT actions.* 
FROM actions 
INNER JOIN posts ON posts.id = actions.post_id
WHERE (posts.creator_id = 7)

-- Consulta: Novas Notificações 
-- Descrição: Busca o número de novas notificações desde a última visita

SELECT COUNT(*) FROM actions
INNER JOIN posts ON posts.id = actions.post_id
WHERE (posts.creator_id = 7) AND (actions.created_at > '1970-01-01 00:00:00 UTC')

-- Consulta: Conversas
-- Descrição: Usuários que você já mandou mensagem/conversou

SELECT users.* FROM users
JOIN (
  (SELECT messages.receiver_id as user_id 
   FROM messages WHERE messages.sender_id = 7 
   GROUP BY messages.receiver_id) 
  UNION 
  (SELECT messages.sender_id as user_id 
   FROM messages 
   WHERE messages.receiver_id = 7 
   GROUP BY messages.sender_id)
  ) conversations ON conversations.user_id = users.id