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