def format_counts(cls):

        """
        Map unique file formats to document counts.
        """

        count = fn.Count(cls.id)

        query = (
            cls
            .select(cls.format, count.alias('count'))
            .distinct(cls.document)
            .group_by(cls.format)
            .order_by(count.desc())
        )

        counts = []
        for c in query.iterator():
            counts.append((c.format, c.count))

        return counts 

def get_aggregated_logs(
    start_time,
    end_time,
    performer=None,
    repository=None,
    namespace=None,
    ignore=None,
    model=LogEntry3,
):
    """
    Returns the count of logs, by kind and day, for the logs matching the given filters.
    """
    date = db.extract_date("day", model.datetime)
    selections = [model.kind, date.alias("day"), fn.Count(model.id).alias("count")]
    query = _logs_query(
        selections, start_time, end_time, performer, repository, namespace, ignore, model=model
    )
    return query.group_by(date, model.kind) 

def get_stars(repository_ids):
    """
    Returns a map from repository ID to the number of stars for each repository in the given
    repository IDs list.
    """
    if not repository_ids:
        return {}

    tuples = (
        Star.select(Star.repository, fn.Count(Star.id))
        .where(Star.repository << repository_ids)
        .group_by(Star.repository)
        .tuples()
    )

    star_map = {}
    for record in tuples:
        star_map[record[0]] = record[1]

    return star_map 

def check_diff(self):
        idx_list = [idx.id for idx in History.select(History.id).order_by(History.id.desc())
                    .limit(self.cfg.DiffCount())]
        ip_diff_add_sql = IP.select(fn.Count(fn.Distinct(IP.ip))).join(Item).where(IP.add == idx_list[0]).scalar()
        ip_diff_purge_sql = IP.select(fn.Count(fn.Distinct(IP.ip))).join(Item).where(IP.purge == idx_list[0]).scalar()
        domain_diff_add_sql = Domain.select(fn.Count(fn.Distinct(Domain.domain)))\
            .join(Item).where(Domain.add == idx_list[0]).scalar()
        domain_diff_purge_sql = Domain.select(fn.Count(fn.Distinct(Domain.domain)))\
            .join(Item).where(Domain.purge == idx_list[0]).scalar()
        url_diff_add_sql = URL.select(fn.Count(fn.Distinct(URL.url)))\
            .join(Item).where(URL.add == idx_list[0]).scalar()
        url_diff_purge_sql = URL.select(fn.Count(fn.Distinct(URL.url)))\
            .join(Item).where(URL.purge == idx_list[0]).scalar()

        if ip_diff_add_sql or ip_diff_purge_sql or domain_diff_add_sql or \
                domain_diff_purge_sql or url_diff_add_sql or url_diff_purge_sql:
            History.update(dump=True).where(History.id == idx_list[0]).execute()
            return True
        else:
            # History.update(dump=False).where(History.id == idx_list[0]).execute()
            return False 

def test_select_subquery(flushdb):
    # 10 users, 5 blogs each
    await create_users_blogs(5, 3)

    # delete user 2's 2nd blog
    await Blog.delete().where(Blog.title == 'b-2-2')

    subquery = (Blog.select(fn.Count(Blog.pk))
                    .where(Blog.user == User.id)
                    .group_by(Blog.user))
    users = User.select(User, subquery.alias('ct')).order_by(R('ct'), User.id)

    expected = [('u2', 2),
                ('u0', 3),
                ('u1', 3),
                ('u3', 3),
                ('u4', 3)]
    assert [(x.username, x.ct) async for x in users] == expected 

def test_scalar(flushdb):
    await User.create_users(5)

    users = User.select(fn.Count(User.id)).scalar()
    assert await users == 5

    users = User.select(fn.Count(User.id)).where(User.username << ['u1', 'u2'])
    assert await users.scalar() == 2
    assert await users.scalar(True) == (2,)

    users = User.select(fn.Count(User.id)).where(User.username == 'not-here')
    assert await users.scalar() == 0
    assert await users.scalar(True) == (0,)

    users = User.select(fn.Count(User.id), fn.Count(User.username))
    assert await users.scalar() == 5
    assert await users.scalar(True) == (5, 5)

    await User.create(username='u1')
    await User.create(username='u2')
    await User.create(username='u3')
    await User.create(username='u99')
    users = User.select(fn.Count(fn.Distinct(User.username))).scalar()
    assert await users == 6 

