async def run():
await Tortoise.init(config_file="config.json")
await Tortoise.generate_schemas()
tournament = await Tournament.create(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="Team {}".format(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())
async def run():
client = SqliteClient('example_aggregation.sqlite3')
await client.create_connection()
Tortoise.init(client)
await generate_schema(client)
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='Team {}'.format(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())
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="Team {}".format(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(
id=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)
async def run():
await Tortoise.init(config_file='config.json')
await Tortoise.generate_schemas()
tournament = await Tournament.create(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='Team {}'.format(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())
async def test_filter_by_aggregation_field(self):
tournament = await Tournament.create(name='0')
await Tournament.create(name='1')
await Event.create(name='2', tournament=tournament)
tournaments = await Tournament.annotate(events_count=Count('events')).filter(events_count=1)
self.assertEqual(len(tournaments), 1)
self.assertEqual(tournaments[0].id, tournament.id)
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_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_filter_by_aggregation_field_with_not(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(~Q(events_count=1, name="0"))
self.assertEqual(len(tournaments), 1)
self.assertEqual(tournaments[0].id, tournament_second.id)
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_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_as_two_nodes(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(
Q(events_count=1) & Q(name='0'),
)
self.assertEqual(len(tournaments), 1)
self.assertEqual(tournaments[0].id, tournament.id)
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="Team {}".format(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_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_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_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 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)
