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()