def emojistats_custom(self, event, mode, sort):
        if mode not in ('server', 'global'):
            raise CommandFail('invalid emoji mode, must be `server` or `global`')

        if sort not in ('least', 'most'):
            raise CommandFail('invalid emoji sort, must be `least` or `most`')

        order = 'DESC' if sort == 'most' else 'ASC'

        if mode == 'server':
            q = CUSTOM_EMOJI_STATS_SERVER_SQL.format(order, guild=event.guild.id)
        else:
            q = CUSTOM_EMOJI_STATS_GLOBAL_SQL.format(order, guild=event.guild.id)

        q = list(GuildEmoji.raw(q).tuples())

        tbl = MessageTable()
        tbl.set_header('Count', 'Name', 'ID')
        for emoji_id, name, count in q:
            tbl.add(count, name, emoji_id)

        event.msg.reply(tbl.compile()) 

def rank_texts(cls):

        """
        Get total citation counts and ranks for texts.

        Returns: list
        """

        # TODO: Does this belong on the index manager?

        count = fn.Count(Citation.id)

        query = (
            Text
            .select(Text, count)
            .join(Citation)
            .where(Text.display==True)
            .where(Text.valid==True)
            .group_by(Text.id)
            .order_by(Text.id)
            .naive()
        )

        counts = [t.count for t in query]

        # Compute dense-rank ratios.
        dense_ranks = rankdata(counts, 'dense')
        top = max(dense_ranks)
        scores = [float(r/top) for r in dense_ranks]

        # Compute overall ranks (#1 is most frequent).
        max_ranks = rankdata(counts, 'max')
        top = max(max_ranks)
        ranks = [int(top-r+1) for r in max_ranks]

        return [
            dict(zip(['text', 'rank', 'score'], t))
            for t in zip(query, ranks, scores)
        ] 

def test_annotate_int(flushdb):
    users = await create_user_blogs()
    annotated = await User.select().annotate(
        Blog, fn.Count(Blog.pk).alias('ct'))

    for i, user in enumerate(annotated):
        assert user.ct == 2
        assert user.username == 'u-%d' % i 

def get_teams_within_org(organization, has_external_auth=False):
    """
    Returns a AttrDict of team info (id, name, description), its role under the org, the number of
    repositories on which it has permission, and the number of members.
    """
    query = Team.select().where(Team.organization == organization).join(TeamRole)

    def _team_view(team):
        return {
            "id": team.id,
            "name": team.name,
            "description": team.description,
            "role_name": Team.role.get_name(team.role_id),
            "repo_count": 0,
            "member_count": 0,
            "is_synced": False,
        }

    teams = {team.id: _team_view(team) for team in query}
    if not teams:
        # Just in case. Should ideally never happen.
        return []

    # Add repository permissions count.
    permission_tuples = (
        RepositoryPermission.select(RepositoryPermission.team, fn.Count(RepositoryPermission.id))
        .where(RepositoryPermission.team << list(teams.keys()))
        .group_by(RepositoryPermission.team)
        .tuples()
    )

    for perm_tuple in permission_tuples:
        teams[perm_tuple[0]]["repo_count"] = perm_tuple[1]

    # Add the member count.
    members_tuples = (
        TeamMember.select(TeamMember.team, fn.Count(TeamMember.id))
        .where(TeamMember.team << list(teams.keys()))
        .group_by(TeamMember.team)
        .tuples()
    )

    for member_tuple in members_tuples:
        teams[member_tuple[0]]["member_count"] = member_tuple[1]

    # Add syncing information.
    if has_external_auth:
        sync_query = TeamSync.select(TeamSync.team).where(TeamSync.team << list(teams.keys()))
        for team_sync in sync_query:
            teams[team_sync.team_id]["is_synced"] = True

    return [AttrDict(team_info) for team_info in list(teams.values())]