def test_filter_aggregate(self):
vals = Author.objects.filter(age__gt=29).aggregate(Sum("age"))
self.assertEqual(len(vals), 1)
self.assertEqual(vals["age__sum"], 254)
vals = Author.objects.filter(age__gt=29).aggregate(
Sum("age", only=Q(age__lt=29)))
# If there are no matching aggregates, then None, not 0 is the answer.
self.assertEqual(vals["age__sum"], None)
def test_multiple_aggregates(self):
vals = Author.objects.aggregate(Sum("age"), Avg("age"))
self.assertEqual(vals, {
"age__sum": 337,
"age__avg": Approximate(37.4, places=1)
})
vals = Author.objects.aggregate(Sum("age", only=Q(age__gt=29)),
Avg("age"))
self.assertEqual(vals, {
"age__sum": 254,
"age__avg": Approximate(37.4, places=1)
})
def test_single_aggregate(self):
vals = Author.objects.aggregate(Avg("age"))
self.assertEqual(vals, {"age__avg": Approximate(37.4, places=1)})
vals = Author.objects.aggregate(Sum("age", only=Q(age__gt=29)))
self.assertEqual(vals, {"age__sum": 254})
vals = Author.objects.extra(select={'testparams':'age < %s'}, select_params=[0])\
.aggregate(Sum("age", only=Q(age__gt=29)))
self.assertEqual(vals, {"age__sum": 254})
vals = Author.objects.aggregate(
Sum("age",
only=Q(name__icontains='jaco') | Q(name__icontains='adrian')))
self.assertEqual(vals, {"age__sum": 69})
def get_aggregates(tipologia=None, **kwargs):
if not tipologia:
aggregation_struct = {
'sum': Sum('importo'),
'count': Count('importo')
}
else:
tipologia_id, tipologia_shortname = tipologia
aggregation_struct = {
'sum': Sum('importo', only=Q(tipologia_cedente=tipologia_id)),
'count': Count('importo', only=Q(tipologia_cedente=tipologia_id))
}
return Donazione.objects.filter(**kwargs).aggregate(**aggregation_struct)
def get_aggregates(tipologia=None, **kwargs):
importo_field = 'donazione__importo'
if not tipologia:
aggregation_struct = {
'sum': Sum(importo_field),
'count': Count(importo_field)
}
else:
tipologia_id, tipologia_shortname = tipologia
aggregation_struct = {
'sum': Sum(importo_field, only=Q(donazione__tipologia_cedente=tipologia_id)),
'count': Count(importo_field, only=Q(donazione__tipologia_cedente=tipologia_id))
}
return DonazioneInterventoProgramma.objects.filter(**kwargs).aggregate(**aggregation_struct)
def test_related_aggregate(self):
vals = Author.objects.aggregate(Avg("friends__age"))
self.assertEqual(len(vals), 1)
self.assertAlmostEqual(vals["friends__age__avg"], 34.07, places=2)
vals = Author.objects.aggregate(Avg("friends__age",
only=Q(age__lt=29)))
self.assertEqual(len(vals), 1)
self.assertAlmostEqual(vals["friends__age__avg"], 33.67, places=2)
vals2 = Author.objects.filter(age__lt=29).aggregate(
Avg("friends__age"))
self.assertEqual(vals, vals2)
vals = Author.objects.aggregate(
Avg("friends__age", only=Q(friends__age__lt=35)))
self.assertEqual(len(vals), 1)
self.assertAlmostEqual(vals["friends__age__avg"], 28.75, places=2)
# The average age of author's friends, whose age is lower than the authors age.
vals = Author.objects.aggregate(
Avg("friends__age", only=Q(friends__age__lt=F('age'))))
self.assertEqual(len(vals), 1)
self.assertAlmostEqual(vals["friends__age__avg"], 30.43, places=2)
vals = Book.objects.filter(rating__lt=4.5).aggregate(
Avg("authors__age"))
self.assertEqual(len(vals), 1)
self.assertAlmostEqual(vals["authors__age__avg"], 38.2857, places=2)
vals = Author.objects.all().filter(name__contains="a").aggregate(
Avg("book__rating"))
self.assertEqual(len(vals), 1)
self.assertEqual(vals["book__rating__avg"], 4.0)
vals = Book.objects.aggregate(Sum("publisher__num_awards"))
self.assertEqual(len(vals), 1)
self.assertEqual(vals["publisher__num_awards__sum"], 30)
vals = Publisher.objects.aggregate(Sum("book__price"))
self.assertEqual(len(vals), 1)
self.assertEqual(vals["book__price__sum"], Decimal("270.27"))
def test_reverse_fkey_annotate(self):
books = Book.objects.annotate(
Sum("publisher__num_awards")).order_by("name")
self.assertQuerysetEqual(
books,
[('Artificial Intelligence: A Modern Approach', 7),
('Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp',
9), ('Practical Django Projects', 3),
('Python Web Development with Django', 7),
('Sams Teach Yourself Django in 24 Hours', 1),
('The Definitive Guide to Django: Web Development Done Right', 3)
], lambda b: (b.name, b.publisher__num_awards__sum))
publishers = Publisher.objects.annotate(
Sum("book__price")).order_by("name")
self.assertQuerysetEqual(publishers,
[('Apress', Decimal("59.69")),
("Jonno's House of Books", None),
('Morgan Kaufmann', Decimal("75.00")),
('Prentice Hall', Decimal("112.49")),
('Sams', Decimal("23.09"))], lambda p:
(p.name, p.book__price__sum))
def latest_leaderboard(request,
year=datetime.datetime.now().year,
sector='all',
size='all',
parentid=None,
selected_month='all'):
# Obtain the context from the HTTP request.
context = RequestContext(request)
if year is None:
return redirect('2017/')
d = {}
parent = None
# use the year to set filtering on Employer, Commutersurvey, etc.
activeFilter = "active{0}".format(year)
if parentid: # this is a bunch of subteams
parent = Employer.objects.filter(**{
activeFilter: True
}).get(id=parentid)
teams = Team.objects.only('id', 'name').filter(parent_id=parentid)
survey_data = teams
else: # this is a bunch of companies
companies = Employer.objects.only('id', 'name').exclude(
id__in=[32, 33, 34, 38, 39, 40]).filter(**{activeFilter: True})
# Filtering the results by size
if size == 'small':
companies = companies.filter(nr_employees__lte=50)
elif size == 'medium':
companies = companies.filter(nr_employees__gt=50,
nr_employees__lte=300)
elif size == 'large':
companies = companies.filter(nr_employees__gt=300,
nr_employees__lte=2000)
elif size == 'largest':
companies = companies.filter(nr_employees__gt=2000)
# Filtering the results by sector
if sector != 'all':
selected_sector = Sector.objects.get(short=sector).name
companies = companies.filter(sector__short=sector)
else:
selected_sector = ''
selected_sector_name = 'All Sectors'
survey_data = companies
if selected_month != 'all':
months_dict = {
'january': '01',
'february': '02',
'march': '03',
'april': '04',
'may': '05',
'june': '06',
'july': '07',
'august': '08',
'september': '09',
'october': '10',
'november': '11',
'december': '12'
}
shortmonth = months_dict[selected_month]
month_model = Month.objects.filter(wr_day__year=year,
wr_day__month=shortmonth)
survey_data = survey_data.filter(
commutersurvey__wr_day_month=month_model)
else:
month_models = Month.objects.filter(wr_day__year=year).exclude(
wr_day__month='01').exclude(wr_day__month='02').exclude(
wr_day__month='03').exclude(wr_day__month='11').exclude(
wr_day__month='12')
survey_data = survey_data.filter(
commutersurvey__wr_day_month=month_models)
survey_data = survey_data.annotate(
saved_carbon=Sum('commutersurvey__carbon_savings'),
overall_calories=Sum('commutersurvey__calories_total'),
num_checkins=Count('commutersurvey'))
totals = survey_data.aggregate(total_carbon=Sum('saved_carbon'),
total_calories=Sum('overall_calories'),
total_checkins=Sum('num_checkins'))
for company in survey_data:
# links is company id, then optionally team id
if hasattr(company, 'parent'): # then this is a team
links = (company.parent.id, company.id)
else:
links = (company.id, )
d[(str(company.name),
links)] = calculate_metrics(company, selected_month, year)
ranks = calculate_rankings(d)
sectors_dict = dict(Sector.objects.values_list('short', 'name'))
months_arr = [
'april', 'may', 'june', 'july', 'august', 'september', 'october'
]
sizes_arr = [('small', 'Small (fewer than 50)'),
('medium', 'Medium (51 to 300)'),
('large', 'Large (301 to 2000)'),
('largest', 'Largest (2001+ employees)')]
return render_to_response(
'leaderboard/leaderboard_new.html', {
'year':
year,
'ranks':
ranks,
'totals':
totals,
'request':
request,
'employersWithSubteams':
Employer.objects.filter(**{
activeFilter: True
}).filter(team__isnull=False).distinct(),
'size':
size,
'selected_month':
selected_month,
'parent':
parent,
'selected_sector':
sector,
'sizes_list':
sizes_arr,
'months_list':
months_arr,
'sectors_list':
sectors_dict
}, context)