def as_sql(self, compiler, connection, **extra_context): if self.filter: if connection.features.supports_aggregate_filter_clause: filter_sql, filter_params = self.filter.as_sql(compiler, connection) template = self.filter_template % extra_context.get('template', self.template) sql, params = super().as_sql(compiler, connection, template=template, filter=filter_sql) return sql, params + filter_params else: copy = self.copy() copy.filter = None condition = When(Q()) source_expressions = copy.get_source_expressions() condition.set_source_expressions([self.filter, source_expressions[0]]) copy.set_source_expressions([Case(condition)] + source_expressions[1:]) return super(Aggregate, copy).as_sql(compiler, connection, **extra_context) return super().as_sql(compiler, connection, **extra_context)
def test_combined_q_object(self): self.assertQuerysetEqual(CaseTestModel.objects.annotate(test=Case( When(Q(integer=2) | Q(integer2=3), then=Value('when')), default=Value('default'), output_field=models.CharField(), ), ).order_by('pk'), [(1, 1, 'default'), (2, 3, 'when'), (3, 4, 'default'), (2, 2, 'when'), (3, 4, 'default'), (3, 3, 'when'), (4, 5, 'default')], transform=attrgetter('integer', 'integer2', 'test'))
def test_case_reuse(self): SOME_CASE = Case( When(pk=0, then=Value('0')), default=Value('1'), output_field=models.CharField(), ) self.assertQuerysetEqual( CaseTestModel.objects.annotate(somecase=SOME_CASE).order_by('pk'), CaseTestModel.objects.annotate( somecase=SOME_CASE).order_by('pk').values_list( 'pk', 'somecase'), lambda x: (x.pk, x.somecase))
def test_update_decimal(self): CaseTestModel.objects.update( decimal=Case( When(integer=1, then=Decimal('1.1')), When(integer=2, then=Decimal('2.2')), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [ (1, Decimal('1.1')), (2, Decimal('2.2')), (3, None), (2, Decimal('2.2')), (3, None), (3, None), (4, None) ], transform=attrgetter('integer', 'decimal') )
def erc20_tokens_with_balance(self, address: str) -> List[Dict[str, Any]]: """ :return: List of dictionaries {'token_address': str, 'balance': int} """ arguments_value_field = RawSQL("(arguments->>'value')::numeric", ()) return self.erc20_events(address=address).values('address').annotate( balance=Sum( Case( When(arguments__from=address, then=-arguments_value_field), default=arguments_value_field, ))).order_by('-balance').values('address', 'balance')
def get_annotation(self, cte): topic_condition = self.build_topic_condition(F("kind_id")) topic_condition += self.build_descendant_condition(cte) topic_condition += self.build_coach_condition(cte.col.role_visibility) resource_condition = self.build_topic_condition(F("kind_id"), "!=") resource_condition += self.build_coach_condition(F("role_visibility")) return Count( Case( # when selected node is a coach topic, then count descendent content_id's When(condition=WhenQ(*topic_condition), then=cte.col.content_id), # when selected node is not a topic, count its content_id When(condition=WhenQ(*resource_condition), then=F("content_id")), default=Value(None), ), distinct=True, )
def get_service_bodies(params): root_server_id = params.get('root_server_id') body_qs = ServiceBody.objects.all() if root_server_id: body_qs = body_qs.filter(root_server_id=root_server_id) # BMLT returns top-level parents as having parent_id 0 body_qs = body_qs.annotate( calculated_parent_id=Case(When(parent=None, then=Value(0)), default='parent_id', output_field=models.BigIntegerField())) return body_qs
def get_annotation(self, cte): resource_condition = self.build_topic_condition(F('kind_id'), '!=') whens = [ # when selected node is not a topic, just use its changed status When(condition=WhenQ(*resource_condition), then=F('changed')), ] if self.include_self: # when selected node is a topic and it's changed and including self, then return that whens.append( When(condition=WhenQ(*[F('changed')]), then=F('changed'))) return Coalesce( Case( *whens, # fallback to aggregating descendant changed status when a unchanged topic default=BoolOr(cte.col.changed)), Value(False), output_field=BooleanField())
def test_join_promotion_multiple_annotations(self): o = CaseTestModel.objects.create(integer=1, integer2=1, string='1') # Testing that: # 1. There isn't any object on the remote side of the fk_rel # relation. If the query used inner joins, then the join to fk_rel # would remove o from the results. So, in effect we are testing that # we are promoting the fk_rel join to a left outer join here. # 2. The default value of 3 is generated for the case expression. self.assertQuerysetEqual( CaseTestModel.objects.filter(pk=o.pk).annotate( foo=Case( When(fk_rel__pk=1, then=2), default=3, output_field=models.IntegerField() ), bar=Case( When(fk_rel__pk=1, then=4), default=5, output_field=models.IntegerField() ), ), [(o, 3, 5)], lambda x: (x, x.foo, x.bar) ) # Now 2 should be generated, as the fk_rel is null. self.assertQuerysetEqual( CaseTestModel.objects.filter(pk=o.pk).annotate( foo=Case( When(fk_rel__isnull=True, then=2), default=3, output_field=models.IntegerField() ), bar=Case( When(fk_rel__isnull=True, then=4), default=5, output_field=models.IntegerField() ), ), [(o, 2, 4)], lambda x: (x, x.foo, x.bar) )
def analise_resultado_torneio_para_um_time(request): """Retorna dados sobre resultados em torneios de um time""" time_id = int(request.GET.get('time_id')) # Verificar se jogador existe time = get_object_or_404(Time, id=time_id) dados_jogador = list(JogadorTorneio.objects.filter(valido=True, time=time, jogador__isnull=False).order_by('id', 'torneio__data') \ .values('posicao_final', 'torneio__data', 'torneio', 'jogador__nick')) dados_torneios = Torneio.objects.filter(id__in=[dado['torneio'] for dado in dados_jogador]) \ .annotate(qtd_jogadores=Count(Case(When(jogadortorneio__valido=True, then=1)))) \ .values('id', 'qtd_jogadores') for dado_jogador in dados_jogador: for dado_torneio in dados_torneios: if dado_jogador['torneio'] == dado_torneio['id']: dado_jogador['qtd_jogadores'] = dado_torneio['qtd_jogadores'] break # TODO transferir operações com pandas para utils dados_jogadores_df = pd.DataFrame(list(dados_jogador)) # Adicionar rendimento no torneio dados_jogadores_df['rendimento'] = 1 - ( dados_jogadores_df['posicao_final'] - 1) / dados_jogadores_df['qtd_jogadores'] dados_jogadores_df = dados_jogadores_df.drop(['qtd_jogadores', 'torneio'], axis=1) # Alterar nomes de colunas para facilitar dados_jogadores_df = dados_jogadores_df.rename(columns={ 'torneio__data': 'data', 'jogador__nick': 'nome' }) dados_jogadores_df = dados_jogadores_df.sort_values(['nome', 'data']) dados_jogadores = list() for nome in dados_jogadores_df['nome'].unique().tolist(): # Preparar dicionário com dados do jogador dict_jogador = {'nome': nome} jogadores_idx = dados_jogadores_df['nome'] == nome temp_df = dados_jogadores_df.loc[jogadores_idx, dados_jogadores_df.columns != 'nome'] for column in temp_df.columns: dict_jogador[column] = temp_df[column].values.tolist() dados_jogadores.append(dict_jogador) return JsonResponse({'dados_jogadores': dados_jogadores})
def CountWhen(output_field=None, **kwargs): """Use Sum-Case to simulate a filtered Count""" # pylint: disable=invalid-name if output_field is None: output_field = IntegerField() return Sum( Case( When(then=1, **kwargs), default=0, ), output_field=output_field, )
def test_m2m_reuse(self): CaseTestModel.objects.create(integer=10, integer2=1, string='1') # Need to use values before annotate so that Oracle will not group # by fields it isn't capable of grouping by. qs = CaseTestModel.objects.values_list('id', 'integer').annotate( cnt=models.Sum( Case(When(~Q(fk_rel__integer=1), then=1), default=2), output_field=models.IntegerField() ), ).annotate( cnt2=models.Sum( Case(When(~Q(fk_rel__integer=1), then=1), default=2), output_field=models.IntegerField() ), ).order_by('integer') self.assertEqual(str(qs.query).count(' JOIN '), 1) self.assertQuerysetEqual( qs, [(1, 2, 2), (2, 2, 2), (2, 2, 2), (3, 2, 2), (3, 2, 2), (3, 2, 2), (4, 1, 1), (10, 1, 1)], lambda x: x[1:] )
def as_oracle(self, compiler, connection): # Oracle doesn't allow EXISTS() to be compared to another expression # unless it's wrapped in a CASE WHEN. wrapped = False exprs = [] for expr in (self.lhs, self.rhs): if isinstance(expr, Exists): expr = Case(When(expr, then=True), default=False, output_field=BooleanField()) wrapped = True exprs.append(expr) lookup = type(self)(*exprs) if wrapped else self return lookup.as_sql(compiler, connection)
def as_oracle(self, compiler, connection): # Oracle doesn't allow EXISTS() and filters to be compared to another # expression unless they're wrapped in a CASE WHEN. wrapped = False exprs = [] for expr in (self.lhs, self.rhs): if connection.ops.conditional_expression_supported_in_where_clause(expr): expr = Case(When(expr, then=True), default=False) wrapped = True exprs.append(expr) lookup = type(self)(*exprs) if wrapped else self return lookup.as_sql(compiler, connection)
def get_annotation(self, cte=None): """ @see MPTTModel.get_descendant_count() """ return Max( Case( # when selected node is topic, use algorithm to get descendant count When( condition=WhenQ(*self.build_topic_condition(F('kind_id'))), then=(F('rght') - F('lft') - Value(1)) / Value(2)), # integer division floors the result in postgres default=Value(1)))
def handle(self, *args, **options): """ Send new message notifications """ # command to run: python manage.py tunga_send_customer_emails min_date = datetime.datetime.utcnow() - relativedelta( minutes=1) # 5 minute window to read new messages customer_channels = Channel.objects.filter( type=CHANNEL_TYPE_SUPPORT, created_by__isnull=True, content_type=ContentType.objects.get_for_model(Inquirer) ).annotate(new_messages=Sum( Case(When(~Q(action_targets__actor_content_type=F('content_type')) & Q(action_targets__gt=F('last_read')) & Q(action_targets__timestamp__lte=min_date) & Q(action_targets__verb__in=[verbs.SEND, verbs.UPLOAD]), then=1), default=0, output_field=IntegerField())), latest_message=Max('action_targets__id')).filter( new_messages__gt=0) for channel in customer_channels: customer = channel.content_object if customer.email: activities = Action.objects.filter(channels=channel, id__gt=channel.last_read, verb__in=[verbs.SEND ]).order_by('id') messages = [activity.action_object for activity in activities] if messages: to = [customer.email] subject = "[Tunga Support] Help" ctx = { 'customer': customer, 'count': channel.new_messages, 'messages': messages, 'channel_url': '%s/customer/help/%s/' % (TUNGA_URL, channel.id) } if send_mail(subject, 'tunga/email/email_unread_help_messages', to, ctx): channel.last_read = channel.latest_message channel.save()
class SearchCompanyExportAPIView(SearchCompanyAPIViewMixin, SearchExportAPIView): """Company search export view.""" queryset = DBCompany.objects.annotate( link=get_front_end_url_expression('company', 'pk'), upper_headquarter_type_name=Upper('headquarter_type__name'), sector_name=get_sector_name_subquery('sector'), # get company.turnover if set else company.turnover_range turnover_value=Case( When( turnover__isnull=False, then=Concat(Value('$'), 'turnover'), ), default='turnover_range__name', output_field=CharField(), ), # get company.number_of_employees if set else company.employee_range number_of_employees_value=Case( When( number_of_employees__isnull=False, then=Cast('number_of_employees', CharField()), ), default='employee_range__name', output_field=CharField(), ), ) field_titles = { 'name': 'Name', 'link': 'Link', 'sector_name': 'Sector', 'address_country__name': 'Country', 'uk_region__name': 'UK region', 'archived': 'Archived', 'created_on': 'Date created', 'number_of_employees_value': 'Number of employees', 'turnover_value': 'Annual turnover', 'upper_headquarter_type_name': 'Headquarter type', }
def test_aggregate(self): self.assertEqual( CaseTestModel.objects.aggregate( one=models.Sum(Case( When(integer=1, then=Value(1)), output_field=models.IntegerField(), )), two=models.Sum(Case( When(integer=2, then=Value(1)), output_field=models.IntegerField(), )), three=models.Sum(Case( When(integer=3, then=Value(1)), output_field=models.IntegerField(), )), four=models.Sum(Case( When(integer=4, then=Value(1)), output_field=models.IntegerField(), )), ), {'one': 1, 'two': 2, 'three': 3, 'four': 1} )
def test_conditional_aggregation_example(self): Client.objects.create( name='Jean Grey', account_type=Client.REGULAR, registered_on=date.today(), ) Client.objects.create( name='James Bond', account_type=Client.PLATINUM, registered_on=date.today(), ) Client.objects.create( name='Jane Porter', account_type=Client.PLATINUM, registered_on=date.today(), ) self.assertEqual( Client.objects.aggregate( regular=models.Sum( Case( When(account_type=Client.REGULAR, then=1), output_field=models.IntegerField(), )), gold=models.Sum( Case( When(account_type=Client.GOLD, then=1), output_field=models.IntegerField(), )), platinum=models.Sum( Case( When(account_type=Client.PLATINUM, then=1), output_field=models.IntegerField(), )), ), { 'regular': 2, 'gold': 1, 'platinum': 3 })
def get_queryset(self, request): qs = super().get_queryset(request) return qs.annotate( vote_count=Count('vote'), interested_count=Sum( Case(When(vote__is_interested=True, then=Value(1))), output_field=IntegerField(), ) ).annotate( not_interested_count=ExpressionWrapper( F('vote_count') - F('interested_count'), output_field=IntegerField() ) )
def test_update_uuid(self): CaseTestModel.objects.update( uuid=Case( # fails on sqlite if output_field is not set explicitly on all # Values containing UUIDs When(integer=1, then=Value( UUID('11111111111111111111111111111111'), output_field=models.UUIDField(), )), When(integer=2, then=Value( UUID('22222222222222222222222222222222'), output_field=models.UUIDField(), )), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, UUID('11111111111111111111111111111111')), (2, UUID('22222222222222222222222222222222')), (3, None), (2, UUID('22222222222222222222222222222222')), (3, None), (3, None), (4, None)], transform=attrgetter('integer', 'uuid'))
def non_debt_egg_balance(self): result = self.inventoryitem_set.aggregate(eggs=Sum( Case( When( eggtransaction__debt=False, then=F("eggtransaction__eggs"), ), output_field=IntegerField(), default=0, )))["eggs"] if result is None: return to_isk(0) else: return to_isk(result)
def filter_list_queryset(self, request, queryset, view): label_filter = request.query_params.get('filter', None) if label_filter in ['running', 'my-tasks']: if label_filter == 'running': queryset = queryset.filter(closed=False) queryset = queryset.filter( Q(user=request.user) | (Q(participation__user=request.user) & (Q(participation__accepted=True) | Q(participation__responded=False)))) elif label_filter == 'saved': queryset = queryset.filter(savedtask__user=request.user) elif label_filter == 'skills': try: user_skills = request.user.userprofile.skills.all() queryset = queryset.filter(skills__in=user_skills) when = [] for skill in user_skills: new_when = When(skills=skill, then=1) when.append(new_when) queryset = queryset.annotate(matches=Sum( Case(*when, default=0, output_field=IntegerField()))).order_by( '-matches', '-created_at') except (ObjectDoesNotExist, UserProfile.DoesNotExist): return queryset.none() elif label_filter in ['my-clients', 'project-owners']: queryset = queryset.filter( (Q(user__connections_initiated__to_user=request.user) & Q(user__connections_initiated__accepted=True)) | (Q(user__connection_requests__from_user=request.user) & Q(user__connection_requests__accepted=True))) if request.user.is_staff or request.user.is_superuser: return queryset if request.user.type == USER_TYPE_PROJECT_OWNER: queryset = queryset.filter(user=request.user) elif request.user.type == USER_TYPE_DEVELOPER: return queryset.filter( Q(user=request.user) | Q(participation__user=request.user) | (Q(visibility=VISIBILITY_DEVELOPER) | (Q(visibility=VISIBILITY_MY_TEAM) & ( (Q(user__connections_initiated__to_user=request.user) & Q(user__connections_initiated__accepted=True)) | (Q(user__connection_requests__from_user=request.user) & Q(user__connection_requests__accepted=True))))) ).distinct() else: return queryset.none() return queryset
def get_leads(user: User, date_from: date, date_to: date, projects: list = None, label_type=None, label_values=None, os_groups=None, browser_groups=None, traffic_channels=None): leads_qs = Lead.objects.filter(pixel__project__user=user) leads_qs = _apply_lead_common_filters(leads_qs, date_from, date_to, projects, label_type, label_values, os_groups, browser_groups, traffic_channels) leads_qs = leads_qs.order_by('-created') leads_qs = leads_qs.annotate(os_group=F('os_version__family__group__name')) device_types = dict(DeviceType.TYPES) leads_qs = leads_qs.annotate(device=Concat( Case(When(device_model__device_type__category=DeviceType.PHONE, then=Value(str(device_types[DeviceType.PHONE]))), When(device_model__device_type__category=DeviceType.TABLET, then=Value(str(device_types[DeviceType.TABLET]))), When(device_model__device_type__category=DeviceType.DESKTOP, then=Value(str(device_types[DeviceType.DESKTOP]))), default=Value('Unknown')), Value(' '), F('device_model__brand__name'), Value(' '), F('device_model__model'))) cur_language = get_language() if cur_language == 'ru-ru': leads_qs = leads_qs.annotate(city=Coalesce( Case(When(geo__name_ru__exact='', then=None), When(geo__name_ru__isnull=False, then='geo__name_ru'), default=None), F('geo__name'))) else: leads_qs = leads_qs.annotate(city=F('geo__name')) leads_qs = leads_qs.select_related('geo') return leads_qs
def as_sql(self, compiler, connection, **extra_context): if self.filter: if connection.features.supports_aggregate_filter_clause: filter_sql, filter_params = self.filter.as_sql( compiler, connection) template = self.filter_template % extra_context.get( 'template', self.template) sql, params = super().as_sql(compiler, connection, template=template, filter=filter_sql) return sql, params + filter_params else: copy = self.copy() copy.filter = None condition = When(Q()) source_expressions = copy.get_source_expressions() condition.set_source_expressions( [self.filter, source_expressions[0]]) copy.set_source_expressions([Case(condition)] + source_expressions[1:]) return super(Aggregate, copy).as_sql(compiler, connection, **extra_context) return super().as_sql(compiler, connection, **extra_context)
def test_m2m_exclude(self): CaseTestModel.objects.create(integer=10, integer2=1, string='1') qs = CaseTestModel.objects.values_list('id', 'integer').annotate( cnt=models.Sum( Case(When(~Q(fk_rel__integer=1), then=1), default=2), output_field=models.IntegerField()), ).order_by('integer') # The first o has 2 as its fk_rel__integer=1, thus it hits the # default=2 case. The other ones have 2 as the result as they have 2 # fk_rel objects, except for integer=4 and integer=10 (created above). # The integer=4 case has one integer, thus the result is 1, and # integer=10 doesn't have any and this too generates 1 (instead of 0) # as ~Q() also matches nulls. self.assertQuerysetEqual(qs, [(1, 2), (2, 2), (2, 2), (3, 2), (3, 2), (3, 2), (4, 1), (10, 1)], lambda x: x[1:])
def handle(self, *args, **options): """ Update periodic update events and send notifications for upcoming update events. """ # command to run: python manage.py tunga_manage_task_status # Choose tasks that aren't closed or under review already tasks_filter = Task.objects.filter( scope=TASK_SCOPE_TASK, closed=False, review=False ).annotate( activated_at=Case( When( approved_at__isnull=True, then='created_at' ), default='approved_at', output_field=DateTimeField() ) ) utc_now = datetime.datetime.utcnow() # Remind admins and devs about approved tasks with no applications 2 days after creation or approval min_date_no_applications = utc_now - relativedelta(days=2) min_date_no_developer_selected = utc_now - relativedelta(days=10) tasks_no_applications = tasks_filter.filter( approved=True, participants__isnull=False, activated_at__range=[ min_date_no_developer_selected, min_date_no_applications ] ) for task in tasks_no_applications: # Remind admins remind_no_task_applications.delay(task.id, admin=True) # Remind devs remind_no_task_applications.delay(task.id, admin=False) # Remind admins to take action on tasks with no accepted applications 10 days after creation or approval tasks_no_developers_selected = tasks_filter.filter( participants__isnull=True, created_at__lte=min_date_no_developer_selected ) for task in tasks_no_developers_selected: # Put task in review task.review = True task.save() # Notify admins to take action notify_review_task_admin.delay(task.id)
def test_expressions(self): self.assertEqual( repr(Case(When(a=1))), "<Case: CASE WHEN <Q: (AND: ('a', 1))> THEN Value(None), ELSE Value(None)>" ) self.assertEqual(repr(Col('alias', 'field')), "Col(alias, field)") self.assertEqual(repr(Date('published', 'exact')), "Date(published, exact)") self.assertEqual(repr(DateTime('published', 'exact', utc)), "DateTime(published, exact, %s)" % utc) self.assertEqual(repr(F('published')), "F(published)") self.assertEqual(repr(F('cost') + F('tax')), "<Expression: F(cost) + F(tax)>") self.assertEqual(repr(Func('published', function='TO_CHAR')), "Func(F(published), function=TO_CHAR)") self.assertEqual(repr(OrderBy(Value(1))), 'OrderBy(Value(1), descending=False)') self.assertEqual(repr(Random()), "Random()") self.assertEqual(repr(RawSQL('table.col', [])), "RawSQL(table.col, [])") self.assertEqual(repr(Ref('sum_cost', Sum('cost'))), "Ref(sum_cost, Sum(F(cost)))") self.assertEqual(repr(Value(1)), "Value(1)")
def get(self, request): items = ( ProtectedItem.objects.annotate(is_link=Case( When(protectedfile__isnull=True, then=Value("True")), default=Value("False"), )) # .extra(select={"day": "TO_CHAR(created, 'YYYY-MM-DD')"}) # .values("day", "is_link") # .order_by("day") # .annotate(available=Count("created")) ) print(items) stats = [] return Response({})
def test_expressions(self): self.assertEqual( repr(Case(When(a=1))), "<Case: CASE WHEN <Q: (AND: ('a', 1))> THEN Value(None), ELSE Value(None)>" ) self.assertEqual(repr(Col('alias', 'field')), "Col(alias, field)") self.assertEqual(repr(F('published')), "F(published)") self.assertEqual(repr(F('cost') + F('tax')), "<CombinedExpression: F(cost) + F(tax)>") self.assertEqual( repr(ExpressionWrapper(F('cost') + F('tax'), models.IntegerField())), "ExpressionWrapper(F(cost) + F(tax))" ) self.assertEqual(repr(Func('published', function='TO_CHAR')), "Func(F(published), function=TO_CHAR)") self.assertEqual(repr(OrderBy(Value(1))), 'OrderBy(Value(1), descending=False)') self.assertEqual(repr(Random()), "Random()") self.assertEqual(repr(RawSQL('table.col', [])), "RawSQL(table.col, [])") self.assertEqual(repr(Ref('sum_cost', Sum('cost'))), "Ref(sum_cost, Sum(F(cost)))") self.assertEqual(repr(Value(1)), "Value(1)")
def annotate_activity_leave_seconds(self): sql = f""" ROUND ( EXTRACT ( EPOCH FROM ( "{History._meta.db_table}"."payload" #>> ARRAY['date','0'] ) :: timestamp with time zone ) - EXTRACT (EPOCH FROM "{History._meta.db_table}"."date") ) """ return self.annotate(activity_leave_seconds=Case( When( typus=HistoryTypus.ACTIVITY_LEAVE, then=RawSQL(sql, [], output_field=IntegerField()), ), default=None, ), )