def _filter_elections(self, query, cycle): election_duration = utils.get_election_duration(sa.func.left(models.CandidateHistory.candidate_id, 1)) return query.join( models.CandidateElection, sa.and_( models.CandidateHistory.candidate_id == models.CandidateElection.candidate_id, models.CandidateHistory.two_year_period > models.CandidateElection.cand_election_year - election_duration, models.CandidateHistory.two_year_period <= models.CandidateElection.cand_election_year, ), ).filter( models.CandidateElection.cand_election_year >= cycle, models.CandidateElection.cand_election_year < cycle + election_duration, ).order_by( models.CandidateHistory.candidate_id, sa.desc(models.CandidateHistory.two_year_period), ).distinct( models.CandidateHistory.candidate_id, )
def aggregate_cycles(self, query, cycle_column): election_duration = utils.get_election_duration(sa.func.left(self.model.candidate_id, 1)) query = query.join( models.CandidateElection, sa.and_( self.model.candidate_id == models.CandidateElection.candidate_id, self.model.cycle <= models.CandidateElection.cand_election_year, self.model.cycle > (models.CandidateElection.cand_election_year - election_duration), ), ) return query.with_entities( self.model.candidate_id, self.model.committee_id, cycle_column.label("cycle"), sa.func.sum(self.model.total).label("total"), sa.func.sum(self.model.count).label("count"), *self.label_columns ).group_by(self.model.candidate_id, self.model.committee_id, cycle_column, *self.group_columns)
def _filter_elections(self, query, candidate_id, cycle): election_duration = utils.get_election_duration(models.CandidateCommitteeLink.committee_type) return query.join( models.CandidateElection, sa.and_( models.CandidateCommitteeLink.candidate_id == models.CandidateElection.candidate_id, models.CandidateCommitteeLink.fec_election_year > models.CandidateElection.cand_election_year - election_duration, models.CandidateCommitteeLink.fec_election_year <= models.CandidateElection.cand_election_year, ), ).filter( models.CandidateElection.candidate_id == candidate_id, models.CandidateElection.cand_election_year >= cycle, models.CandidateElection.cand_election_year < cycle + election_duration, ).order_by( models.CommitteeHistory.committee_id, sa.desc(models.CommitteeHistory.cycle), ).distinct( models.CommitteeHistory.committee_id, )
def _filter_elections(self, query, cycle): election_duration = utils.get_election_duration( sa.func.left(models.CandidateHistory.candidate_id, 1)) return query.join( models.CandidateElection, sa.and_( models.CandidateHistory.candidate_id == models.CandidateElection.candidate_id, models.CandidateHistory.two_year_period > models.CandidateElection.cand_election_year - election_duration, models.CandidateHistory.two_year_period <= models.CandidateElection.cand_election_year, ), ).filter( models.CandidateElection.cand_election_year >= cycle, models.CandidateElection.cand_election_year < cycle + election_duration, ).order_by( models.CandidateHistory.candidate_id, sa.desc(models.CandidateHistory.two_year_period), ).distinct(models.CandidateHistory.candidate_id, )
def aggregate_cycles(self, query, cycle_column): election_duration = utils.get_election_duration( sa.func.left(self.model.candidate_id, 1)) query = query.join( models.CandidateElection, sa.and_( self.model.candidate_id == models.CandidateElection.candidate_id, self.model.cycle <= models.CandidateElection.cand_election_year, self.model.cycle > (models.CandidateElection.cand_election_year - election_duration), ), ) return query.with_entities( self.model.candidate_id, self.model.committee_id, cycle_column.label('cycle'), sa.func.sum(self.model.total).label('total'), sa.func.sum(self.model.count).label('count'), *self.label_columns).group_by(self.model.candidate_id, self.model.committee_id, cycle_column, *self.group_columns)
from webservices import args from webservices import utils from webservices import filters from webservices import schemas from webservices.utils import use_kwargs from webservices.common.views import ApiResource from webservices.common import models from webservices.common.models import ( CandidateElection, CandidateCommitteeLink, ScheduleABySize, ScheduleAByState, db ) election_duration = utils.get_election_duration(CandidateCommitteeLink.committee_type) def candidate_aggregate(aggregate_model, label_columns, group_columns, kwargs): """Aggregate committee totals by candidate. :param aggregate_model: SQLAlchemy aggregate model :param list label_columns: List of label columns; must include group-by columns :param list group_columns: List of group-by columns :param dict kwargs: Parsed arguments from request """ cycle_column = ( CandidateElection.cand_election_year if kwargs.get('election_full') else CandidateCommitteeLink.fec_election_year ).label('cycle')
from webservices import args from webservices import utils from webservices import filters from webservices import schemas from webservices.utils import use_kwargs from webservices.common.views import ApiResource from webservices.common import models from webservices.common.models import ( CandidateElection, CandidateCommitteeLink, ScheduleABySize, ScheduleAByState, db ) election_duration = utils.get_election_duration(CandidateCommitteeLink.committee_type) def candidate_aggregate(aggregate_model, label_columns, group_columns, kwargs): """Aggregate committee totals by candidate. :param aggregate_model: SQLAlchemy aggregate model :param list label_columns: List of label columns; must include group-by columns :param list group_columns: List of group-by columns :param dict kwargs: Parsed arguments from request """ cycle_column = ( CandidateElection.cand_election_year if kwargs.get('election_full') else CandidateCommitteeLink.fec_election_year ).label('cycle')