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 _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_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),
)