Skip to content

How to view a discord message in context?

SQL Query

Requires, message id, msg_timestamp, and channel_id of specific message


select * from
(
    select 
        *
    from
        messages_t
    where 
        channel_id = (select channel_id from messages_t offset 10 limit 1)
        and msg_timestamp > (select msg_timestamp from messages_t offset 10 limit 1)
    limit 5
)
union
select * from
(
    select 
        *
    from
        messages_t
    where 
        channel_id = (select channel_id from messages_t offset 10 limit 1)
        and msg_timestamp < (select msg_timestamp from messages_t offset 10 limit 1)
    limit 5
) order by msg_timestamp desc

Scratch pad

  • Plan 1
    • We feed in a single message_id
    • We then lookup the channel_id for that message_id
    • Then we sort all the messages from that channel
    • Then we pull out X messages before and after
  • How do we pull out the messages before and after?
  • Is there a feature in SQL for this, get before and after a specific ID?
  • Plan 2
    • There is a better way to do this
    • We get the timestamp from the specific_msg_id
    • Then we get 10 messages going backwards in time and 10 messages going forwards through time
    • Oh shit that is way better
  • Plan 3
    • It is way easier to get the 10 messages after and the 10 messages before?
    • Wait I can join queries with limits
    • Yes but in order to do what we need the msg_timestamp, id, and channel_id
    • If you have a message you already have that info
    • Oh yad

Rejected SQL Query from Plan 1


select
    *
from
(
    select 
        id as specific_msg_id,
        channel_id as specific_channel_id,
        msg_timestamp as specific_msg_timestamp
    from
        messages_t
    where id = (select id from messages_t offset 10 limit 1)
) as channel_metadata_t
join messages_t on channel_metadata_t.channel_id = messages_t.channel_id
order by msg_timestamp desc;