def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible,
to reduce database trips.
"""
# Annotate with the total 'in stock' quantity
queryset = queryset.annotate(
in_stock=Coalesce(
SubquerySum('stock_items__quantity', filter=StockItem.IN_STOCK_FILTER),
Decimal(0)
),
)
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items')
)
# Filter to limit builds to "active"
build_filter = Q(
status__in=BuildStatus.ACTIVE_CODES
)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(
building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
)
)
# Filter to limit orders to "open"
order_filter = Q(
order__status__in=PurchaseOrderStatus.OPEN
)
# Annotate with the total 'on order' quantity
queryset = queryset.annotate(
ordering=Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__quantity', filter=order_filter),
Decimal(0),
) - Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__received', filter=order_filter),
Decimal(0),
)
)
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(
suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
),
)
return queryset
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible,
to reduce database trips.
"""
# TODO: Update the "in_stock" annotation to include stock for variants of the part
# Ref: https://github.com/inventree/InvenTree/issues/2240
# Annotate with the total 'in stock' quantity
queryset = queryset.annotate(in_stock=Coalesce(
SubquerySum('stock_items__quantity',
filter=StockItem.IN_STOCK_FILTER),
Decimal(0),
output_field=models.DecimalField(),
), )
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items'))
# Filter to limit builds to "active"
build_filter = Q(status__in=BuildStatus.ACTIVE_CODES)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Filter to limit orders to "open"
order_filter = Q(order__status__in=PurchaseOrderStatus.OPEN)
# Annotate with the total 'on order' quantity
queryset = queryset.annotate(ordering=Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__quantity',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
) - Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__received',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
output_field=models.DecimalField(),
), )
return queryset
def test_player_count(self):
team1_count_subquery_count = SubqueryCount('team1__players')
team2_count_subquery_count = SubqueryCount('team2__players')
games = Game.objects.annotate(team1_count=team1_count_subquery_count,
team2_count=team2_count_subquery_count)
for g in games:
self.assertEqual(g.team1_count, g.team1.players.count())
self.assertEqual(g.team2_count, g.team2.players.count())
def annotate_queryset(queryset):
# Add count of parts manufactured
queryset = queryset.annotate(
parts_manufactured=SubqueryCount('manufactured_parts'))
queryset = queryset.annotate(
parts_supplied=SubqueryCount('supplied_parts'))
return queryset
def popular_books_with_author_count_easy_subquery(request, **context):
annotations = {
'rating': SubqueryAvg('userrating__rating'),
'rating_count': SubqueryCount('userrating'),
'author_count': SubqueryCount('authors')
}
books = Book.objects.annotate(**annotations).order_by('-rating')[:50]
context.update({
'books': books
})
return render(request, 'popular_books_author_count.html', context)
def annotate_queryset(queryset):
"""Annoate the supplied queryset with aggregated information"""
# Add count of parts manufactured
queryset = queryset.annotate(
parts_manufactured=SubqueryCount('manufactured_parts')
)
queryset = queryset.annotate(
parts_supplied=SubqueryCount('supplied_parts')
)
return queryset
def annotate_queryset(queryset):
"""Add some extra annotations to the queryset.
Performing database queries as efficiently as possible, to reduce database trips.
"""
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items'))
# Annotate with the total variant stock quantity
variant_query = part.filters.variant_stock_query()
queryset = queryset.annotate(
variant_stock=part.filters.annotate_variant_quantity(
variant_query, reference='quantity'), )
# Filter to limit builds to "active"
build_filter = Q(status__in=BuildStatus.ACTIVE_CODES)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
output_field=models.DecimalField(),
), )
queryset = queryset.annotate(
ordering=part.filters.annotate_on_order_quantity(),
in_stock=part.filters.annotate_total_stock(),
allocated_to_sales_orders=part.filters.
annotate_sales_order_allocations(),
allocated_to_build_orders=part.filters.
annotate_build_order_allocations(),
)
# Annotate with the total 'available stock' quantity
# This is the current stock, minus any allocations
queryset = queryset.annotate(unallocated_stock=ExpressionWrapper(
F('in_stock') - F('allocated_to_sales_orders') -
F('allocated_to_build_orders'),
output_field=models.DecimalField(),
))
return queryset
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_count_forward(self):
annotation = {
'author_count': SubqueryCount('authors')
}
books = Book.objects.annotate(**annotation).order_by('id')
counts = {book.title: book.author_count for book in books}
self.assertEqual(counts, {'Book 1': 1, 'Book 2': 2, 'Book 3': 2, 'Book 4': 1})
def test_subquery_count(self):
annotation = {'child_count': SubqueryCount('child')}
parents = Parent.objects.annotate(**annotation)
counts = {parent.name: parent.child_count for parent in parents}
self.assertEqual(counts, {'John': 3, 'Jane': 0})
def test_self_join(self):
annotation = {'book_author_count': SubqueryCount('book__bookauthor')}
book_authors = BookAuthor.objects.annotate(**annotation)
counts = {ba.id: ba.book_author_count for ba in book_authors}
self.assertEqual(counts, {1: 1, 2: 2, 3: 2, 4: 2, 5: 2, 6: 1})
def annotate_queryset(queryset):
"""
Add extra information to the queryset
"""
queryset = queryset.annotate(line_items=SubqueryCount('lines'))
return queryset
def test_subquery_count_reverse(self):
annotation = {
'book_count': SubqueryCount('authored_books')
}
authors = Author.objects.annotate(**annotation).order_by('id')
counts = {author.name: author.book_count for author in authors}
self.assertEqual(counts, {'Author 1': 1,
'Author 2': 1,
'Author 3': 2,
'Author 4': 1,
'Author 5': 1,
'Author 6': 0})
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()
def update_coder_n_accounts_and_n_contests(signal, instance, action, reverse,
pk_set, **kwargs):
when, action = action.split('_', 1)
if when != 'post' or action not in ['add', 'remove']:
return
if reverse:
instance.n_accounts = instance.account_set.count()
instance.n_contests = instance.account_set.aggregate(
total=Sum('n_contests'))['total'] or 0
instance.save()
else:
Coder.objects.filter(pk__in=pk_set) \
.annotate(n_a=SubqueryCount('account')) \
.annotate(n_c=SubquerySum('account__n_contests')) \
.update(n_accounts=F('n_a'), n_contests=Coalesce('n_c', 0))
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible.
"""
# Annotate the queryset with the total allocated to sales orders
queryset = queryset.annotate(
allocated=Coalesce(
SubquerySum('sales_order_allocations__quantity'), Decimal(0)) +
Coalesce(SubquerySum('allocations__quantity'), Decimal(0)))
# Annotate the queryset with the number of tracking items
queryset = queryset.annotate(
tracking_items=SubqueryCount('tracking_info'))
return queryset
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible.
"""
# Annotate the queryset with the total allocated to sales orders
queryset = queryset.annotate(
allocated=Coalesce(
SubquerySum('sales_order_allocations__quantity'), Decimal(0)
) + Coalesce(
SubquerySum('allocations__quantity'), Decimal(0)
)
)
# Annotate the queryset with the number of tracking items
queryset = queryset.annotate(
tracking_items=SubqueryCount('tracking_info')
)
# Add flag to indicate if the StockItem has expired
queryset = queryset.annotate(
expired=Case(
When(
StockItem.EXPIRED_FILTER, then=Value(True, output_field=BooleanField()),
),
default=Value(False, output_field=BooleanField())
)
)
# Add flag to indicate if the StockItem is stale
stale_days = common.models.InvenTreeSetting.get_setting('STOCK_STALE_DAYS')
stale_date = datetime.now().date() + timedelta(days=stale_days)
stale_filter = StockItem.IN_STOCK_FILTER & ~Q(expiry_date=None) & Q(expiry_date__lt=stale_date)
queryset = queryset.annotate(
stale=Case(
When(
stale_filter, then=Value(True, output_field=BooleanField()),
),
default=Value(False, output_field=BooleanField()),
)
)
return queryset
def annotate_queryset(queryset):
"""
Add extra information to the queryset
- Number of liens in the PurchaseOrder
- Overdue status of the PurchaseOrder
"""
queryset = queryset.annotate(line_items=SubqueryCount('lines'))
queryset = queryset.annotate(
overdue=Case(When(
PurchaseOrder.OVERDUE_FILTER,
then=Value(True, output_field=BooleanField()),
),
default=Value(False, output_field=BooleanField())))
return queryset
def get_queryset(self):
return core.models.Space.objects.owned(self.request.user).annotate(
files_count=SubqueryCount('files'),
files_total_size=SubquerySum('files__content_length'),
)
def party(request, slug, tab='ranking'):
party = get_object_or_404(Party.objects.for_user(request.user), slug=slug)
party_contests = Contest.objects \
.filter(rating__party=party) \
.annotate(has_statistics=Exists('statistics')) \
.order_by('-end_time')
filt = Q(rating__party=party, statistics__account__coders=OuterRef('pk'))
coders = party.coders \
.annotate(n_participations=SubqueryCount('account__resource__contest', filter=filt)) \
.order_by('-n_participations') \
.select_related('user')
set_coders = set(coders)
if request.user.is_authenticated:
ignore_filters = request.user.coder.filter_set.filter(
categories__contains=['calendar']).order_by('created')
ignore_filters = list(ignore_filters.values('id', 'name'))
else:
ignore_filters = []
ignore_filters.append({'id': 0, 'name': 'disable long'})
results = []
total = {}
contests = Contest.objects.filter(rating__party=party)
future = contests.filter(
end_time__gt=timezone.now()).order_by('start_time')
statistics = Statistics.objects.filter(
account__coders__in=party.coders.all(),
contest__in=party_contests.filter(start_time__lt=timezone.now()),
contest__end_time__lt=timezone.now(),
) \
.order_by('-contest__end_time') \
.select_related('contest', 'account') \
.prefetch_related('account__coders', 'account__coders__user')
contests_standings = collections.OrderedDict(
(c, {}) for c in contests.filter(
end_time__lt=timezone.now()).order_by('-end_time'))
for statistic in statistics:
contest = statistic.contest
for coder in statistic.account.coders.all():
if coder in set_coders:
standings = contests_standings[contest].setdefault(
statistic.addition.get('division', '__none__'), [])
standings.append({
'solving': statistic.solving,
'upsolving': statistic.upsolving,
'stat': statistic,
'coder': coder,
})
for contest, divisions in contests_standings.items():
standings = []
fields = collections.OrderedDict()
if len(divisions) > 1 or '__none__' not in divisions:
fields['division'] = ('Div', 'division', 'Division')
for division, statistics in divisions.items():
if statistics:
max_solving = max([s['solving'] for s in statistics]) or 1
max_total = max(
[s['solving'] + s['upsolving'] for s in statistics]) or 1
for s in statistics:
solving = s['solving']
upsolving = s['upsolving']
s['score'] = 4. * (
solving +
upsolving) / max_total + 1. * solving / max_solving
s['interpretation'] = f'4 * ({solving} + {upsolving}) / {max_total} + {solving} / {max_solving}'
s['division'] = s['stat'].addition.get('division',
'').replace(
'_', ' ')
max_score = max([s['score'] for s in statistics]) or 1
for s in statistics:
s['score'] = 100. * s['score'] / max_score
s['interpretation'] = [
f'100 * ({s["interpretation"]}) / {max_score}'
]
for s in statistics:
coder = s['coder']
d = total.setdefault(coder.id, {})
d['score'] = s['score'] + d.get('score', 0)
d['coder'] = coder
d['num'] = d.setdefault('num', 0) + 1
d['avg'] = f"{(d['score'] / d['num']):.2f}"
d, s = d.setdefault('stat', {}), s['stat']
solved = s.addition.get('solved', {})
d['solving'] = solved.get('solving', s.solving) + d.get(
'solving', 0)
d['upsolving'] = solved.get(
'upsolving', s.upsolving) + d.get('upsolving', 0)
standings.extend(statistics)
standings.sort(key=lambda s: s['score'], reverse=True)
results.append({
'contest': contest,
'standings': standings,
'fields': list(fields.values()),
})
total = sorted(list(total.values()),
key=lambda d: d['score'],
reverse=True)
results.insert(
0, {
'standings':
total,
'fields': [('Num', 'num', 'Number contests'),
('Avg', 'avg', 'Average score')],
})
for result in results:
place = 0
prev = None
for i, s in enumerate(result['standings']):
if prev != s['score']:
prev = s['score']
place = i + 1
s['place'] = place
return render(
request,
'party.html',
{
'ignore_filters': [],
'fixed_ignore_filters': ignore_filters,
'timezone': get_timezone(request),
'future': future,
'party': party,
'party_contests': party_contests,
'results': results,
'coders': coders,
'tab': 'ranking' if tab is None else tab,
},
)
def annotate_queryset(queryset):
"""
Add some extra annotations to the queryset,
performing database queries as efficiently as possible,
to reduce database trips.
"""
# Annotate with the total 'in stock' quantity
queryset = queryset.annotate(in_stock=Coalesce(
SubquerySum('stock_items__quantity',
filter=StockItem.IN_STOCK_FILTER),
Decimal(0),
output_field=models.DecimalField(),
), )
# Annotate with the total number of stock items
queryset = queryset.annotate(
stock_item_count=SubqueryCount('stock_items'))
# Annotate with the total variant stock quantity
variant_query = StockItem.objects.filter(
part__tree_id=OuterRef('tree_id'),
part__lft__gt=OuterRef('lft'),
part__rght__lt=OuterRef('rght'),
).filter(StockItem.IN_STOCK_FILTER)
queryset = queryset.annotate(variant_stock=Coalesce(
Subquery(
variant_query.annotate(total=Func(
F('quantity'), function='SUM',
output_field=FloatField())).values('total')),
0,
output_field=FloatField(),
))
# Filter to limit builds to "active"
build_filter = Q(status__in=BuildStatus.ACTIVE_CODES)
# Annotate with the total 'building' quantity
queryset = queryset.annotate(building=Coalesce(
SubquerySum('builds__quantity', filter=build_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Filter to limit orders to "open"
order_filter = Q(order__status__in=PurchaseOrderStatus.OPEN)
# Annotate with the total 'on order' quantity
queryset = queryset.annotate(ordering=Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__quantity',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
) - Coalesce(
SubquerySum('supplier_parts__purchase_order_line_items__received',
filter=order_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the number of 'suppliers'
queryset = queryset.annotate(suppliers=Coalesce(
SubqueryCount('supplier_parts'),
Decimal(0),
output_field=models.DecimalField(),
), )
"""
Annotate with the number of stock items allocated to sales orders.
This annotation is modeled on Part.sales_order_allocations() method:
- Only look for "open" orders
- Stock items have not been "shipped"
"""
so_allocation_filter = Q(
line__order__status__in=SalesOrderStatus.
OPEN, # LineItem points to an OPEN order
shipment__shipment_date=
None, # Allocated item has *not* been shipped out
)
queryset = queryset.annotate(allocated_to_sales_orders=Coalesce(
SubquerySum('stock_items__sales_order_allocations__quantity',
filter=so_allocation_filter),
Decimal(0),
output_field=models.DecimalField(),
))
"""
Annotate with the number of stock items allocated to build orders.
This annotation is modeled on Part.build_order_allocations() method
"""
bo_allocation_filter = Q(build__status__in=BuildStatus.ACTIVE_CODES, )
queryset = queryset.annotate(allocated_to_build_orders=Coalesce(
SubquerySum('stock_items__allocations__quantity',
filter=bo_allocation_filter),
Decimal(0),
output_field=models.DecimalField(),
))
# Annotate with the total 'available stock' quantity
# This is the current stock, minus any allocations
queryset = queryset.annotate(unallocated_stock=ExpressionWrapper(
F('in_stock') - F('allocated_to_sales_orders') -
F('allocated_to_build_orders'),
output_field=models.DecimalField(),
))
return queryset
