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()
await Team.bulk_create(
Team(name="Team {}".format(i + 1)) for i in range(2))
participants = list(await Team.all())
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).annotate(
lowest_team_id=Min("participants__id")).first())
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)
##############
default_name_tournaments = (await Tournament.all().annotate(
Count("events")).order_by("events__count"))
self.assertEqual(len(default_name_tournaments), 2)
self.assertEqual(default_name_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(NotARelationFieldError,
str(NotARelationFieldError("name",
Event))):
await Event.all().annotate(tournament_test_id=Sum("name__id")
).first()
async def test_self_ref_annotate(self):
root = await Employee.create(name="Root")
await Employee.create(name="Loose")
await Employee.create(name="1. First H1", manager=root)
await Employee.create(name="2. Second H1", manager=root)
root_ann = await Employee.annotate(
num_team_members=Count("team_members")).get(name="Root")
self.assertEqual(root_ann.num_team_members, 2)
root_ann = await Employee.annotate(
num_team_members=Count("team_members")).get(name="Loose")
self.assertEqual(root_ann.num_team_members, 0)
def execute_get_missing_initial_alerts(itgs):
endpoint_users = Table('endpoint_users')
endpoint_alerts = Table('endpoint_alerts')
users = Table('users')
usage_after_filters = Table('usage_after_filters')
query = (Query.with_(
Query.from_(endpoint_users).where(
Not(
Exists(
Query.from_(endpoint_alerts).where(
endpoint_alerts.endpoint_id ==
endpoint_users.endpoint_id).where(
endpoint_alerts.user_id == endpoint_users.user_id))
)).select(endpoint_users.endpoint_id.as_('endpoint_id'),
endpoint_users.user_id.as_('user_id'),
Min(endpoint_users.created_at).as_('first_usage'),
Max(endpoint_users.created_at).as_('last_usage'),
Count(endpoint_users.id).as_('count_usage')).groupby(
endpoint_users.endpoint_id, endpoint_users.user_id),
'usage_after_filters').from_(usage_after_filters).join(users).on(
users.id == usage_after_filters.user_id).select(
usage_after_filters.user_id, users.username,
usage_after_filters.endpoint_id,
usage_after_filters.first_usage,
usage_after_filters.last_usage,
usage_after_filters.count_usage).orderby(
usage_after_filters.user_id))
sql = query.get_sql()
itgs.read_cursor.execute(sql)
async def test_ordering_annotation_aggregations_m2o_values(self):
await create_store_objects()
products = Product.annotate(cnt=Count('brand')).order_by(
"-cnt", "name").values('cnt', 'name').limit(5)
products_fetched = await products
query_string = products.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT '
'COUNT("brand_id") "cnt","name" "name" '
'FROM "store_product" '
'GROUP BY "id" '
'ORDER BY "cnt" DESC,"name" ASC '
'LIMIT 5')
self.assertEqual(products_fetched, [{
'name': 'product_01',
'cnt': 1
}, {
'name': 'product_02',
'cnt': 1
}, {
'name': 'product_03',
'cnt': 1
}, {
'name': 'product_04',
'cnt': 1
}, {
'name': 'product_05',
'cnt': 1
}])
def show(id: int, authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
request_cost = 1
with LazyItgs() as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization,
(helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
request_cost):
return Response(status_code=429,
headers={'x-request-cost': str(request_cost)})
if user_id is None:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms
can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms
auth_methods = Table('password_authentications')
query = (Query.from_(auth_methods).select(
auth_methods.human,
auth_methods.deleted).where(auth_methods.id == Parameter('%s')))
args = [id]
if not can_view_others_auth_methods:
query = query.where(auth_methods.user_id == Parameter('%s'))
args.append(user_id)
if not can_view_deleted_auth_methods:
query = query.where(auth_methods.deleted.eq(False))
itgs.read_cursor.execute(query.get_sql(), args)
row = itgs.read_cursor.fetchone()
if row is None:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
(main, deleted) = row
authtokens = Table('authtokens')
itgs.read_cursor.execute(
Query.from_(authtokens).select(Count(
Star())).where(authtokens.expires_at < Now()).where(
authtokens.source_type == Parameter('%s')).where(
authtokens.source_id == Parameter('%s')).get_sql(),
('password_authentication', id))
(active_grants, ) = itgs.read_cursor.fetchone()
return JSONResponse(status_code=200,
content=models.AuthMethod(
main=main,
deleted=deleted,
active_grants=active_grants).dict(),
headers={'x-request-cost': str(request_cost)})
def count_tweets(search_phrase: str,
repo: Dolt,
table: str,
account_id: Optional[int] = None,
hide_deleted_tweets: bool = False,
only_deleted_tweets: bool = False) -> int:
tweets: Table = Table(table)
query: QueryBuilder = Query.from_(tweets) \
.select(Count(tweets.id)) \
.orderby(tweets.id, order=Order.desc) \
.where(Lower(tweets.text).like(
search_phrase.lower()
) # TODO: lower(text) COLLATE utf8mb4_unicode_ci like lower('{search_phrase}')
)
if account_id is not None:
# Show Results For Specific Account
query: QueryBuilder = query.where(tweets.twitter_user_id == account_id)
if hide_deleted_tweets:
# Filter Out Deleted Tweets
query: QueryBuilder = query.where(tweets.isDeleted == 0)
elif only_deleted_tweets:
# Only Show Deleted Tweets
query: QueryBuilder = query.where(tweets.isDeleted == 1)
# Perform Count Query
count_result = repo.sql(query=query.get_sql(quote_char=None),
result_format="json")["rows"]
# Retrieve Count of Tweets From Search
for header in count_result[0]:
return count_result[0][header]
return -1
async def non_indexing(self, replica_id):
q = self.select(Count('1')).where(
self.table.replica_id == replica_id,
).where(
self.table.status != Status.indexing,
)
return await self.fetchone(q)
async def run():
Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
await Tortoise.open_connections()
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"))
def _make_query(self):
self.query = self.model._meta.basequery
self.resolve_filters(
model=self.model,
q_objects=self.q_objects,
annotations=self.annotations,
custom_filters=self.custom_filters,
)
self.query = self.query.select(Count("*"))
async def test_ordering_aggregations_m2o(self):
products = Product.all().order_by(-Count('brand')).limit(20)
query_string = products.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT "id","name","price","brand_id","vendor_id" '
'FROM "store_product" '
'GROUP BY "id" '
'ORDER BY COUNT("brand_id") DESC '
'LIMIT 20')
def _make_query(self) -> None:
self.query = copy(self.model._meta.basequery)
self.resolve_filters(
model=self.model,
q_objects=self.q_objects,
annotations=self.annotations,
custom_filters=self.custom_filters,
)
self.query._select_other(Count("*"))
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)
def create_query(self, parent_context: Optional[QueryContext]) -> QueryBuilder:
query = self.query_builder(parent_context.alias if parent_context else None)
context = QueryContext(query, parent_context)
context.push(self.model, query._from[-1])
self._add_query_details(context=context)
context.query._select_other(Count("*"))
context.pop()
return context.query
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"])
def handle_loan_paid(version, body):
"""Called when we detect a loan was repaid. If there are no more loans unpaid
by the borrower, and the borrower is banned, we unban them.
"""
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
borrower_username = body['borrower']['username']
borrower_id = body['borrower']['id']
was_unpaid = body['was_unpaid']
itgs.logger.print(Level.TRACE, 'Detected /u/{} repaid a loan',
borrower_username)
if not was_unpaid:
itgs.logger.print(
Level.TRACE,
'Nothing to do about /u/{} repaying a loan - was not unpaid',
borrower_username)
return
info = perms.manager.fetch_info(itgs, borrower_username, RPIDEN,
version)
if not info['borrow_banned']:
itgs.logger.print(
Level.TRACE,
'Nothing to do about /u/{} repaying a loan - not banned',
borrower_username)
return
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(Star())).where(
loans.deleted_at.isnull()).where(
loans.unpaid_at.notnull()).where(
loans.borrower_id == Parameter('%s')).get_sql(),
(borrower_id, ))
(cnt, ) = itgs.read_cursor.fetchone()
if cnt > 0:
itgs.logger.print(
Level.TRACE,
'Nothing to do about /u/{} repaying a loan - still has {} unpaid loans',
borrower_username, cnt)
return
itgs.logger.print(Level.DEBUG,
'Unbanning /u/{} (no more loans unpaid)',
borrower_username)
utils.reddit_proxy.send_request(itgs, RPIDEN, version, 'unban_user', {
'subreddit': 'borrow',
'username': borrower_username
})
perms.manager.flush_cache(itgs, borrower_username.lower())
itgs.logger.print(
Level.INFO, 'Unbanned /u/{} - repaid all outstanding unpaid loans',
borrower_username)
def get_count_jenis_item(self):
cursor = self.get_new_cursor()
q = Query.from_(self.TABLE_PRODUCTS).join(self.TABLE_JENIS_PRODUCTS) \
.on(self.TABLE_PRODUCTS.jenis_id == self.TABLE_JENIS_PRODUCTS.id) \
.groupby(self.TABLE_PRODUCTS.jenis_id).select(
self.TABLE_JENIS_PRODUCTS.jenis_name,
Count(self.TABLE_PRODUCTS.id))
cursor.execute(q.get_sql(quote_char=None))
fetched_data = cursor.fetchall()
cursor.close()
return fetched_data
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_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_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 delete(self, replica_id):
async with self.conn.transaction():
await ReplicaFiles(self.conn).delete_replica(replica_id)
q = self.query().where(self.table.id == replica_id).delete()
await self.exec(q)
q = self.select(Count('1'))
cnt = await self.fetchval(q)
if cnt == 0:
await Files(self.conn).delete_all()
def get_products_group_by_state(self):
cursor = self.get_new_cursor()
q1 = Query.from_(self.TABLE_POSITION_PRODUCTS).where(
(self.TABLE_POSITION_PRODUCTS.new_update == BoolSql.true)).groupby(
self.TABLE_POSITION_PRODUCTS.product_state).select(
Count(self.TABLE_PRODUCTS.id),
self.TABLE_PRODUCTS.product_state)
cursor.execute(q1.get_sql(quote_char=None))
fetched_data = list(cursor.fetchall())
cursor.close()
return fetched_data
def __init__(self, model, db, q_objects, annotations,
custom_filters) -> None:
super().__init__(model, db)
table = Table(model._meta.table)
self.query = model._meta.basequery
self.resolve_filters(
model=model,
q_objects=q_objects,
annotations=annotations,
custom_filters=custom_filters,
)
self.query = self.query.select(Count(table.star))
async def get_users_count(elements_count: ElementsCount = Depends()
) -> UsersCountGithubBadge:
"""Получить кол-во пользователей.
:param elements_count: ElementsCount
:return: UsersCountGithubBadge
"""
count = elements_count.update_query(
str(SqlQuery().from_('bot_init_subscriber').select(Count('*'))), )
return UsersCountGithubBadge(
label='users count',
message=await count.get(),
)
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_ordering_aggregations_o2m(self):
await create_store_objects()
brands = Brand.all().order_by(-Count('products')).prefetch_related(
'products').limit(20)
brands_fetched = await brands
query_string = brands.query.get_sql().replace('`', '"')
self.assertEqual(
query_string,
'SELECT "store_brand"."id","store_brand"."name","store_brand"."image_id" '
'FROM "store_brand" '
'LEFT OUTER JOIN "store_product" "products" '
'ON "products"."brand_id"="store_brand"."id" '
'GROUP BY "store_brand"."id" '
'ORDER BY COUNT("products"."brand_id") DESC '
'LIMIT 20')
brands_distilled = [{
'name': c.name,
'products': {p.name
for p in c.products}
} for c in brands_fetched]
self.assertEqual(brands_distilled, [{
'name': 'brand_6',
'products': {
'product_16', 'product_17', 'product_18', 'product_19',
'product_20', 'product_21'
}
}, {
'name': 'brand_5',
'products': {
'product_11', 'product_12', 'product_13', 'product_14',
'product_15'
}
}, {
'name': 'brand_4',
'products':
{'product_07', 'product_08', 'product_09', 'product_10'}
}, {
'name': 'brand_3',
'products': {'product_04', 'product_05', 'product_06'}
}, {
'name': 'brand_2',
'products': {'product_02', 'product_03'}
}, {
'name': 'brand_1',
'products': {'product_01'}
}])
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_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"})
def __init__(self, model, filter_kwargs, db, q_objects, annotations,
having, custom_filters):
super().__init__()
self._db = db if db else model._meta.db
table = Table(model._meta.table)
self.query = self._db.query_class.from_(table)
self.resolve_filters(
model=model,
filter_kwargs=filter_kwargs,
q_objects=q_objects,
annotations=annotations,
having=having,
custom_filters=custom_filters,
)
self.query = self.query.select(Count(table.star))
async def test_ordering_annotation_aggregations_m2m(self):
await create_store_objects()
categories = Category.annotate(cnt=Count('products')).order_by(
"-cnt").prefetch_related('products').limit(20)
cats_fetched = await categories
query_string = categories.query.get_sql().replace('`', '"')
self.assertEqual(
query_string, 'SELECT '
'"store_category"."id",'
'"store_category"."name",'
'"store_category"."image_id",'
'COUNT("store_category__productcategory"."category_id") "cnt" '
'FROM "store_category" '
'LEFT OUTER JOIN "store_productcategory" "store_category__productcategory" '
'ON "store_category__productcategory"."category_id"="store_category"."id" '
'GROUP BY "store_category"."id" '
'ORDER BY "cnt" DESC '
'LIMIT 20')
cats_distilled = [{
'name': c.name,
'products': {p.name
for p in c.products}
} for c in cats_fetched]
self.assertEqual(cats_distilled, [{
'name': 'category_2',
'products': {
'product_02', 'product_04', 'product_06', 'product_08',
'product_10', 'product_12', 'product_14', 'product_16',
'product_18', 'product_20'
}
}, {
'name': 'category_3',
'products': {
'product_03', 'product_06', 'product_09', 'product_12',
'product_15', 'product_18', 'product_21'
}
}, {
'name': 'category_5',
'products':
{'product_05', 'product_10', 'product_15', 'product_20'}
}, {
'name': 'category_7',
'products': {'product_07', 'product_14', 'product_21'}
}])
async def get_messages_list(
request: Request,
filter_param: Literal['without_mailing',
'unknown'] = Query(default='', alias='filter'),
page_num: int = 1,
page_size: int = 50,
elements_count: ElementsCount = Depends(),
paginated_sequence: PaginatedSequence = Depends(),
user: UserSchema = Depends(User.get_from_token),
) -> PaginatedMessagesResponse:
"""Получить сообщения.
:param request: Request
:param filter_param: str
:param page_num: int
:param page_size: int
:param elements_count: ElementsCount
:param paginated_sequence: PaginatedSequence
:param user: UserSchema
:return: PaginatedResponse
"""
messages_table = Table('bot_init_message')
count = elements_count.update_query(
str(SqlQuery().from_(messages_table).select(Count('*')), ), )
return await PaginatedResponse(
count,
(paginated_sequence.update_query(
PaginatedMessagesQuery(
FilteredMessageQuery(
MessagesQuery(),
filter_param,
),
LimitOffsetByPageParams(page_num, page_size),
), ).update_model_to_parse(Message)),
PaginatedMessagesResponse,
NeighborsPageLinks(
PrevPage(page_num, page_size, count,
UrlWithoutQueryParams(request)),
NextPage(
page_num,
page_size,
UrlWithoutQueryParams(request),
count,
LimitOffsetByPageParams(page_num, page_size),
),
),
).get()
