def inbox_messages(user)

  user_id = user.id

  sub_query = """
   SELECT
     message_id
   FROM (
     SELECT
       DISTINCT ON (conversation_id)
       messages.id as message_id,
       messages.conversation_id,
       messages.from,
       message_recipients.user_id as recipient_id,
       conversations.subject,
       message_recipients.id as message_recipient_id,
       messages.created_at as message_created_at
     FROM messages
     JOIN conversations
       ON conversations.id = messages.conversation_id
     JOIN message_recipients
       ON messages.id = message_recipients.message_id
     JOIN (
       -- this sub query gets all conversations where
       -- user has been a recipient
       SELECT
         DISTINCT ON (conversation_id) conversation_id
       FROM messages
       JOIN message_recipients
         ON message_recipients.message_id = messages.id
       WHERE user_id = #{user_id}
       AND conversation_id NOT IN (
         SELECT
           conversation_id
         FROM user_archived_conversations
         WHERE user_id = #{user_id}
       )
     ) as relevant_conversations
       ON relevant_conversations.conversation_id = messages.conversation_id
     WHERE message_recipients.user_id = #{user_id}
       OR messages.from = #{user_id}
     ORDER BY conversation_id, message_created_at DESC
   ) as msg
   -- ORDER BY message_created_at DESC #Ordering is being done in active record below
 """

  Message.where("id IN (#{sub_query})").order("created_at DESC")

end