def test_subquery_count_reverse_explicit(self):
# The two queries are the same, one just passes a long version of joining from author to books,
# this test verifies that the automatic reverse of the joins handles both cases.
# The annotation is a bit non-sensical, taking the Max over titles, but that isn't the point
annotation = {
'max_book_title': SubqueryMax('bookauthor__book__title')
}
authors = Author.objects.annotate(**annotation).order_by('id')
titles = {author.name: author.max_book_title for author in authors}
self.assertEqual(titles, {'Author 1': 'Book 1',
'Author 2': 'Book 2',
'Author 3': 'Book 3',
'Author 4': 'Book 3',
'Author 5': 'Book 4',
'Author 6': None})
annotation = {
'max_book_title': SubqueryMax('authored_books__title')
}
authors = Author.objects.annotate(**annotation).order_by('id')
titles = {author.name: author.max_book_title for author in authors}
self.assertEqual(titles, {'Author 1': 'Book 1',
'Author 2': 'Book 2',
'Author 3': 'Book 3',
'Author 4': 'Book 3',
'Author 5': 'Book 4',
'Author 6': None})
def main(host=None):
accounts = Account.objects
if host:
accounts = accounts.filter(resource__host__regex=host)
print(timezone.now(), accounts.count())
filt = Q(addition___no_update_n_contests__isnull=True) | Q(addition___no_update_n_contests=False)
qs = accounts.annotate(count=SubqueryCount('statistics', filter=filt))
qs = qs.exclude(n_contests=F('count'))
total = qs.count()
print(timezone.now(), total)
with tqdm(total=total) as pbar:
with transaction.atomic():
for a in qs.iterator():
a.n_contests = a.count
a.save()
pbar.update()
print(timezone.now())
filt = Q(statistics__addition___no_update_n_contests__isnull=True)
qs = accounts.annotate(last=SubqueryMax('statistics__contest__start_time', filter=filt))
qs = qs.exclude(last_activity=F('last'))
total = qs.count()
print(timezone.now(), total)
with tqdm(total=total) as pbar:
with transaction.atomic():
for a in qs.iterator():
a.last_activity = a.last
a.save()
pbar.update()
print(timezone.now())
def test_subquery_max(self):
annotation = {
'youngest_child_timestamp': SubqueryMax('child__timestamp',
output_field=DateTimeField())
}
parents = Parent.objects.filter(name='John').annotate(**annotation)
youngest_child = Child.objects.filter(parent__name='John').order_by('-timestamp')[0]
self.assertEqual(parents[0].youngest_child_timestamp, youngest_child.timestamp)
def test_reverse_foreign_key(self):
annotations = {
'max_price': SubqueryMax('package__purchase__price'),
'min_price': SubqueryMin('package__purchase__price')
}
catalogs = Catalog.objects.annotate(**annotations)
prices = {catalog.number: (catalog.max_price, catalog.min_price) for catalog in catalogs}
self.assertEqual(prices, {'A': (6, 4),
'B': (12, 11)})
def test_forward_and_reverse_foreign_keys(self):
annotations = {
'max_price': SubqueryMax('catalog__package__purchase__price'),
'min_price': SubqueryMin('catalog__package__purchase__price')
}
catalog_infos = CatalogInfo.objects.annotate(**annotations)
extremes = {info.info: (info.max_price, info.min_price) for info in catalog_infos}
self.assertEqual(extremes, {'cat A info': (6, 4),
'cat B info': (12, 11)})
def test_subquery_min_through_m2m_and_foreign_key(self):
annotation = {
'max_publisher_number': SubqueryMax('authored_books__publisher__number')
}
authors = Author.objects.annotate(**annotation)
numbers = {author.name: author.max_publisher_number for author in authors}
self.assertEqual(numbers, {'Author 1': 1,
'Author 2': 1,
'Author 3': 2,
'Author 4': 2,
'Author 5': 2,
'Author 6': None})
def coders(request, template='coders.html'):
coders = Coder.objects.select_related('user')
params = {}
search = request.GET.get('search')
if search:
filt = get_iregex_filter(search, 'username', logger=request.logger)
coders = coders.filter(filt)
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
coders = coders.annotate(filter_country=Exists('account', filter=Q(account__country__in=countries)))
coders = coders.filter(Q(country__in=countries) | Q(filter_country=True))
params['countries'] = countries
resources = request.GET.getlist('resource')
if resources:
resources = [r for r in resources if r]
resources = list(Resource.objects.filter(pk__in=resources))
for r in resources:
coders = coders.annotate(**{f'{r.pk}_rating': SubqueryMax('account__rating', filter=Q(resource=r))})
coders = coders.annotate(**{f'{r.pk}_n_contests': SubquerySum('account__n_contests', filter=Q(resource=r))})
params['resources'] = resources
# ordering
orderby = request.GET.get('sort_column')
if orderby in ['username', 'created', 'n_accounts']:
pass
elif orderby and orderby.startswith('resource_'):
_, pk = orderby.split('_')
orderby = [f'{pk}_rating', f'{pk}_n_contests']
elif orderby:
request.logger.error(f'Not found `{orderby}` column for sorting')
orderby = []
orderby = orderby if not orderby or isinstance(orderby, list) else [orderby]
order = request.GET.get('sort_order')
if order in ['asc', 'desc']:
orderby = [getattr(F(o), order)(nulls_last=True) for o in orderby]
elif order:
request.logger.error(f'Not found `{order}` order for sorting')
orderby = orderby or ['-created']
coders = coders.order_by(*orderby)
context = {
'coders': coders,
'params': params,
}
return template, context
def main(host=None):
resources = Resource.objects.order_by('n_accounts')
if host:
resources = resources.filter(host__regex=host)
total = resources.count()
with tqdm(total=total, desc='resources') as pbar_resource:
for resource in resources.iterator():
start_time = timezone.now()
accounts = Account.objects.filter(resource=resource)
qs = accounts.annotate(count=SubqueryCount(
'statistics',
filter=(Q(addition___no_update_n_contests__isnull=True)
| Q(addition___no_update_n_contests=False)),
), ).annotate(last=SubqueryMax(
'statistics__contest__start_time',
filter=(
Q(statistics__addition___no_update_n_contests__isnull=True)
| Q(statistics__addition___no_update_n_contests=False)),
), )
total = 0
n_contests_diff = 0
n_last_act_diff = 0
with tqdm(desc='accounts') as pbar:
for a in qs.iterator():
total += 1
to_save = False
if a.count != a.n_contests:
n_contests_diff += 1
a.n_contests = a.count
to_save = True
if a.last != a.last_activity:
n_last_act_diff += 1
a.last_activity = a.last
to_save = True
if to_save:
a.save()
pbar.update()
pbar.close()
pbar_resource.set_postfix(
resource=resource.host,
time=timezone.now() - start_time,
total=accounts.count(),
n_contests_diff=n_contests_diff,
n_last_act_diff=n_last_act_diff,
)
pbar_resource.update()
pbar_resource.close()
