def get_spi_report_queryset():
"""Get SPI Report queryset."""
return InvestmentProject.objects.select_related(
'investmentprojectcode',
'project_manager__dit_team',
).annotate(
spi_propositions=get_array_agg_subquery(
Proposition,
'investment_project',
JSONBBuildObject(
deadline='deadline',
status='status',
adviser_id='adviser_id',
adviser_name=get_full_name_expression('adviser'),
modified_on='modified_on',
),
ordering=('created_on', ),
),
spi_interactions=get_array_agg_subquery(
Interaction,
'investment_project',
JSONBBuildObject(
service_id='service_id',
service_name=get_service_name_subquery('service'),
created_by_id='created_by_id',
created_by_name=get_full_name_expression('created_by'),
created_on='created_on',
),
filter=Q(service_id__in=ALL_SPI_SERVICE_IDS),
ordering=('created_on', ),
),
).order_by('created_on')
def get_dataset(self):
"""Returns a list of all interaction records"""
return get_base_interaction_queryset().annotate(
adviser_ids=get_aggregate_subquery(
Interaction,
ArrayAgg('dit_participants__adviser_id',
ordering=('dit_participants__id', )),
),
contact_ids=get_aggregate_subquery(
Interaction,
ArrayAgg('contacts__id', ordering=('contacts__id', )),
),
interaction_link=get_front_end_url_expression('interaction', 'pk'),
policy_area_names=get_array_agg_subquery(
Interaction.policy_areas.through,
'interaction',
'policyarea__name',
ordering=('policyarea__order', ),
),
policy_issue_type_names=get_array_agg_subquery(
Interaction.policy_issue_types.through,
'interaction',
'policyissuetype__name',
ordering=('policyissuetype__order', ),
),
sector=get_sector_name_subquery('company__sector'),
service_delivery=get_service_name_subquery('service'),
).values(
'adviser_ids',
'communication_channel__name',
'company_id',
'contact_ids',
'created_by_id',
'created_on',
'date',
'event_id',
'grant_amount_offered',
'id',
'interaction_link',
'investment_project_id',
'kind',
'modified_on',
'net_company_receipt',
'notes',
'policy_area_names',
'policy_feedback_notes',
'policy_issue_type_names',
'sector',
'service_delivery_status__name',
'service_delivery',
'subject',
'theme',
'were_countries_discussed',
)
def test_aggregates_as_array(self, names, distinct):
"""
Test that the first names of all authors for each book can be aggregated into an array
for various cases, and with distinct on and off.
"""
authors = PersonFactory.create_batch(
len(names),
first_name=factory.Iterator(
sample(names, len(names)),
),
)
BookFactory(authors=authors)
queryset = Book.objects.annotate(
author_names=get_array_agg_subquery(
Book.authors.through,
'book',
'person__first_name',
distinct=distinct,
),
)
actual_author_names = queryset.first().author_names
if distinct:
assert Counter(actual_author_names) == Counter(set(names))
else:
assert Counter(actual_author_names) == Counter(names)
def get_dataset(self):
"""Returns list of PipelineItem records"""
return PipelineItem.objects.annotate(
sector_name=get_sector_name_subquery('sector'),
contact_ids=get_array_agg_subquery(
PipelineItem.contacts.through,
'pipelineitem',
'contact__id',
ordering=('contact__created_on', ),
),
).values(
'adviser_id',
'archived',
'company_id',
'contact_ids',
'created_on',
'expected_win_date',
'id',
'likelihood_to_win',
'modified_on',
'name',
'potential_value',
'sector_name',
'status',
)
def get_company_list_item_queryset():
"""
Returns an annotated query set used by CompanyListItemViewSet and CompanyListViewSet.
The annotations are supported by an index on the Interaction model.
(Note that getting all three interaction fields in one expression currently is not easily
done with the Django ORM, hence three annotations are used.)
"""
return CompanyListItem.objects.annotate(
latest_interaction_id=_get_field_of_latest_interaction('pk'),
latest_interaction_created_on=_get_field_of_latest_interaction(
'created_on'),
latest_interaction_date=_get_field_of_latest_interaction('date'),
latest_interaction_subject=_get_field_of_latest_interaction('subject'),
latest_interaction_dit_participants=_get_field_of_latest_interaction(
get_array_agg_subquery(
InteractionDITParticipant,
'interaction',
JSONBBuildObject(
adviser=_get_null_when_expression(
JSONBBuildObject(
id='adviser__id',
name=get_full_name_expression('adviser'),
),
Q(adviser__isnull=True),
),
team=_get_null_when_expression(
JSONBBuildObject(
id='team__id',
name='team__name',
),
Q(team__isnull=True),
),
),
ordering=('pk', ),
), ),
latest_interaction_time_ago=ExpressionWrapper(
Now() - F('latest_interaction_date'),
output_field=DurationField(),
),
).select_related('company', ).only(
# Only select the fields we need to reduce data transfer time for large lists
# (in particular, companies have a lot of fields which are not needed here)
'id',
'created_on',
'company__id',
'company__archived',
'company__name',
'company__trading_names',
)
def test_orders_results_when_ordering_specified(self, ordering,
expected_names):
"""Test that the values are ordered corrected when an ordering is specified."""
names = ['Barbara', 'Claire', 'Samantha']
authors = PersonFactory.create_batch(
len(names),
first_name=factory.Iterator(sample(names, len(names)), ),
)
BookFactory(authors=authors)
queryset = Book.objects.annotate(author_names=get_array_agg_subquery(
Book.authors.through,
'book',
'person__first_name',
ordering=ordering,
), )
actual_author_names = queryset.first().author_names
assert actual_author_names == expected_names
def test_aggregates_as_filtered_array(self, names, desired_names):
"""
Test that the desired first names of authors for each book can be aggregated into an array
for various cases.
"""
authors = PersonFactory.create_batch(
len(names),
first_name=factory.Iterator(sample(names, len(names)), ),
)
BookFactory(authors=authors)
queryset = Book.objects.annotate(author_names=get_array_agg_subquery(
Book.authors.through,
'book',
'person__first_name',
filter=Q(person__first_name__in=desired_names),
), )
actual_author_names = queryset.first()
assert set(actual_author_names.author_names) == set(desired_names)
def get_dataset(self):
"""Returns list of Investment Projects Dataset records"""
return InvestmentProject.objects.annotate(
actual_uk_region_names=get_array_agg_subquery(
InvestmentProject.actual_uk_regions.through,
'investmentproject',
'ukregion__name',
ordering=('ukregion__name', ),
),
business_activity_names=get_array_agg_subquery(
InvestmentProject.business_activities.through,
'investmentproject',
'investmentbusinessactivity__name',
ordering=('investmentbusinessactivity__name', ),
),
competing_countries=get_aggregate_subquery(
InvestmentProject,
ArrayAgg('competitor_countries__name',
ordering=('competitor_countries__name', )),
),
delivery_partner_names=get_array_agg_subquery(
InvestmentProject.delivery_partners.through,
'investmentproject',
'investmentdeliverypartner__name',
ordering=('investmentdeliverypartner__name', ),
),
investor_company_sector=get_sector_name_subquery(
'investor_company__sector'),
level_of_involvement_name=get_empty_string_if_null_expression(
'level_of_involvement__name', ),
project_reference=get_project_code_expression(),
strategic_driver_names=get_array_agg_subquery(
InvestmentProject.strategic_drivers.through,
'investmentproject',
'investmentstrategicdriver__name',
ordering=('investmentstrategicdriver__name', ),
),
sector_name=get_sector_name_subquery('sector'),
team_member_ids=get_aggregate_subquery(
InvestmentProject,
ArrayAgg('team_members__adviser_id',
ordering=('team_members__id', )),
),
uk_company_sector=get_sector_name_subquery('uk_company__sector'),
uk_region_location_names=get_array_agg_subquery(
InvestmentProject.uk_region_locations.through,
'investmentproject',
'ukregion__name',
ordering=('ukregion__name', ),
),
).values(
'actual_land_date',
'actual_uk_region_names',
'address_1',
'address_2',
'address_town',
'address_postcode',
'anonymous_description',
'associated_non_fdi_r_and_d_project_id',
'average_salary__name',
'business_activity_names',
'client_relationship_manager_id',
'client_requirements',
'competing_countries',
'created_by_id',
'created_on',
'delivery_partner_names',
'description',
'estimated_land_date',
'export_revenue',
'fdi_type__name',
'fdi_value__name',
'foreign_equity_investment',
'government_assistance',
'gross_value_added',
'gva_multiplier__multiplier',
'id',
'investment_type__name',
'investor_company_id',
'investor_company_sector',
'investor_type__name',
'level_of_involvement_name',
'likelihood_to_land__name',
'modified_by_id',
'modified_on',
'name',
'new_tech_to_uk',
'non_fdi_r_and_d_budget',
'number_new_jobs',
'number_safeguarded_jobs',
'other_business_activity',
'project_arrived_in_triage_on',
'project_assurance_adviser_id',
'project_manager_id',
'project_reference',
'proposal_deadline',
'r_and_d_budget',
'referral_source_activity__name',
'referral_source_activity_marketing__name',
'referral_source_activity_website__name',
'sector_name',
'specific_programme__name',
'stage__name',
'status',
'strategic_driver_names',
'team_member_ids',
'total_investment',
'uk_company_id',
'uk_company_sector',
'uk_region_location_names',
)
