class ElectionView(utils.Resource):
@use_kwargs(args.paging)
@use_kwargs(args.elections)
@use_kwargs(args.make_sort_args(default='-total_receipts'))
@marshal_with(schemas.ElectionPageSchema())
def get(self, **kwargs):
query = self._get_records(kwargs)
return utils.fetch_page(query, kwargs, cap=0)
def _get_records(self, kwargs):
utils.check_election_arguments(kwargs)
totals_model = office_totals_map[kwargs['office']]
pairs = self._get_pairs(totals_model, kwargs).subquery()
aggregates = self._get_aggregates(pairs).subquery()
outcomes = self._get_outcomes(kwargs).subquery()
latest = self._get_latest(pairs).subquery()
return db.session.query(
aggregates,
latest,
sa.case(
[(outcomes.c.cand_id != None, True)], # noqa
else_=False,
).label('won'),
).outerjoin(
latest,
aggregates.c.candidate_id == latest.c.candidate_id,
).outerjoin(
outcomes,
aggregates.c.candidate_id == outcomes.c.cand_id,
).distinct()
def _get_pairs(self, totals_model, kwargs):
pairs = CandidateHistory.query.with_entities(
CandidateHistory.candidate_id,
CandidateHistory.name,
CandidateHistory.party_full,
CandidateHistory.incumbent_challenge_full,
CandidateHistory.office,
CandidateHistory.two_year_period,
CandidateHistory.candidate_election_year,
CandidateCommitteeLink.committee_id,
totals_model.receipts,
totals_model.disbursements,
totals_model.last_cash_on_hand_end_period.label('cash_on_hand_end_period'),
totals_model.coverage_end_date,
)
pairs = join_candidate_totals(pairs, kwargs, totals_model)
pairs = filter_candidate_totals(pairs, kwargs, totals_model)
return pairs
def _get_latest(self, pairs):
latest = db.session.query(
pairs.c.cash_on_hand_end_period,
).distinct(
pairs.c.candidate_id,
pairs.c.cmte_id,
).order_by(
pairs.c.candidate_id,
pairs.c.cmte_id,
sa.desc(pairs.c.two_year_period),
).subquery()
return db.session.query(
latest.c.candidate_id,
sa.func.sum(sa.func.coalesce(latest.c.cash_on_hand_end_period,0.0)).label('cash_on_hand_end_period'),
).group_by(
latest.c.candidate_id,
)
def _get_aggregates(self, pairs):
return db.session.query(
pairs.c.candidate_id,
pairs.c.candidate_election_year,
sa.func.max(pairs.c.name).label('candidate_name'),
sa.func.max(pairs.c.party_full).label('party_full'),
sa.func.max(pairs.c.incumbent_challenge_full).label('incumbent_challenge_full'),
sa.func.max(pairs.c.office).label('office'),
sa.func.sum(sa.func.coalesce(pairs.c.receipts, 0.0)).label('total_receipts'),
sa.func.sum(sa.func.coalesce(pairs.c.disbursements, 0.0)).label('total_disbursements'),
sa.func.sum(sa.func.coalesce(pairs.c.cash_on_hand_end_period, 0.0)).label('cash_on_hand_end_period'),
sa.func.array_agg(sa.distinct(pairs.c.cmte_id)).label('committee_ids'),
sa.func.max(pairs.c.coverage_end_date).label('coverage_end_date'),
).group_by(
pairs.c.candidate_id,
pairs.c.candidate_election_year
)
def _get_outcomes(self, kwargs):
return db.session.query(
ElectionResult.cand_id
).filter(
ElectionResult.election_yr == kwargs['cycle'],
ElectionResult.cand_office == kwargs['office'][0].upper(),
ElectionResult.cand_office_st == (kwargs.get('state', 'US')),
ElectionResult.cand_office_district == (kwargs.get('district', '00')),
)
class ElectionView(Resource):
@args.register_kwargs(args.paging)
@args.register_kwargs(args.elections)
@args.register_kwargs(
args.make_sort_args(default=['-total_receipts'],
default_nulls_large=False))
@schemas.marshal_with(schemas.ElectionPageSchema())
def get(self, **kwargs):
query = self._get_records(kwargs)
return utils.fetch_page(query, kwargs, cap=0)
def _get_records(self, kwargs):
utils.check_election_arguments(kwargs)
totals_model = office_totals_map[kwargs['office']]
pairs = self._get_pairs(totals_model, kwargs).subquery()
aggregates = self._get_aggregates(pairs).subquery()
filings = self._get_filings(pairs).subquery()
return db.session.query(
aggregates,
filings,
).join(
filings,
aggregates.c.candidate_id == filings.c.candidate_id,
)
def _get_pairs(self, totals_model, kwargs):
pairs = CandidateHistory.query.with_entities(
CandidateHistory.candidate_id,
CandidateHistory.name,
CandidateHistory.party_full,
CandidateHistory.incumbent_challenge_full,
CandidateHistory.office,
totals_model.committee_id,
totals_model.receipts,
totals_model.disbursements,
totals_model.last_report_year.label('report_year'),
totals_model.last_report_type_full.label('report_type_full'),
totals_model.last_beginning_image_number.label(
'beginning_image_number'),
totals_model.last_cash_on_hand_end_period.label(
'cash_on_hand_end_period'),
).distinct(
CandidateHistory.candidate_key,
CommitteeHistory.committee_key,
).join(
CandidateCommitteeLink,
CandidateHistory.candidate_key ==
CandidateCommitteeLink.candidate_key,
).join(
CommitteeHistory,
CandidateCommitteeLink.committee_key ==
CommitteeHistory.committee_key,
).join(
totals_model,
CommitteeHistory.committee_id == totals_model.committee_id,
).filter(
CandidateHistory.two_year_period == kwargs['cycle'],
CandidateHistory.election_years.any(kwargs['cycle']),
CandidateHistory.office == kwargs['office'][0].upper(),
CandidateCommitteeLink.election_year.in_(
[kwargs['cycle'], kwargs['cycle'] - 1]),
CommitteeHistory.cycle == kwargs['cycle'],
CommitteeHistory.designation.in_(['P', 'A']),
totals_model.cycle == kwargs['cycle'],
)
if kwargs['state']:
pairs = pairs.filter(CandidateHistory.state == kwargs['state'])
if kwargs['district']:
pairs = pairs.filter(
CandidateHistory.district == kwargs['district'])
return pairs.order_by(
CandidateHistory.candidate_key,
CommitteeHistory.committee_key,
sa.desc(totals_model.coverage_end_date),
)
def _get_aggregates(self, pairs):
return db.session.query(
pairs.c.candidate_id,
sa.func.max(pairs.c.name).label('candidate_name'),
sa.func.max(pairs.c.party_full).label('party_full'),
sa.func.max(pairs.c.incumbent_challenge_full).label(
'incumbent_challenge_full'),
sa.func.max(pairs.c.office).label('office'),
sa.func.sum(pairs.c.receipts).label('total_receipts'),
sa.func.sum(pairs.c.disbursements).label('total_disbursements'),
sa.func.sum(pairs.c.cash_on_hand_end_period).label(
'cash_on_hand_end_period'),
sa.func.array_agg(pairs.c.committee_id).label('committee_ids'),
).group_by(pairs.c.candidate_id, )
def _get_filings(self, pairs):
return db.session.query(
pairs.c.candidate_id,
pairs.c.report_year,
pairs.c.report_type_full,
pairs.c.beginning_image_number,
).distinct(pairs.c.candidate_id, ).order_by(
pairs.c.candidate_id,
sa.desc(pairs.c.coverage_end_date),
)