async def test_filter_by_aggregation_field_with_or_not_reversed(self): tournament = await Tournament.create(name="0") await Tournament.create(name="1") await Tournament.create(name="2") await Event.create(name="1", tournament=tournament) tournaments = await Tournament.annotate(events_count=Count("events")).filter( ~(Q(name="2") | Q(events_count=1)) ) self.assertEqual(len(tournaments), 1) self.assertSetEqual({t.name for t in tournaments}, {"1"})
async def test_order_by_aggregation_reversed(self): tournament_first = await Tournament.create(name="1") tournament_second = await Tournament.create(name="2") await Event.create(name="b", tournament=tournament_first) await Event.create(name="c", tournament=tournament_first) await Event.create(name="a", tournament=tournament_second) tournaments = await Tournament.annotate(events_count=Count("events")).order_by( "-events_count" ) self.assertEqual([t.name for t in tournaments], ["1", "2"])
async def test_reset_queryset_on_query(self): tournament = await Tournament.create(name="New Tournament") event = await Event.create(name="Test", tournament_id=tournament.id) participants = [] for i in range(2): team = await Team.create(name=f"Team {(i + 1)}") participants.append(team) await event.participants.add(*participants) queryset = Event.all().annotate(count=Count("participants")) await queryset.first() await queryset.filter(name="Test").first()
async def test_count_group_by_with_join(self): ret = (await Book.annotate(count=Count("id") ).group_by("author__name").values( "author__name", "count")) self.assertListSortEqual(ret, [{ "author__name": "author1", "count": 10 }, { "author__name": "author2", "count": 5 }])
async def test_filter_by_aggregation_field_with_or_as_one_node(self): tournament = await Tournament.create(name="0") await Tournament.create(name="1") await Tournament.create(name="2") await Event.create(name="1", tournament=tournament) tournaments = await Tournament.annotate(events_count=Count("events")).filter( Q(events_count=1, name="2", join_type=Q.OR) ) self.assertEqual(len(tournaments), 2) self.assertSetEqual({t.name for t in tournaments}, {"0", "2"})
async def test_filter_by_aggregation_field_with_and(self): tournament = await Tournament.create(name="0") tournament_second = await Tournament.create(name="1") await Event.create(name="1", tournament=tournament) await Event.create(name="2", tournament=tournament_second) tournaments = await Tournament.annotate(events_count=Count("events") ).filter(events_count=1, name="0") self.assertEqual(len(tournaments), 1) self.assertEqual(tournaments[0].id, tournament.id)
async def total_distinct_users_count(cls, network_id, from_date=0, to_date=0): r = await cls \ .annotate(total_addresses=Count('user_address', distinct=True)) \ .filter(date__gte=from_date) \ .filter(date__lte=to_date) \ .filter(network=network_id) \ .values('total_addresses') return r[0]['total_addresses']
async def test_aggregation_with_distinct(self): tournament = await Tournament.create(name="New Tournament") await Event.create(name="Event 1", tournament=tournament) await Event.create(name="Event 2", tournament=tournament) await MinRelation.create(tournament=tournament) tournament_2 = await Tournament.create(name="New Tournament") await Event.create(name="Event 1", tournament=tournament_2) await Event.create(name="Event 2", tournament=tournament_2) await Event.create(name="Event 3", tournament=tournament_2) await MinRelation.create(tournament=tournament_2) await MinRelation.create(tournament=tournament_2) school_with_distinct_count = (await Tournament.filter( id=tournament_2.id).annotate( events_count=Count("events", distinct=True), minrelations_count=Count("minrelations", distinct=True), ).first()) self.assertEqual(school_with_distinct_count.events_count, 3) self.assertEqual(school_with_distinct_count.minrelations_count, 2)
async def get_top_three_files() -> List: top_three_result = ( await PullRequestFile .annotate(total_count=Count("id")) .group_by("filename") .order_by("-total_count") .limit(3) .values("filename", "total_count") ) print(top_three_result) print(type(top_three_result)) return top_three_result
async def test_group_by_annotate_result(self): ret = (await Book.annotate(upper_name=Upper("author__name"), count=Count("id")).group_by("upper_name").values( "upper_name", "count")) self.assertListSortEqual(ret, [{ "upper_name": "AUTHOR1", "count": 10 }, { "upper_name": "AUTHOR2", "count": 5 }])
async def test_count_group_by(self): ret = (await Book.annotate(count=Count("id")).group_by("author_id").values( "author_id", "count")) for item in ret: author_id = item.get("author_id") count = item.get("count") if author_id == self.a1.pk: self.assertEqual(count, 10) elif author_id == self.a2.pk: self.assertEqual(count, 5)
async def run(): await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]}) await Tortoise.generate_schemas() tournament = await Tournament.create(name="New Tournament", desc="great") await tournament.save() await Tournament.create(name="Second tournament") await Tournament.create(name=" final tournament ") await Event(name="Without participants", tournament_id=tournament.id).save() event = Event(name="Test", tournament_id=tournament.id) await event.save() participants = [] for i in range(2): team = Team(name=f"Team {(i + 1)}") await team.save() participants.append(team) await event.participants.add(participants[0], participants[1]) await event.participants.add(participants[0], participants[1]) print(await Tournament.all().annotate(events_count=Count("events")).filter(events_count__gte=1)) print(await Event.filter(id=event.id).first().annotate(lowest_team_id=Min("participants__id"))) print(await Tournament.all().annotate(events_count=Count("events")).order_by("events_count")) print(await Event.all().annotate(tournament_test_id=Sum("tournament__id")).first()) print(await Tournament.annotate(clean_desciption=Coalesce("desc")).filter(clean_desciption="")) print( await Tournament.annotate(trimmed_name=Trim("name")).filter(trimmed_name="final tournament") ) print( await Tournament.annotate(name_len=Length("name")).filter( name_len__gt=len("New Tournament") ) ) print(await Tournament.annotate(name_lo=Lower("name")).filter(name_lo="new tournament")) print(await Tournament.annotate(name_lo=Upper("name")).filter(name_lo="NEW TOURNAMENT"))
async def test_aggregation(self): tournament = Tournament(name="New Tournament") await tournament.save() await Tournament.create(name="Second tournament") await Event(name="Without participants", tournament_id=tournament.id).save() event = Event(name="Test", tournament_id=tournament.id) await event.save() participants = [] for i in range(2): team = Team(name=f"Team {(i + 1)}") await team.save() participants.append(team) await event.participants.add(participants[0], participants[1]) await event.participants.add(participants[0], participants[1]) tournaments_with_count = (await Tournament.all().annotate( events_count=Count("events")).filter(events_count__gte=1)) self.assertEqual(len(tournaments_with_count), 1) self.assertEqual(tournaments_with_count[0].events_count, 2) event_with_lowest_team_id = (await Event.filter( event_id=event.event_id ).first().annotate(lowest_team_id=Min("participants__id"))) self.assertEqual(event_with_lowest_team_id.lowest_team_id, participants[0].id) ordered_tournaments = (await Tournament.all().annotate( events_count=Count("events")).order_by("events_count")) self.assertEqual(len(ordered_tournaments), 2) self.assertEqual(ordered_tournaments[1].id, tournament.id) event_with_annotation = (await Event.all().annotate( tournament_test_id=Sum("tournament__id")).first()) self.assertEqual(event_with_annotation.tournament_test_id, event_with_annotation.tournament_id) with self.assertRaisesRegex(ConfigurationError, "name__id not resolvable"): await Event.all().annotate(tournament_test_id=Sum("name__id") ).first()
async def test_prefetch_nested_with_aggregation(self): tournament = await Tournament.create(name="tournament") event = await Event.create(name="First", tournament=tournament) await Event.create(name="Second", tournament=tournament) team = await Team.create(name="1") team_second = await Team.create(name="2") await event.participants.add(team, team_second) fetched_tournaments = (await Tournament.all().prefetch_related( Prefetch("events", queryset=Event.annotate( teams=Count("participants")).filter(teams=2)) ).first()) self.assertEqual(len(fetched_tournaments.events), 1) self.assertEqual(fetched_tournaments.events[0].id, event.id)
async def get(self): """Возвращает список пользователей, отсортированный по кол-ву рецептов. По умолчанию первые 10. """ if not await permits(self.request, "is_active"): raise web.HTTPForbidden try: limit = int(self.request.query.get("limit", "10")) except ValueError: limit = 10 users = (await User.all().annotate(recipes_count=Count("recipes") ).order_by("-recipes_count").limit(limit)) return web.json_response([await user.to_dict() for user in users])
async def test_nested_aggregation_in_annotation(self): tournament = await Tournament.create(name="0") await Tournament.create(name="1") event = await Event.create(name="2", tournament=tournament) team_first = await Team.create(name="First") team_second = await Team.create(name="Second") await event.participants.add(team_second) await event.participants.add(team_first) tournaments = await Tournament.annotate( events_participants_count=Count("events__participants") ).filter(id=tournament.id) self.assertEqual(tournaments[0].events_participants_count, 2)
async def test_filter_by_aggregation_field_comparison_length(self): t1 = await Tournament.create(name="Tournament") await Event.create(name="event1", tournament=t1) await Event.create(name="event2", tournament=t1) t2 = await Tournament.create(name="contest") await Event.create(name="event3", tournament=t2) await Tournament.create(name="Championship") t4 = await Tournament.create(name="local") await Event.create(name="event4", tournament=t4) await Event.create(name="event5", tournament=t4) tournaments = await Tournament.annotate( name_len=Length("name"), event_count=Count("events")).filter(name_len__gt=5, event_count=2) self.assertEqual(len(tournaments), 1) self.assertSetEqual({t.name for t in tournaments}, {"Tournament"})
async def get_subject_leaders(self, guild: int) -> None: self._set_embed_title('subjects') stats = await FunFact.all() \ .filter(guild_id=guild) \ .annotate(subject_count=Count('uid')) \ .group_by('subject') \ .order_by('-subject_count') \ .limit(PLACES_PER_PAGE) \ .offset(self._offset) \ .values('subject', 'subject_count') # type: List[dict] for stat in stats: ess = 's' if stat['subject_count'] > 1 else '' leader = Leader( subject=stat['subject'], value=f"{stat['subject_count']} fact{ess}" ) self._leaders.append(leader)
async def callback(self): query = Call.all().order_by('-created_at').annotate( records_count=Count('records') ).prefetch_related('records').filter( state=CallState.END, record_path=None, records_count__gte=1 ).limit(50) async for call in query: record = self._choice_record(call.records) record.converted = await convert_record(record.full_path, call.id) record.duration = await get_mp3_file_duration(record.converted) if record.converted: await record.save() call.record_path = record.converted await call.save()
async def get_game_counts(database, game_mode, member_db=None): base_query = Game.annotate(count=Count("id")) modes = [ mode_id for mode_id in constants.SUPPORTED_GAME_MODES.get(game_mode) ] if member_db: query = base_query.filter( members__member=member_db.member, members__member__clans__clan=member_db.clan, mode_id__in=modes, ) else: query = base_query.filter(mode_id__in=modes) counts = {} for row in await query.group_by("mode_id").values("mode_id", "count"): game_title = constants.MODE_MAP[row["mode_id"]]["title"] counts[game_title] = row["count"] return counts
async def stars(self, ctx): records = await ( sb.filter(message_author_id=ctx.author.id) .all() .annotate(emoji_count=Count("reaction_emoji")) .group_by("reaction_emoji") .order_by("-emoji_count") .values_list("reaction_emoji", "emoji_count") ) output = "Heres the stars you've recieved:\n\n" for emoji, count in records: output += f"{emoji} {count}\n" embed = discord.Embed( description=output ) embed.set_author(name="Starboard", icon_url=global_config.icons.star_icon) embed.set_footer(text=global_config.bot_footer) await ctx.send(embed=embed)
async def thank_leaderboard(self, ctx: commands.Context): await ctx.trigger_typing() lb = (await UserModel.annotate(thank_count=Count( "thanks", _filter=Q(thanks__guild_id=ctx.guild.id)) ).filter(thank_count__gt=0 ).order_by("-thank_count").limit(5)) if not lb: return await ctx.send(embed=Embed( title="Oopsy", description="There are no thanks here yet!", color=Color.red(), )) invis = "\u2800" embed = Embed( title="LeaderBoard", color=Color.blue(), description="\n\n".join([ f"**{m.thank_count} Thanks**{invis * (4 - len(str(m.thank_count)))}<@!{m.id}>" for m in lb ]), ) await ctx.send(embed=embed)
async def comments_by_ID( req: Request, res: Response, comment_ID: int, children: bool = False, limit: Optional[int] = 20, offset: Optional[int] = 0, sort: Optional[str] = "id:asc", ) -> Dict[str, Any]: """Get comment by ID Args: comment_ID (int): comment ID children (bool): get current comment children limit (int, optional): max number of returned comments. Defaults to 100. offset (int, optional): first comment to return (use with limit). Defaults to 1. sort (str, optional): the order of the result. attribute:(asc {ascending} or desc {descending}). Defaults to "id:asc". Returns: Dict[str, Any]: contains comment found """ key, value = ("comment", {}) if not children else ("comments", []) response = {"success": True, key: value, "detail": "Successful operation"} if not await Comment.exists(pk=comment_ID): res.status_code = status.HTTP_404_NOT_FOUND response["success"] = False response["detail"] = "Not Found" return response if children: order_by = API_functools.valid_order(Comment, sort) if order_by is None: res.status_code = status.HTTP_400_BAD_REQUEST return { **response, "success": False, "detail": invalid_sort_detail, } comment = await Comment.filter(pk=comment_ID).first() comments = await comment.json_children(order_by=order_by) response["comments"] = comments return await filter_comments( req, res, len(response["comments"]), data=response["comments"], offset=offset, limit=limit, sort=sort, ) else: response["comment"] = API_functools.get_or_default( await API_functools.add_owner_fullname([ API_functools.get_or_default( jsonable_encoder(await Comment.filter( pk=comment_ID ).prefetch_related("vote").prefetch_related( "children").annotate( votes=Count("vote", distinct=True) ).annotate(nb_children=Count("children", distinct=True) ).values( *API_functools.get_attributes(Comment), "votes", "nb_children", )), index=0, default={}, ) ]), index=0, default={}, ) return response
async def _handle_payments_to_winners(self) -> bool: """Handle payments for winning tickets We will check if users have tickets with winning numbers, if they don't lottery points will be added to the total winning pool for the next lottery """ striked_lotteries = ( await Lottery.filter(status=LotteryStatus.STRIKED) .prefetch_related("tickets") .annotate(total_tickets=Count("tickets")) ) notification_channel = self.bot.get_channel(config.LOTTERY_CHANNEL_ID) bulk_save_has_winners = [] bulk_save_no_winners = [] for lottery in striked_lotteries: # check if lottery has winning tickets winners_ids = set() winning_tickets = [] for ticket in lottery.tickets: if ticket.ticket_number in lottery.winning_tickets: winners_ids.add(ticket.user_id) winning_tickets.append(ticket) logging.debug(f":::lottery_cron: Winner for lottery: {lottery.name} is: {ticket.user_id}") # process winners if winning_tickets: bulk_save_has_winners.append(lottery.id) # get winning pool for the current lottery lottery_pool = lottery.total_tickets * lottery.ticket_price # get old winning pool old_winning_pool = await get_old_winning_pool() total_winning_pool = old_winning_pool + lottery_pool winning_ticket_share = total_winning_pool / len(winning_tickets) # share winning pool between winners for w_ticket in winning_tickets: await User.filter(id__in=[w_ticket.user_id]).update( balance=F("balance") + int(winning_ticket_share) ) # remove old winning pool (because it was paid to the winners) if old_winning_pool: await Lottery.filter(Q(status=LotteryStatus.ENDED) & Q(has_winners=False)).update(has_winners=True) winners_mentions = [f"<@!{_}>" for _ in winners_ids] winners_mentions_str = ", ".join(winners_mentions) else: bulk_save_no_winners.append(lottery.id) widget = Embed( description=f"`{lottery.name}` striked", color=GOLD, title=lottery.name, ) widget.set_thumbnail(url=config.PROJECT_THUMBNAIL) widget.add_field( name="Winning tickets:", value=f"`{', '.join(map(str, lottery.winning_tickets))}`" if lottery.winning_tickets else "`-`", inline=False, ) if winners_ids: widget.add_field( name=":trophy::trophy:Winners:trophy::trophy:", value=winners_mentions_str, inline=False ) # send notification to the channel await notification_channel.send(content=winners_mentions_str, embed=widget) else: widget.add_field( name="Winners:", value="Nobody won the sweepstake, winning pool will be added to the next sweepstake", inline=False, ) # send notification to the channel await notification_channel.send(embed=widget) # bulk change lotteries to LotteryStatus.ENDED should_reload_options = False if bulk_save_has_winners: await Lottery.filter(id__in=bulk_save_has_winners).update(status=LotteryStatus.ENDED, has_winners=True) should_reload_options = True if bulk_save_no_winners: await Lottery.filter(id__in=bulk_save_no_winners).update(status=LotteryStatus.ENDED) should_reload_options = True return should_reload_options
async def view_lottery(self, ctx: SlashContext, name: str): lottery = ( await Lottery.all() .prefetch_related("tickets") .annotate(total_tickets=Count("tickets")) .get_or_none(name=name) ) if not lottery: return await ctx.send( f"{ctx.author.mention}, error, sweepstake `{name}` doesn't exist", delete_after=DELETE_AFTER ) widget = Embed( description=":game_die:Sweepstake information:game_die:", color=GREEN, title=f"{lottery.name}", ) widget.set_thumbnail(url=config.PROJECT_THUMBNAIL) widget.add_field( name="Ticket price:", value=f"{pp_points(lottery.ticket_price)}{config.POINTS_EMOJI}", inline=False, ) widget.add_field( name="Strike Date (estimated):", value=f"[{lottery.strike_date_eta:%Y-%m-%d %H:%M} UTC](<https://etherscan.io/block/countdown/{lottery.strike_eth_block}>)", # noqa: E501 inline=False, ) widget.add_field( name="Tickets left:", value=f"{lottery.possible_tickets_count - lottery.total_tickets}", inline=False ) if lottery.status in [LotteryStatus.STARTED, LotteryStatus.STOP_SALES]: # get winning pool for the current lottery lottery_pool = lottery.total_tickets * lottery.ticket_price # get old winning pool old_winning_pool = await get_old_winning_pool() total_winning_pool = old_winning_pool + lottery_pool widget.add_field( name="Expected reward to win:", value=f"{pp_points(total_winning_pool)}{config.POINTS_EMOJI}", inline=False, ) widget.add_field(name="Status:", value=f"{lottery.status}", inline=False) widget.add_field( name="\a", value=":rocket:Advanced:rocket:", inline=False, ) if not lottery.is_guaranteed: widget.add_field(name="Min ticket number:", value=f"{lottery.ticket_min_number}", inline=False) widget.add_field(name="Max ticket number:", value=f"{lottery.ticket_max_number}", inline=False) widget.add_field( name="Winning tickets:", value=f"{', '.join(map(str, lottery.winning_tickets)) if lottery.winning_tickets else '-'}", inline=False, ) widget.add_field( name="How many ticket numbers will be drawn:", value=lottery.number_of_winning_tickets, inline=False ) widget.add_field( name="Strike ETH block:", value=f"[{lottery.strike_eth_block}](<https://etherscan.io/block/{lottery.strike_eth_block}>)", inline=False, ) # noqa: E501 await ctx.send(content=ctx.author.mention, embed=widget, delete_after=DELETE_AFTER)
async def test_count_filter_group_by(self): ret = (await Book.annotate(count=Count("id")).filter( count__gt=6).group_by("author_id").values("author_id", "count")) self.assertEqual(len(ret), 1) self.assertEqual(ret[0].get("count"), 10)
async def index(request: Request, rearq: ReArq = Depends(get_rearq)): task_map = rearq.task_map task_num = len(task_map) workers_info = await rearq.redis.hgetall(constants.WORKER_KEY) worker_num = len(workers_info) run_times = await JobResult.all().count() result = (await Job.all().annotate(count=Count("id"), date=ToDate("enqueue_time")).group_by( "date", "status").order_by("date").values( "date", "status", "count")) x_axis = [] series = [ { "name": "Deferred", "type": "line", "data": [], "stack": "total", "label": { "show": "true" }, }, { "name": "Queued", "type": "line", "stack": "total", "data": [], "label": { "show": "true" }, }, { "name": "InProgress", "type": "line", "stack": "total", "data": [], "label": { "show": "true" }, }, { "name": "Success", "type": "line", "stack": "total", "data": [], "label": { "show": "true" }, }, { "name": "Failed", "type": "line", "stack": "total", "data": [], "label": { "show": "true" }, }, { "name": "Expired", "type": "line", "stack": "total", "data": [], "label": { "show": "true" }, }, ] for item in result: date = str(item.get("date")) if date not in x_axis: x_axis.append(date) count = item.get("count") status = item.get("status") if status == "deferred": series[0]["data"].append(count) elif status == "queued": series[1]["data"].append(count) elif status == "in_progress": series[2]["data"].append(count) elif status == "success": series[3]["data"].append(count) elif status == "failed": series[4]["data"].append(count) elif status == "expired": series[5]["data"].append(count) return templates.TemplateResponse( "dashboard.html", { "request": request, "page_title": "dashboard", "worker_num": worker_num, "task_num": task_num, "run_times": run_times, "x_axis": x_axis, "series": series, }, )
async def hashmarksPopularTags(min=1, limit=64): return await IPTag.annotate(marks_count=Count('revhashmarks')).filter( marks_count__gte=min).limit(limit).order_by('-marks_count')
async def test_implicit_group_by(self): ret = await Author.annotate(count=Count("books")).filter(count__gt=6) self.assertEqual(ret[0].count, 10)
async def run(): await Tortoise.init(db_url="mysql://*****:*****@localhost:55555/test_demo", modules={"models": ["__main__"]}) # await Tortoise.generate_schemas() # # a1 = await Author.create(name="author1") # a2 = await Author.create(name="author2") # for i in range(10): # await Book.create(name=f"book{i}", author_id=a1.id, rating=i) # for i in range(5): # await Book.create(name=f"book{i}", author=a2, rating=i) ret = await Book.annotate(count=Count('id')).group_by('author_id').values() ret2 = await Book.all().group_by('author_id').annotate(count = Count('id')).values() print(ret) print(ret2) print() # >>> [{'author_id': 1, 'count': 10}, {'author_id': 2, 'count': 5}] ret = ( await Book.annotate(count=Count("id")) .filter(count__gt=6) .group_by("author_id") .values("author_id", "count") ) print(ret) # >>> [{'author_id': 1, 'count': 10}] ret = await Book.annotate(sum=Sum("rating")).group_by("author_id").values("author_id", "sum") print(ret) # >>> [{'author_id': 1, 'sum': 45.0}, {'author_id': 2, 'sum': 10.0}] ret = ( await Book.annotate(sum=Sum("rating")) .filter(sum__gt=11) .group_by("author_id") .values("author_id", "sum") ) print(ret) # >>> [{'author_id': 1, 'sum': 45.0}] ret = await Book.annotate(avg=Avg("rating")).group_by("author_id").values("author_id", "avg") print(ret) # >>> [{'author_id': 1, 'avg': 4.5}, {'author_id': 2, 'avg': 2.0}] ret = ( await Book.annotate(avg=Avg("rating")) .filter(avg__gt=3) .group_by("author_id") .values("author_id", "avg") ) print(ret) # >>> [{'author_id': 1, 'avg': 4.5}] # and use .values_list() ret = ( await Book.annotate(count=Count("id")) .group_by("author_id") .values_list("author_id", "count") ) print(ret) # >>> [(1, 10), (2, 5)] # group by with join ret = ( await Book.annotate(count=Count("id")) .group_by("author__name") .values("author__name", "count") ) print(ret)