A deep dive into all messages sent and received by a user within the given timeframe.
-- [params]
-- user_id :user
-- string :interval = 1 month
SELECT
chat_channel_id,
user_id,
message,
created_at AS reltime$time,
created_at
FROM chat_messages
WHERE chat_channel_id IN (
SELECT cc.id
FROM chat_channels cc
JOIN chat_messages cm ON cm.chat_channel_id = cc.id
WHERE cc.chatable_type = 'DirectMessage'
AND cm.user_id = :user
)
AND created_at::date > CURRENT_TIMESTAMP - INTERVAL :interval
ORDER BY chat_channel_id DESC, created_at ASC