def test_annotate_with_aggregation_in_condition(self): self.assertQuerysetEqual( CaseTestModel.objects.values(*self.non_lob_fields).annotate( min=Min('fk_rel__integer'), max=Max('fk_rel__integer'), ).annotate(test=Case( When(integer2=F('min'), then=Value('min')), When(integer2=F('max'), then=Value('max')), output_field=models.CharField(), ), ).order_by('pk'), [(1, 1, 'min'), (2, 3, 'max'), (3, 4, 'max'), (2, 2, 'min'), (3, 4, 'max'), (3, 3, 'min'), (4, 5, 'min')], transform=itemgetter('integer', 'integer2', 'test'))
def test_annotate_with_aggregation_in_predicate(self): self.assertQuerysetEqual( CaseTestModel.objects.values(*self.non_lob_fields).annotate( max=Max('fk_rel__integer'), ).annotate(test=Case( When(max=3, then=Value('max = 3')), When(max=4, then=Value('max = 4')), default=Value(''), output_field=models.CharField(), ), ).order_by('pk'), [(1, 1, ''), (2, 3, 'max = 3'), (3, 4, 'max = 4'), (2, 3, 'max = 3'), (3, 4, 'max = 4'), (3, 4, 'max = 4'), (4, 5, '')], transform=itemgetter('integer', 'max', 'test'))
def test_update_ip_address(self): CaseTestModel.objects.update( ip_address=Case( # fails on postgresql if output_field is not set explicitly When(integer=1, then=Value('1.1.1.1')), When(integer=2, then=Value('2.2.2.2')), output_field=models.IPAddressField(), ), ) self.assertQuerysetEqual(CaseTestModel.objects.all().order_by('pk'), [(1, '1.1.1.1'), (2, '2.2.2.2'), (3, None), (2, '2.2.2.2'), (3, None), (3, None), (4, None)], transform=attrgetter('integer', 'ip_address'))
def test_update_binary(self): CaseTestModel.objects.update( binary=Case( When(integer=1, then=Value(b'one', output_field=models.BinaryField())), When(integer=2, then=Value(b'two', output_field=models.BinaryField())), default=Value(b'', output_field=models.BinaryField()), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, b'one'), (2, b'two'), (3, b''), (2, b'two'), (3, b''), (3, b''), (4, b'')], transform=lambda o: (o.integer, bytes(o.binary)) )
def test_update_file_path(self): CaseTestModel.objects.update( file_path=Case( When(integer=1, then=Value('~/1')), When(integer=2, then=Value('~/2')), default=Value(''), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, '~/1'), (2, '~/2'), (3, ''), (2, '~/2'), (3, ''), (3, ''), (4, '')], transform=attrgetter('integer', 'file_path') )
def test_update_boolean(self): CaseTestModel.objects.update( boolean=Case( When(integer=1, then=True), When(integer=2, then=True), default=False, ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, True), (2, True), (3, False), (2, True), (3, False), (3, False), (4, False)], transform=attrgetter('integer', 'boolean') )
def test_update_with_expression_as_value(self): CaseTestModel.objects.update( integer=Case( When(integer=1, then=F('integer') + 1), When(integer=2, then=F('integer') + 3), default='integer', ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [('1', 2), ('2', 5), ('3', 3), ('2', 5), ('3', 3), ('3', 3), ('4', 4)], transform=attrgetter('string', 'integer') )
def test_update_text(self): CaseTestModel.objects.update( text=Case( When(integer=1, then=Value('1')), When(integer=2, then=Value('2')), default=Value(''), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, '1'), (2, '2'), (3, ''), (2, '2'), (3, ''), (3, ''), (4, '')], transform=attrgetter('integer', 'text') )
def test_filter_with_aggregation_in_predicate(self): self.assertQuerysetEqual( CaseTestModel.objects.values(*self.non_lob_fields).annotate( max=Max('fk_rel__integer'), ).filter( integer=Case( When(max=3, then=2), When(max=4, then=3), ), ).order_by('pk'), [(2, 3, 3), (3, 4, 4), (2, 2, 3), (3, 4, 4), (3, 3, 4)], transform=itemgetter('integer', 'integer2', 'max') )
def test_update(self): CaseTestModel.objects.update( string=Case( When(integer=1, then=Value('one')), When(integer=2, then=Value('two')), default=Value('other'), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, 'one'), (2, 'two'), (3, 'other'), (2, 'two'), (3, 'other'), (3, 'other'), (4, 'other')], transform=attrgetter('integer', 'string') )
def test_in_subquery(self): self.assertQuerysetEqual( CaseTestModel.objects.filter( pk__in=CaseTestModel.objects.annotate( test=Case( When(integer=F('integer2'), then='pk'), When(integer=4, then='pk'), output_field=models.IntegerField(), ), ).values('test')).order_by('pk'), [(1, 1), (2, 2), (3, 3), (4, 5)], transform=attrgetter('integer', 'integer2') )
def test_aggregate(self): self.assertEqual( CaseTestModel.objects.aggregate( one=models.Sum(Case( When(integer=1, then=1), output_field=models.IntegerField(), )), two=models.Sum(Case( When(integer=2, then=1), output_field=models.IntegerField(), )), three=models.Sum(Case( When(integer=3, then=1), output_field=models.IntegerField(), )), four=models.Sum(Case( When(integer=4, then=1), output_field=models.IntegerField(), )), ), {'one': 1, 'two': 2, 'three': 3, 'four': 1} )
def test_combined_expression(self): self.assertQuerysetEqual( CaseTestModel.objects.annotate( test=Case( When(integer=1, then=2), When(integer=2, then=1), default=3, output_field=models.IntegerField(), ) + 1, ).order_by('pk'), [(1, 3), (2, 2), (3, 4), (2, 2), (3, 4), (3, 4), (4, 4)], transform=attrgetter('integer', 'test') )
def test_filter_example(self): a_month_ago = date.today() - timedelta(days=30) a_year_ago = date.today() - timedelta(days=365) self.assertQuerysetEqual( Client.objects.filter( registered_on__lte=Case( When(account_type=Client.GOLD, then=a_month_ago), When(account_type=Client.PLATINUM, then=a_year_ago), ), ), [('Jack Black', 'P')], transform=attrgetter('name', 'account_type') )
def test_simple_example(self): self.assertQuerysetEqual( Client.objects.annotate( discount=Case( When(account_type=Client.GOLD, then=Value('5%')), When(account_type=Client.PLATINUM, then=Value('10%')), default=Value('0%'), output_field=models.CharField(), ), ).order_by('pk'), [('Jane Doe', '0%'), ('James Smith', '5%'), ('Jack Black', '10%')], transform=attrgetter('name', 'discount') )
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 test_update_email(self): CaseTestModel.objects.update( email=Case( When(integer=1, then=Value('*****@*****.**')), When(integer=2, then=Value('*****@*****.**')), default=Value(''), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, '*****@*****.**'), (2, '*****@*****.**'), (3, ''), (2, '*****@*****.**'), (3, ''), (3, ''), (4, '')], transform=attrgetter('integer', 'email') )
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/unread_help_messages', to, ctx): channel.last_read = channel.latest_message channel.save()
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 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 test_update_fk(self): obj1, obj2 = CaseTestModel.objects.all()[:2] CaseTestModel.objects.update( fk=Case( When(integer=1, then=Value(obj1.pk)), When(integer=2, then=Value(obj2.pk)), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, obj1.pk), (2, obj2.pk), (3, None), (2, obj2.pk), (3, None), (3, None), (4, None)], transform=attrgetter('integer', 'fk_id') )
def test_filter_with_aggregation_in_condition(self): self.assertQuerysetEqual( CaseTestModel.objects.values(*self.non_lob_fields).annotate( min=Min('fk_rel__integer'), max=Max('fk_rel__integer'), ).filter( integer=Case( When(integer2=F('min'), then=Value(2)), When(integer2=F('max'), then=Value(3)), ), ).order_by('pk'), [(3, 4, 3, 4), (2, 2, 2, 3), (3, 4, 3, 4)], transform=itemgetter('integer', 'integer2', 'min', 'max') )
def test_update_time(self): CaseTestModel.objects.update( time=Case( # fails on sqlite if output_field is not set explicitly on all # Values containing times When(integer=1, then=Value(time(1), output_field=models.TimeField())), When(integer=2, then=Value(time(2), output_field=models.TimeField())), ), ) self.assertQuerysetEqual( CaseTestModel.objects.all().order_by('pk'), [(1, time(1)), (2, time(2)), (3, None), (2, time(2)), (3, None), (3, None), (4, None)], transform=attrgetter('integer', 'time') )
def test_filter_with_annotation_in_value(self): self.assertQuerysetEqual( CaseTestModel.objects.annotate( f=F('integer'), f_plus_1=F('integer') + 1, ).filter( integer2=Case( When(integer=2, then='f_plus_1'), When(integer=3, then='f'), ), ).order_by('pk'), [(2, 3), (3, 3)], transform=attrgetter('integer', 'integer2') )
def test_filter_with_annotation_in_condition(self): self.assertQuerysetEqual( CaseTestModel.objects.annotate( f_plus_1=F('integer') + 1, ).filter( integer=Case( When(integer2=F('integer'), then=Value(2)), When(integer2=F('f_plus_1'), then=Value(3)), output_field=models.IntegerField(), ), ).order_by('pk'), [(3, 4), (2, 2), (3, 4)], transform=attrgetter('integer', 'integer2') )
def test_annotate_with_aggregation_in_value(self): self.assertQuerysetEqual( CaseTestModel.objects.values(*self.non_lob_fields).annotate( min=Min('fk_rel__integer'), max=Max('fk_rel__integer'), ).annotate( test=Case( When(integer=2, then='min'), When(integer=3, then='max'), ), ).order_by('pk'), [(1, None, 1, 1), (2, 2, 2, 3), (3, 4, 3, 4), (2, 2, 2, 3), (3, 4, 3, 4), (3, 4, 3, 4), (4, None, 5, 5)], transform=itemgetter('integer', 'test', 'min', 'max') )
def test_annotate_with_annotation_in_condition(self): self.assertQuerysetEqual( CaseTestModel.objects.annotate( f_plus_1=F('integer') + 1, ).annotate( f_test=Case( When(integer2=F('integer'), then=Value('equal')), When(integer2=F('f_plus_1'), then=Value('+1')), output_field=models.CharField(), ), ).order_by('pk'), [(1, 'equal'), (2, '+1'), (3, '+1'), (2, 'equal'), (3, '+1'), (3, 'equal'), (4, '+1')], transform=attrgetter('integer', 'f_test') )
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 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 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() ) )