def test_outer_join(self): REGIONS = ("bernard's star", "sun") orders = With( Order.objects.values('region_id').annotate( amount=Sum(F('amount')))) joined_inner = (orders.join( Region.objects.filter(name__in=REGIONS), name=orders.col.region_id).with_cte(orders).annotate( amount=orders.col.amount)) data_inner = [(_.name, _.amount) for _ in joined_inner] self.assertEqual(data_inner, [ ('sun', 1000), ]) joined_outer = (orders.join( Region.objects.filter(name__in=('sun', "bernard's star")), name=orders.col.region_id, _join_type=LOUTER).with_cte(orders).annotate( amount=orders.col.amount)) data_outer = [(_.name, _.amount) for _ in joined_outer] self.assertEqual(data_outer, [ ("bernard's star", None), ('sun', 1000), ])
def get_available_approvals(self, as_user): those_with_max_priority = With( TransitionApproval.objects.filter( workflow=self.workflow, status=PENDING, skipped=False, enabled=True ).values( 'workflow', 'object_id', 'source_state', 'destination_state' ).annotate(min_priority=Min('priority')) ) workflow_objects = With( self.wokflow_object_class.objects.all(), name="workflow_object" ) approvals_with_max_priority = those_with_max_priority.join( self._authorized_approvals(as_user), workflow_id=those_with_max_priority.col.workflow_id, object_id=those_with_max_priority.col.object_id, source_state_id=those_with_max_priority.col.source_state_id, destination_state_id=those_with_max_priority.col.destination_state_id, ).with_cte( those_with_max_priority ).annotate( object_id_as_int=Cast('object_id', IntegerField()), min_priority=those_with_max_priority.col.min_priority ).filter(min_priority=F("priority")) return workflow_objects.join( approvals_with_max_priority, object_id_as_int=workflow_objects.col.pk ).with_cte( workflow_objects ).filter(source_state=getattr(workflow_objects.col, self.field_name + "_id"))
def get_available_approvals(self, as_user): those_with_max_priority = With( TransitionApproval.objects.filter( workflow=self.workflow, status=PENDING).values( 'workflow', 'object_id', 'transition').annotate(min_priority=Min('priority'))) workflow_objects = With(self.wokflow_object_class.objects.all(), name="workflow_object") approvals_with_max_priority = those_with_max_priority.join( self._authorized_approvals(as_user), workflow_id=those_with_max_priority.col.workflow_id, object_id=those_with_max_priority.col.object_id, transition_id=those_with_max_priority.col.transition_id, ).with_cte(those_with_max_priority).annotate( object_id_as_str=Cast('object_id', CharField(max_length=200)), min_priority=those_with_max_priority.col.min_priority).filter( min_priority=F("priority")) return workflow_objects.join( approvals_with_max_priority, object_id_as_str=Cast( workflow_objects.col.pk, CharField(max_length=200))).with_cte(workflow_objects).filter( transition__source_state=getattr(workflow_objects.col, self.field_name + "_id"))
def requires_update(self, requirement=True, include_archived=False): """ A ``TransactionCheck`` requires an update if it or any check on a transaction before it in order is stale or no longer current. If a ``TransactionCheck`` on an earlier transaction is stale, it means that transaction has been modified since the check was done, which could also invalidate any checks of any subsequent transactions. By default transactions in ARCHIVED workbaskets are ignored, since these workbaskets exist outside of the normal workflow. """ if include_archived: ignore_filter = {} else: ignore_filter = {"transaction__workbasket__status": "ARCHIVED"} # First filtering out any objects we should ignore, # work out for each check whether it alone requires an update, by # seeing whether it is stale or not current. basic_info = With( self.model.objects.exclude(**ignore_filter).annotate( **self.freshness_annotations).annotate( requires_update=self.requires_update_annotation, ), name="basic_info", ) # Now cascade that result down to any subsequent transactions: if a # transaction in the same workbasket comes later, then it will also # require an update. TODO: do stale transactions pollute the update # check for ever? sequence_info = With( basic_info.join( self.model.objects.all(), pk=basic_info.col.pk).annotate( requires_update=expressions.Window( expression=BoolOr(basic_info.col.requires_update), partition_by=models.F("transaction__workbasket"), order_by=[ models.F("transaction__order").asc(), models.F("pk").desc(), ], ), ), name="sequence_info", ) # Now filter for only the type that we want: checks that either do or do # not require an update. return (sequence_info.join(self, pk=sequence_info.col.pk).with_cte( basic_info).with_cte(sequence_info).annotate( requires_update=sequence_info.col.requires_update).filter( requires_update=requirement))
def test_simple_cte_query(self): totals = With( Order.objects.filter( region__parent="sun").values("region_id").annotate( total=Sum("amount"))) orders = (totals.join( Order, region=totals.col.region_id).with_cte(totals).annotate( region_total=totals.col.total).order_by("amount")) data = [(o.amount, o.region_id, o.region_total) for o in orders] self.assertEqual(data, [ (10, 'mercury', 33), (11, 'mercury', 33), (12, 'mercury', 33), (20, 'venus', 86), (21, 'venus', 86), (22, 'venus', 86), (23, 'venus', 86), (30, 'earth', 126), (31, 'earth', 126), (32, 'earth', 126), (33, 'earth', 126), (40, 'mars', 123), (41, 'mars', 123), (42, 'mars', 123), ])
def _cte_get_location_fixture_queryset(user): user_locations = user.get_sql_locations(user.domain) if user_locations.query.is_empty(): return user_locations fixture_ids = With( raw_cte_sql( """ SELECT "id", "path", "depth" FROM get_location_fixture_ids(%s::TEXT, %s) """, [ user.domain, list(user_locations.order_by().values_list("id", flat=True)) ], { "id": int_field, "path": int_array, "depth": int_field }, )) result = fixture_ids.join( SQLLocation.objects.all(), id=fixture_ids.col.id, ).annotate( path=fixture_ids.col.path, depth=fixture_ids.col.depth, ).with_cte(fixture_ids).prefetch_related('location_type', 'parent') return result
def complete_annotations(self, queryset): queryset = queryset.annotate(**self.annotations) editors_query = (User.objects.filter( editable_channels__id=OuterRef("id")).values_list( "id", flat=True).distinct()) viewers_query = (User.objects.filter( view_only_channels__id=OuterRef("id")).values_list( "id", flat=True).distinct()) nodes = With( ContentNode.objects.values( "id", "tree_id").filter(tree_id__in=self.page_tree_ids).order_by(), name="nodes", ) file_query = (nodes.join( File, contentnode_id=nodes.col.id).with_cte(nodes).filter( contentnode__tree_id=OuterRef("main_tree__tree_id")).values( "checksum", "file_size").distinct()) queryset = queryset.annotate( editors_count=SQCount(editors_query, field="id"), viewers_count=SQCount(viewers_query, field="id"), size=SQSum(file_query, field="file_size"), ) return queryset
def get_adjusted_queryset(self): """Return a sorted and filtered QuerySet.""" self.ordering = self.request.GET.get('sort', self.get_ordering()) filter_value = self.request.GET.get('search', '').strip() lookups = {} for field in self.searchable_fields: key = '{0}__icontains'.format(field) lookups[key] = filter_value expressions = [ Q(**{key: value}) for key, value in lookups.items() # noqa: WPS110 ] direction = 'desc' if self.request.GET.get('descending') else 'asc' ordering = getattr(F(self.get_ordering()), direction) # Can be simplified when filtering on windows gets implemented # https://code.djangoproject.com/ticket/28333 queryset = self.get_queryset().order_by(ordering()) ids_nums = With( queryset.annotate(num=Window(RowNumber(), order_by=ordering()), ).values( 'id', 'num'), ) return ids_nums.join( queryset, id=ids_nums.col.id, ).with_cte(ids_nums).annotate(num=ids_nums.col.num).filter( reduce(__or__, expressions), )
def award_queryset(self): cte = With( ToptierAgency.objects.distinct("toptier_agency_id").filter( agency__isnull=False).annotate( agency_id=F("agency__id"), toptier_name=F("name")).values( "toptier_agency_id", "agency_id", "toptier_code", "toptier_name").order_by("toptier_agency_id", "-agency__toptier_flag", "agency_id")) filters = [ self.is_in_provided_def_codes, self.all_closed_defc_submissions, Q(treasury_account__isnull=False), ] annotations = { "id": cte.col.agency_id, "code": cte.col.toptier_code, "description": cte.col.toptier_name, # Currently, this endpoint can never have children. "children": Value([], output_field=ArrayField(IntegerField())), "award_count": self.unique_file_d_award_count(), "obligation": Coalesce(Sum("transaction_obligated_amount"), 0), "outlay": Coalesce( Sum( Case( When(self.final_period_submission_query_filters, then=F("gross_outlay_amount_by_award_cpe")), default=Value(0), )), 0, ), "total_budgetary_resources": Value(None, DecimalField()), # NULL for award spending } # Assuming it is more performant to fetch all rows once rather than # run a count query and fetch only a page's worth of results return (cte.join( FinancialAccountsByAwards, treasury_account__funding_toptier_agency_id=cte.col. toptier_agency_id).with_cte(cte).filter(*filters).annotate( agency_id=cte.col.agency_id, toptier_code=cte.col.toptier_code, toptier_name=cte.col.toptier_name).values( "agency_id", "toptier_code", "toptier_name").annotate( **annotations).values(*annotations))
def disaster_filter_function(filters: dict, download_type: str, values: List[str]): aggregation_mapping = { "disaster_recipient": _disaster_recipient_aggregations } def_codes = filters["def_codes"] query = filters.get("query") award_type_codes = filters.get("award_type_codes") award_filters = [ ~Q(total_loan_value=0) | ~Q(total_obligation_by_award=0) | ~Q(total_outlay_by_award=0) ] if query: query_text = query["text"] q = Q() for field in query["fields"]: q |= Q(**{f"{field}__icontains": query_text}) award_filters.append(q) if award_type_codes: award_filters.append(Q(type__in=award_type_codes)) faba_filters = [ filter_by_defc_closed_periods(), Q(disaster_emergency_fund__code__in=def_codes) ] dollar_annotations = { "inner_obligation": Coalesce(Sum("transaction_obligated_amount"), 0), "inner_outlay": Coalesce( Sum( Case( When(filter_by_latest_closed_periods(), then=F("gross_outlay_amount_by_award_cpe")), default=Value(0), )), 0, ), } cte = With( FinancialAccountsByAwards.objects.filter( *faba_filters).values("award_id").annotate( **dollar_annotations).exclude(inner_obligation=0, inner_outlay=0)) return (cte.join(AwardSearchView, award_id=cte.col.award_id).with_cte(cte).annotate( total_obligation_by_award=cte.col.inner_obligation, total_outlay_by_award=cte.col.inner_outlay).filter( *award_filters).values(*values).annotate( **aggregation_mapping[download_type]()))
def test_named_simple_ctes(self): totals = With( Order.objects.filter( region__parent="sun").values("region_id").annotate( total=Sum("amount")), name="totals", ) region_count = With( Region.objects.filter(parent="sun").values("parent").annotate( num=Count("name")), name="region_count", ) orders = (region_count.join( totals.join(Order, region=totals.col.region_id), region__parent=region_count.col.parent_id).with_cte( totals).with_cte(region_count).annotate( region_total=totals.col.total).annotate( region_count=region_count.col.num).order_by("amount")) print(orders.query) data = [( o.amount, o.region_id, o.region_count, o.region_total, ) for o in orders] self.assertEqual(data, [ (10, 'mercury', 4, 33), (11, 'mercury', 4, 33), (12, 'mercury', 4, 33), (20, 'venus', 4, 86), (21, 'venus', 4, 86), (22, 'venus', 4, 86), (23, 'venus', 4, 86), (30, 'earth', 4, 126), (31, 'earth', 4, 126), (32, 'earth', 4, 126), (33, 'earth', 4, 126), (40, 'mars', 4, 123), (41, 'mars', 4, 123), (42, 'mars', 4, 123), ])
def test_last_activity__cte(self): max_when = Activity.objects \ .values('who') \ .annotate(max_when=Max('when')) \ .values('who', 'max_when') cte = With(max_when) final_activities = cte.join(Activity, who=cte.col.who, when=cte.col.max_when).with_cte(cte) print(final_activities.query) print(final_activities)
def cte_proxy_libros_por_editorial(): total_libros = With(Libro.objects.values('editorial').annotate( total_libros=Count('isbn'), max_paginas=Max('paginas')), name="editorial_libros") total_libros2 = With( Libro.objects.values('editorial').annotate(min_paginas=Min('paginas')), name="editorial_libros2") # Usamos EditorialCustomManager en lugar del modelo original Editorial editorial_totales = (total_libros.join( total_libros2.join(EditorialCustomManager, id=total_libros2.col.editorial_id), id=total_libros.col.editorial_id).with_cte(total_libros).with_cte( total_libros2).annotate( libros_editorial=total_libros.col.total_libros, max_paginas=total_libros.col.max_paginas, min_paginas=total_libros2.col.min_paginas).values( 'id', 'nombre', 'libros_editorial', 'max_paginas', 'min_paginas')) return editorial_totales
def cte_libros_por_editorial(): total_libros = With( Libro.objects.values('editorial').annotate(total_libros=Count('isbn')), name='cuenta_libros') editorial_totales = (total_libros.join( Editorial, id=total_libros.col.editorial_id).with_cte(total_libros).annotate( libros_editorial=total_libros.col.total_libros).values( 'id', 'nombre', 'libros_editorial')) return editorial_totales
def _location_queryset_helper(domain, location_pks): fixture_ids = With(raw_cte_sql( """ SELECT "id", "path", "depth" FROM get_location_fixture_ids(%s::TEXT, %s) """, [domain, location_pks], {"id": int_field, "path": int_array, "depth": int_field}, )) return fixture_ids.join( SQLLocation.objects.all(), id=fixture_ids.col.id, ).annotate( path=fixture_ids.col.path, depth=fixture_ids.col.depth, ).with_cte(fixture_ids).prefetch_related('location_type', 'parent')
def test_last_activity__window_function(self): final_activities = Activity.objects.annotate(max_when=Window( expression=FirstValue('when'), partition_by=[F('who')], order_by=F('when').desc(), )) # final1 = final_activities.filter(when=F('max_when')) # print(final1.query) # will produce an sql error as django attempts to put the window function in the where clause # print(final1) cte = With(final_activities) final2 = cte.join(Activity, who=cte.col.who, when=cte.col.max_when).with_cte(cte) print(final2.query) print(final2)
def test_raw_cte_sql(self): cte = With( raw_cte_sql( """ SELECT region_id, AVG(amount) AS avg_order FROM tests_order WHERE region_id = %s GROUP BY region_id """, ["moon"], { "region_id": text_field, "avg_order": int_field }, )) moon_avg = (cte.join(Region, name=cte.col.region_id).annotate( avg_order=cte.col.avg_order).with_cte(cte)) data = [(r.name, r.parent.name, r.avg_order) for r in moon_avg] self.assertEqual(data, [('moon', 'earth', 2)])
def post(self, request: Request) -> Response: award_filters = [~Q(total_loan_value=0) | ~Q(total_obligation_by_award=0) | ~Q(total_outlay_by_award=0)] if self.award_type_codes: award_filters.append(Q(type__in=self.award_type_codes)) faba_filters = [ self.all_closed_defc_submissions, self.is_in_provided_def_codes, ] dollar_annotations = { "inner_obligation": self.obligated_field_annotation, "inner_outlay": self.outlay_field_annotation, } cte = With( FinancialAccountsByAwards.objects.filter(*faba_filters).values("award_id").annotate(**dollar_annotations) ) recipients = ( cte.join(AwardSearchView, award_id=cte.col.award_id) .with_cte(cte) .annotate(total_obligation_by_award=cte.col.inner_obligation, total_outlay_by_award=cte.col.inner_outlay) .filter(*award_filters) .values("award_id") .aggregate( count=Count( Case( When(recipient_name__in=SPECIAL_CASES, then=F("recipient_name")), default=Cast(F("recipient_hash"), output_field=TextField()), ), distinct=True, ) ) ) return Response({"count": recipients["count"]})
def article(request): top = ArticleMathematician.objects.values( 'mathematician__last_name', 'mathematician__first_name').annotate( total=Count('article')).order_by('-total')[:3] having = ArticleMathematician.objects.values( 'mathematician__last_name', 'mathematician__first_name').annotate( total=Count('article')).filter(total__gt=2) articles = ArticleMathematician.objects.values( 'mathematician__last_name', 'mathematician__first_name').annotate(total=Count('article')) # out = Mathematician.objects.raw('SELECT mathematician_id as id, last_name, first_name, COUNT ("article") as total FROM ' # 'main_mathematician OUTER JOIN main_articleMathematician GROUP BY mathematician_id HAVING total > 2 ') cte = With( Article.objects.values('year').annotate(total=Count('article_name'))) orders = (cte.join(Article, year=cte.col.year).with_cte(cte).annotate( article_total=cte.col.total).order_by('year')) context = {'art': articles, 'having': having, 'top': top, 'ord': orders} return render(request, 'articles.html', context)
def total_queryset(self): cte_filters = [ Q(treasury_account__isnull=False), self.all_closed_defc_submissions, self.is_in_provided_def_codes, self.is_non_zero_total_spending, ] cte_annotations = { "funding_toptier_agency_id": F("treasury_account__funding_toptier_agency_id"), "obligation": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then=F("obligations_incurred_by_program_object_class_cpe"), ), default=Value(0), ) ), 0, ), "outlay": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then=F("gross_outlay_amount_by_program_object_class_cpe"), ), default=Value(0), ) ), 0, ), } cte = With( FinancialAccountsByProgramActivityObjectClass.objects.filter(*cte_filters) .values("treasury_account__funding_toptier_agency_id") .annotate(**cte_annotations) .values(*cte_annotations) ) annotations = { "id": Subquery( Agency.objects.filter(toptier_agency_id=OuterRef("toptier_agency_id")) .order_by("-toptier_flag", "id") .values("id")[:1] ), "code": F("toptier_code"), "description": F("name"), # Currently, this endpoint can never have children w/o type = `award` & `award_type_codes` "children": Value([], output_field=ArrayField(IntegerField())), "award_count": Value(None, output_field=IntegerField()), "obligation": cte.col.obligation, "outlay": cte.col.outlay, "total_budgetary_resources": Coalesce( Subquery( GTASSF133Balances.objects.filter( disaster_emergency_fund_code__in=self.def_codes, fiscal_period=self.latest_reporting_period["submission_fiscal_month"], fiscal_year=self.latest_reporting_period["submission_fiscal_year"], treasury_account_identifier__funding_toptier_agency_id=OuterRef("toptier_agency_id"), ) .annotate(amount=Func("total_budgetary_resources_cpe", function="Sum")) .values("amount"), output_field=DecimalField(), ), 0, ), "link": Exists(SubmissionAttributes.objects.filter(toptier_code=OuterRef("toptier_code"))), } return ( cte.join(ToptierAgency, toptier_agency_id=cte.col.funding_toptier_agency_id) .with_cte(cte) .annotate(**annotations) .values(*annotations) )
def handle(self, *args, **options): start = time.time() # Mark invalid titles titlestart = time.time() logging.info('Marking blank titles...') count = ContentNode.objects.exclude(complete=False).filter(title='').order_by().update(complete=False) logging.info('Marked {} invalid titles (finished in {})'.format(count, time.time() - titlestart)) # Mark invalid licenses licensestart = time.time() logging.info('Marking blank licenses...') count = ContentNode.objects.exclude(kind_id=content_kinds.TOPIC) \ .exclude(complete=False) \ .filter(license__isnull=True) \ .order_by() \ .update(complete=False) logging.info('Marked {} invalid licenses (finished in {})'.format(count, time.time() - licensestart)) licensestart = time.time() logging.info('Marking blank license descriptions...') custom_licenses = list(License.objects.filter(is_custom=True).values_list("pk", flat=True)) count = ContentNode.objects.exclude(kind_id=content_kinds.TOPIC) \ .exclude(complete=False) \ .filter(license_id__in=custom_licenses) \ .filter(Q(license_description__isnull=True) | Q(license_description='')) \ .order_by() \ .update(complete=False) logging.info('Marked {} invalid license descriptions (finished in {})'.format(count, time.time() - licensestart)) licensestart = time.time() logging.info('Marking blank copyright holders...') copyright_licenses = list(License.objects.filter(copyright_holder_required=True).values_list("pk", flat=True)) count = ContentNode.objects.exclude(kind_id=content_kinds.TOPIC) \ .exclude(complete=False) \ .filter(license_id__in=copyright_licenses) \ .filter(Q(copyright_holder__isnull=True) | Q(copyright_holder=''))\ .order_by() \ .update(complete=False) logging.info('Marked {} invalid copyright holders (finished in {})'.format(count, time.time() - licensestart)) # Mark invalid file resources resourcestart = time.time() logging.info('Marking file resources...') file_check_query = With(File.objects.filter(preset__supplementary=False).values("contentnode_id").order_by(), name="t_file") query = file_check_query.join(ContentNode, id=file_check_query.col.contentnode_id, _join_type=LOUTER)\ .with_cte(file_check_query) \ .annotate(t_contentnode_id=file_check_query.col.contentnode_id) \ .exclude(kind_id=content_kinds.TOPIC) \ .exclude(kind_id=content_kinds.EXERCISE) \ .exclude(complete=False) \ .filter(t_contentnode_id__isnull=True)\ .order_by() count = ContentNode.objects.filter(id__in=query.order_by().values_list('id', flat=True)).update(complete=False) logging.info('Marked {} invalid file resources (finished in {})'.format(count, time.time() - resourcestart)) # Mark invalid exercises exercisestart = time.time() logging.info('Marking exercises...') has_questions_query = With(AssessmentItem.objects.all().values("contentnode_id").order_by(), name="t_assessmentitem") query = has_questions_query.join(ContentNode, id=has_questions_query.col.contentnode_id, _join_type=LOUTER)\ .with_cte(has_questions_query) \ .annotate(t_contentnode_id=has_questions_query.col.contentnode_id) \ .filter(kind_id=content_kinds.EXERCISE) \ .exclude(complete=False) \ .filter(t_contentnode_id__isnull=True)\ .order_by() exercisestart = time.time() count = ContentNode.objects.filter(id__in=query.order_by().values_list('id', flat=True)).update(complete=False) logging.info('Marked {} questionless exercises (finished in {})'.format(count, time.time() - exercisestart)) exercisestart = time.time() exercise_check_query = With(AssessmentItem.objects.exclude(type=exercises.PERSEUS_QUESTION) .filter( Q(question='') | Q(answers='[]') | # hack to check if no correct answers (~Q(type=exercises.INPUT_QUESTION) & ~Q(answers__iregex=r'"correct":\s*true'))).order_by(), name="t_assessmentitem") query = exercise_check_query.join(ContentNode, id=has_questions_query.col.contentnode_id)\ .with_cte(exercise_check_query) \ .annotate(t_contentnode_id=exercise_check_query.col.contentnode_id) \ .filter(kind_id=content_kinds.EXERCISE) \ .exclude(complete=False) \ .order_by() count = ContentNode.objects.filter(id__in=query.order_by().values_list('id', flat=True)).update(complete=False) logging.info('Marked {} invalid exercises (finished in {})'.format(count, time.time() - exercisestart)) exercisestart = time.time() logging.info('Marking mastery_model less exercises...') count = ContentNode.objects.exclude(complete=False).filter(kind_id=content_kinds.EXERCISE).filter(~Q(extra_fields__has_key='mastery_model'))\ .order_by().update(complete=False) logging.info('Marked {} mastery_model less exercises(finished in {})'.format(count, time.time() - exercisestart)) exercisestart = time.time() logging.info('Marking bad mastery model exercises...') count = ContentNode.objects.exclude(complete=False).filter(kind_id=content_kinds.EXERCISE)\ .filter(Q(extra_fields__mastery_model=exercises.M_OF_N) & (~Q(extra_fields__has_key='m') | ~Q(extra_fields__has_key='n')))\ .order_by().update(complete=False) logging.info('Marked {} bad mastery model exercises (finished in {})'.format(count, time.time() - exercisestart)) logging.info('Mark incomplete command completed in {}s'.format(time.time() - start))
def total_queryset(self): cte_filters = [ Q(treasury_account__isnull=False), self.all_closed_defc_submissions, self.is_in_provided_def_codes, self.is_non_zero_total_spending, ] cte_annotations = { "funding_toptier_agency_id": F("treasury_account__funding_toptier_agency_id"), "obligation": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then= F("obligations_incurred_by_program_object_class_cpe" ) + F("deobligations_recoveries_refund_pri_program_object_class_cpe" ), ), default=Value(0), )), 0, ), "outlay": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then= F("gross_outlay_amount_by_program_object_class_cpe" ) + F("ussgl487200_down_adj_pri_ppaid_undel_orders_oblig_refund_cpe" ) + F("ussgl497200_down_adj_pri_paid_deliv_orders_oblig_refund_cpe" ), ), default=Value(0), )), 0, ), } cte = With( FinancialAccountsByProgramActivityObjectClass.objects.filter( *cte_filters).values( "treasury_account__funding_toptier_agency_id").annotate( **cte_annotations).values(*cte_annotations)) annotations = { "id": Subquery( Agency.objects.filter( toptier_agency_id=OuterRef("toptier_agency_id")).order_by( "-toptier_flag", "id").values("id")[:1]), "code": F("toptier_code"), "description": F("name"), # Currently, this endpoint can never have children w/o type = `award` & `award_type_codes` "children": Value([], output_field=ArrayField(IntegerField())), "award_count": Value(None, output_field=IntegerField()), "obligation": cte.col.obligation, "outlay": cte.col.outlay, "total_budgetary_resources": Coalesce( Subquery( latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.def_codes, treasury_account_identifier__funding_toptier_agency_id= OuterRef("toptier_agency_id"), ).annotate( amount=Func("total_budgetary_resources_cpe", function="Sum"), unobligated_balance=Func( "budget_authority_unobligated_balance_brought_forward_cpe", function="Sum"), deobligation=Func( "deobligations_or_recoveries_or_refunds_from_prior_year_cpe", function="Sum"), prior_year=Func( "prior_year_paid_obligation_recoveries", function="Sum"), ).annotate( total_budget_authority=F("amount") - F("unobligated_balance") - F("deobligation") - F("prior_year")).values("total_budget_authority"), output_field=DecimalField(), ), 0, ), "link": Exists( SubmissionAttributes.objects.filter( toptier_code=OuterRef("toptier_code"))), } return (cte.join( ToptierAgency, toptier_agency_id=cte.col.funding_toptier_agency_id).with_cte( cte).annotate(**annotations).values(*annotations))
def test_named_ctes(self): def make_paths_cte(paths): return Region.objects.filter(parent__isnull=True).values( "name", path=F("name"), ).union( paths.join(Region, parent=paths.col.name).values( "name", path=Concat( paths.col.path, Value(" "), F("name"), output_field=text_field, ), ), all=True, ) paths = With.recursive(make_paths_cte, name="region_paths") def make_groups_cte(groups): return paths.join(Region, name=paths.col.name).values( "name", parent_path=paths.col.path, parent_name=F("name"), ).union( groups.join(Region, parent=groups.col.name).values( "name", parent_path=groups.col.parent_path, parent_name=groups.col.parent_name, ), all=True, ) groups = With.recursive(make_groups_cte, name="region_groups") region_totals = With( groups.join(Order, region_id=groups.col.name).values( name=groups.col.parent_name, path=groups.col.parent_path, ).annotate( orders_count=Count("id"), region_total=Sum("amount"), ), name="region_totals", ) regions = ( region_totals.join(Region, name=region_totals.col.name).with_cte( paths).with_cte(groups).with_cte(region_totals).annotate( path=region_totals.col.path, # count of orders in this region and all subregions orders_count=region_totals.col.orders_count, # sum of order amounts in this region and all subregions region_total=region_totals.col.region_total, ).order_by("path")) data = [(r.name, r.orders_count, r.region_total) for r in regions] self.assertEqual(data, [ ('proxima centauri', 4, 2033), ('proxima centauri b', 3, 33), ('sun', 18, 1374), ('earth', 7, 132), ('moon', 3, 6), ('mars', 3, 123), ('mercury', 3, 33), ('venus', 4, 86), ])
def construct_loan_queryset(self, faba_grouping_column, base_model, base_model_column): grouping_key = F(faba_grouping_column) if isinstance( faba_grouping_column, str) else faba_grouping_column base_values = With( FinancialAccountsByAwards.objects.filter( Q(award__type__in=loan_type_mapping), self.all_closed_defc_submissions, self.is_in_provided_def_codes, ).annotate( grouping_key=grouping_key, total_loan_value=F("award__total_loan_value"), reporting_fiscal_year=F("submission__reporting_fiscal_year"), reporting_fiscal_period=F( "submission__reporting_fiscal_period"), quarter_format_flag=F("submission__quarter_format_flag"), ).filter(grouping_key__isnull=False).values( "grouping_key", "financial_accounts_by_awards_id", "award_id", "transaction_obligated_amount", "gross_outlay_amount_by_award_cpe", "reporting_fiscal_year", "reporting_fiscal_period", "quarter_format_flag", "total_loan_value", ), "base_values", ) q = Q() for sub in final_submissions_for_all_fy(): q |= (Q(reporting_fiscal_year=sub.fiscal_year) & Q(quarter_format_flag=sub.is_quarter) & Q(reporting_fiscal_period=sub.fiscal_period)) aggregate_faba = With( base_values.queryset().values("grouping_key").annotate( obligation=Coalesce(Sum("transaction_obligated_amount"), 0), outlay=Coalesce( Sum( Case( When(q, then=F("gross_outlay_amount_by_award_cpe")), default=Value(0), )), 0, ), ).values("grouping_key", "obligation", "outlay"), "aggregate_faba", ) distinct_awards = With( base_values.queryset().values("grouping_key", "award_id", "total_loan_value").distinct(), "distinct_awards", ) aggregate_awards = With( distinct_awards.queryset().values("grouping_key").annotate( award_count=Count("award_id"), face_value_of_loan=Coalesce(Sum("total_loan_value"), 0)).values("grouping_key", "award_count", "face_value_of_loan"), "aggregate_awards", ) return Bunch( award_count_column=aggregate_awards.col.award_count, obligation_column=aggregate_faba.col.obligation, outlay_column=aggregate_faba.col.outlay, face_value_of_loan_column=aggregate_awards.col.face_value_of_loan, queryset=aggregate_awards.join( aggregate_faba.join( base_model, **{base_model_column: aggregate_faba.col.grouping_key}), **{ base_model_column: aggregate_awards.col.grouping_key }, ).with_cte(base_values).with_cte(aggregate_faba).with_cte( distinct_awards).with_cte(aggregate_awards), )