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