Add filters for candidate state, district, and party affiliation to /schedules/schedule_e/

[lbeaufort]

Add
candidate_office
candidate_state
candidate_district
candidate_party to /schedules/schedule_e/

• Add the filter to args.py
• Modify the query/MV as needed
• Add indexes as needed

[pkfec]

1. Adding api filters for state and district seems straight forward. These two new api filters can be mapped to below columns on ScheduleE model( ofec_schedule_e table)

   • s_o_cand_office_st
   • s_o_cand_office_district

2. The data on s_o_cand_office_st and s_o_cand_office_district columns seems not correct. In some rows, state column has numbers and district has state abbreviations. Searching data based off these two new filters may results in inconsistent data.

3. To create Candidate Party filter, there is no one-to-one mapping column on the ScheduleE model.
   Have to figure out a way on how to get the candidate party from Candidate History model or some other MV or view and add this field as an filter on the schedule_e api.

[pkfec]

Added Party filter on schedule_e endpoint. Tried to filter data for a committee_id= 'C00331454'
and party= 'DEM'. Most of the candidate fields are coming as NULL and also, the data is not right.

I will have @ChingKuan or @fecjjeng look into the SQL.

Here is the SQL:
SELECT ofec_sched_e.cmte_id, ofec_sched_e.rpt_yr, ofec_sched_e.rpt_tp, ofec_sched_e.image_num, ofec_sched_e.line_num, ofec_sched_e.file_num, ofec_sched_e.pye_nm, ofec_sched_e.payee_f_nm, ofec_sched_e.payee_m_nm, ofec_sched_e.payee_l_nm, ofec_sched_e.pye_st1, ofec_sched_e.pye_st2, ofec_sched_e.pye_city, ofec_sched_e.pye_st, ofec_sched_e.pye_zip, ofec_sched_e.exp_desc, ofec_sched_e.exp_dt, ofec_sched_e.dissem_dt, ofec_sched_e.exp_amt, ofec_sched_e.cal_ytd_ofc_sought, ofec_sched_e.catg_cd, ofec_sched_e.catg_cd_desc, ofec_sched_e.s_o_ind, ofec_sched_e.memo_cd, ofec_sched_e.memo_cd_desc, ofec_sched_e.s_o_cand_id, ofec_sched_e.s_o_cand_nm, ofec_sched_e.s_o_cand_prefix, ofec_sched_e.s_o_cand_nm_first, ofec_sched_e.s_o_cand_m_nm, ofec_sched_e.s_o_cand_nm_last, ofec_sched_e.s_o_cand_suffix, ofec_sched_e.s_o_cand_office, ofec_sched_e.s_o_cand_office_st, ofec_sched_e.s_o_cand_office_district, ofec_sched_e.conduit_cmte_id, ofec_sched_e.conduit_cmte_nm, ofec_sched_e.conduit_cmte_st1, ofec_sched_e.conduit_cmte_st2, ofec_sched_e.conduit_cmte_city, ofec_sched_e.conduit_cmte_st, ofec_sched_e.conduit_cmte_zip, ofec_sched_e.election_tp, ofec_sched_e.fec_election_tp_desc, ofec_sched_e.indt_sign_nm, ofec_sched_e.indt_sign_dt, ofec_sched_e.notary_sign_nm, ofec_sched_e.notary_sign_dt, ofec_sched_e.notary_commission_exprtn_dt, ofec_sched_e.back_ref_tran_id, ofec_sched_e.back_ref_sched_nm, ofec_sched_e.filer_f_nm, ofec_sched_e.filer_m_nm, ofec_sched_e.filer_l_nm, ofec_sched_e.tran_id, ofec_sched_e.orig_sub_id, ofec_sched_e.action_cd, ofec_sched_e.action_cd_desc, ofec_sched_e.schedule_type_desc, ofec_sched_e.filing_form, ofec_sched_e.link_id, ofec_sched_e.sub_id, ofec_sched_e.payee_prefix, ofec_sched_e.payee_name_text, ofec_sched_e.payee_suffix, ofec_sched_e.is_notice, ofec_sched_e.memo_text, ofec_sched_e.filer_prefix, ofec_sched_e.filer_suffix, ofec_sched_e.schedule_type, ofec_sched_e.pdf_url, ofec_committee_history_mv_1.idx, ofec_committee_history_mv_1.name, ofec_committee_history_mv_1.committee_id, ofec_committee_history_mv_1.cycles, ofec_committee_history_mv_1.treasurer_name, ofec_committee_history_mv_1.treasurer_text, ofec_committee_history_mv_1.committee_type, ofec_committee_history_mv_1.committee_type_full, ofec_committee_history_mv_1.filing_frequency, ofec_committee_history_mv_1.designation, ofec_committee_history_mv_1.designation_full, ofec_committee_history_mv_1.organization_type, ofec_committee_history_mv_1.organization_type_full, ofec_committee_history_mv_1.party, ofec_committee_history_mv_1.party_full, ofec_committee_history_mv_1.state, ofec_committee_history_mv_1.street_1, ofec_committee_history_mv_1.street_2, ofec_committee_history_mv_1.city, ofec_committee_history_mv_1.state_full, ofec_committee_history_mv_1.zip, ofec_committee_history_mv_1.candidate_ids, ofec_committee_history_mv_1.cycle, ofec_candidate_history_mv_1.idx, ofec_candidate_history_mv_1.name, ofec_candidate_history_mv_1.office, ofec_candidate_history_mv_1.office_full, ofec_candidate_history_mv_1.party, ofec_candidate_history_mv_1.party_full, ofec_candidate_history_mv_1.state, ofec_candidate_history_mv_1.district, ofec_candidate_history_mv_1.district_number, ofec_candidate_history_mv_1.election_districts, ofec_candidate_history_mv_1.election_years, ofec_candidate_history_mv_1.cycles, ofec_candidate_history_mv_1.candidate_status, ofec_candidate_history_mv_1.incumbent_challenge, ofec_candidate_history_mv_1.incumbent_challenge_full, ofec_candidate_history_mv_1.load_date, ofec_candidate_history_mv_1.first_file_date, ofec_candidate_history_mv_1.last_file_date, ofec_candidate_history_mv_1.last_f2_date, ofec_candidate_history_mv_1.candidate_id, ofec_candidate_history_mv_1.two_year_period, ofec_candidate_history_mv_1.candidate_election_year, ofec_candidate_history_mv_1.address_city, ofec_candidate_history_mv_1.address_state, ofec_candidate_history_mv_1.address_street_1, ofec_candidate_history_mv_1.address_street_2, ofec_candidate_history_mv_1.address_zip, ofec_candidate_history_mv_1.candidate_inactive, ofec_candidate_history_mv_1.active_through
FROM ofec_candidate_history_mv, ofec_sched_e LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON ofec_sched_e.cmte_id = ofec_committee_history_mv_1.committee_id AND ofec_sched_e.rpt_yr + ofec_sched_e.rpt_yr %% 2 = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN ofec_candidate_history_mv AS ofec_candidate_history_mv_1 ON ofec_sched_e.s_o_cand_id = ofec_candidate_history_mv_1.candidate_id AND ofec_sched_e.rpt_yr + ofec_sched_e.rpt_yr %% 2 = ofec_candidate_history_mv_1.two_year_period
WHERE get_cycle(ofec_sched_e.rpt_yr) IN (2006) AND ofec_sched_e.cmte_id IN ('C00331454') AND ofec_candidate_history_mv.party = 'DEM'

cc @jwchumley

[pkfec]

The fix for this issue is similar to scheudle_a #3274.
I would wait until schedule_a issue is resolved. That way i can apply the same logic to schedule_e.

Given the time, its best if we move this issue to next sprint.

[PaulClark2]

@pkfec how is this issue similar to the SA issue? Has there been significant progress on the SA ticket? Is there a solution outlined here? #3248

[pkfec]

@PaulClark2
party column is not there in SE table. There is no easy way to get this column from cand_history table to SE table. Although there is a join happening on these two table via SQLalchemy that is really not getting the values that we are looking for. Hope this helps!

SA has similar join on committe_type filter, and the solution is being worked out.
cc @rjayasekera