Skip to content

How many messages per channel in a specific discord guild?

Query Name

guild_message_per_channel

SQL Query


select
    guild_name,
    channel_name,
    msg_count,
    channel_type,
    guild_id,
    channel_id,
    topic
from 
(
    select
        count(*) msg_count,
        channel_id  
    from messages_t
    where guild_id in (  (select id from guilds_t limit 1)  )
    group by channel_id
) as channel_msg_count_t
join channels_t on channel_msg_count_t.channel_id = channels_t.id
join guilds_t on channels_t.guild_id = guilds_t.id
order by msg_count desc;



select
    guild_name,
    channel_name,
    msg_count,
    channel_id
from
(
    select
        channel_id,
        count(content) as msg_count
    from
        messages_t
    where
        guild_id in (  (select id from guilds_t limit 1)  )
        and messages_t.isbot = 'F'
    group by channel_id
) as author_in_channel_count_t
join channels_t on author_in_channel_count_t.channel_id = channels_t.id
join guilds_t on channels_t.guild_id = guilds_t.id
order by msg_count desc;

GraphQL Query


query MyQuery {
  allChannelsTs(condition: {guildId: "621022643344113684"}) {
    edges {
      node {
        id
        channelName
        channelType
        guildsTByGuildId {
          id
        }
        messagesTsByChannelId {
          aggregates {
            distinctCount {
              id
            }
          }
        }
      }
    }
  }
}

GraphQL Query

query MyQuery {
  allGuildsTs(condition: {id: "621022643344113684"}) {
    edges {
      node {
        id
        guildName
        channelsTsByGuildId {
          nodes {
            guildId
            id
            channelName
          }
          edges {
            node {
              messagesTsByChannelId {
                aggregates {
                  distinctCount {
                    id
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}