Skip to content

What discord messages were reacted to the most from this particular author?

Query Name

guild_author_most_reacted_messages

SQL Query


select 
    guilds_t.guild_name,
    channels_t.channel_name,
    authors_t.author_name,
    authors_t.nickname,
    reaciton_sum,
    messages_t.msg_content,
    guilds_t.id,
    channels_t.id,
    authors_t.id,
    messages_t.id as message_id
from
(
    select 
        sum(reaction_count) as reaciton_sum,
        message_id
    from 
    (
        select 
            author_messages_t.author_guild_id,
            message_id,
            msg_content,
            guild_id,
            channel_id,
            emoji_id,
            emoji_code,
            emoji_name,
            emoji_json,
            reaction_count
        from
            (
            select
                author_guild_id,
                id as messsage_id,
                msg_content
            from
                messages_t
            where
                author_guild_id = (select author_guild_id from reactions_t limit 1 offset 10)
            group by id, msg_content
            ) as author_messages_t
        join reactions_t on author_messages_t.messsage_id = reactions_t.message_id
    ) as messages_with_reactions_t
    group by message_id
) as msg_with_reaction_count_t
join messages_t on msg_with_reaction_count_t.message_id = messages_t.id
join channels_t on messages_t.channel_id = channels_t.id
join authors_t on messages_t.author_guild_id = authors_t.id
join guilds_t on messages_t.guild_id = guilds_t.id
order by reaciton_sum desc;

Similar Queries

Scratch Pad

  • Plan 1
    • We need to get all messages from an author
    • Join it with reactions_t table
    • Group by message_id while summing the reaction count
    • Order by