def read(self, request):
count = request.GET.get('count', None)
start = request.GET.get('start', None)
end = request.GET.get('end', None)
entity_type = request.GET.get('type', None)
if entity_type:
where_clause = "where type = %s"
else:
where_clause = ''
if count:
return dict(count=execute_top(self.count_stmt % where_clause, *([entity_type] if entity_type else []))[0][0])
if start is not None and end is not None:
try:
start = int(start)
end = int(end)
except:
error_response = rc.BAD_REQUEST
error_response.write("Must provide integers for start and end.")
return error_response
else:
error_response = rc.BAD_REQUEST
error_response.write("Must specify valid start and end parameters.")
return error_response
if (end < start or end - start > 10000):
error_response = rc.BAD_REQUEST
error_response.write("Only 10,000 entities can be retrieved at a time.")
return error_response
raw_result = execute_top(self.stmt % where_clause, *([entity_type] if entity_type else []) + [start, end - start + 1])
return [dict(zip(self.fields, row)) for row in raw_result]
def read(self, request, **kwargs):
cycle = request.GET.get('cycle')
raw_candidates_result = execute_top(self.candidates_stmt, cycle, kwargs['entity_id'], cycle)
candidates = [dict(zip(self.candidates_fields, row)) for row in raw_candidates_result]
# this makes a separate query for each candidate
# if this proves to be slow it could be done in one query with slightly more complex code
for candidate in candidates:
raw_timeline = execute_top(self.timeline_stmt, candidate['candidate_id'], cycle)
candidate['timeline'] = [amount for (week, amount) in raw_timeline]
return candidates
def read(self, request, **kwargs):
cycle = request.GET.get('cycle')
raw_candidates_result = execute_top(self.candidates_stmt, cycle,
kwargs['entity_id'], cycle)
candidates = [
dict(zip(self.candidates_fields, row))
for row in raw_candidates_result
]
# this makes a separate query for each candidate
# if this proves to be slow it could be done in one query with slightly more complex code
for candidate in candidates:
raw_timeline = execute_top(self.timeline_stmt,
candidate['candidate_id'], cycle)
candidate['timeline'] = [amount for (week, amount) in raw_timeline]
return candidates
def read(self, request):
query = request.GET['query']
(lat, lon) = (float(request.GET.get('lat')), float(request.GET.get('lon')))
limit = int(request.GET.get('limit', '10'))
raw_result = execute_top(self.stmt, query_to_ft_sql(query), "POINT(%s %s)" % (lon, lat), limit)
labeled_result = [dict(zip(self.fields, row)) for row in raw_result]
return labeled_result
def read(self, request):
query = request.GET.get('search', None)
if not query:
error_response = rc.BAD_REQUEST
error_response.write("Must include a query in the 'search' parameter.")
return error_response
parsed_query = ' & '.join(re.split(r'[ &|!():*]+', unquote_plus(query)))
raw_result = execute_top(self.stmt, parsed_query)
return [dict(zip(self.fields, row)) for row in raw_result]
def read(self, request):
query = request.GET.get('search')
entity_type = request.GET.get('type')
if not query:
error_response = rc.BAD_REQUEST
error_response.write("Must include a query in the 'search' parameter.")
return error_response
stmt = self.stmt
if entity_type:
stmt += '\n and e.type = %s'
parsed_query = ' & '.join(re.split(r'[ &|!():*]+', unquote_plus(query)))
query_params = (parsed_query)
query_params = [x for x in (parsed_query, entity_type) if x]
raw_result = execute_top(stmt, *query_params)
return [dict(zip(self.fields, row)) for row in raw_result]
def get_totals(entity_id):
totals = dict()
for row in execute_top(get_totals_stmt, *[entity_id] * 11):
totals[row[0]] = dict(zip(EntityHandler.totals_fields, row[1:]))
return totals
def read(self, request):
query = request.GET.get('search')
if not query:
error_response = rc.BAD_REQUEST
error_response.write("Must include a query in the 'search' parameter.")
return error_response
stmt = self.stmt
per_page = min(int(request.GET.get('per_page', 10)), 25)
page = max(int(request.GET.get('page', 1)), 1)
start = (page - 1) * per_page
end = start + per_page
parsed_query = ' & '.join(re.split(r'[ &|!():*]+', unquote_plus(query)))
where_filters = []
extra_joins = []
filters = {}
subtype_raw = request.GET.get('subtype', None)
subtype = subtype_raw if subtype_raw in set(('contributors', 'lobbyists', 'politicians', 'industries', 'lobbying_firms', 'political_groups', 'other_orgs')) else None
if subtype:
if subtype == 'contributors':
where_filters.append("e.type = 'individual' and mbim.is_contributor = 't'")
extra_joins.append("left join matchbox_individualmetadata mbim on e.id = mbim.entity_id")
elif subtype == 'lobbyists':
where_filters.append("e.type = 'individual' and mbim.is_lobbyist = 't'")
extra_joins.append("left join matchbox_individualmetadata mbim on e.id = mbim.entity_id")
elif subtype == 'politicians':
where_filters.append("e.type = 'politician'")
# check for seat and state
seat = request.GET.get('seat', None)
seat = seat if seat in valid_seats else None
state = request.GET.get('state', None)
state = state if state in valid_states else None
party = request.GET.get('party', None)
party = party if party in valid_parties else None
if state or seat or party:
pol_filters = ["state = '%s'" % state if state else None, "seat = '%s'" % seat if seat else None]
if party:
if party in ('R', 'D'):
pol_filters.append("party = '%s'" % party)
else:
pol_filters.append("party not in ('R', 'D')")
pol_where = " and ".join(filter(lambda x:x, pol_filters))
extra_joins.append("inner join (select distinct on (entity_id) * from matchbox_politicianmetadata where %s order by entity_id, cycle desc) mbpm on e.id = mbpm.entity_id" % pol_where)
elif subtype == 'industries':
where_filters.append("e.type = 'industry'")
elif subtype == 'lobbying_firms':
where_filters.append("e.type = 'organization' and mbom.lobbying_firm = 't'")
extra_joins.append("left join (select distinct on (entity_id) * from matchbox_organizationmetadata order by entity_id, cycle desc) mbom on e.id = mbom.entity_id")
elif subtype == 'political_groups':
where_filters.append("e.type = 'organization'")
extra_joins.append("inner join (select distinct on (entity_id) * from matchbox_entityattribute where namespace = 'urn:fec:committee') mbea on e.id = mbea.entity_id")
elif subtype == 'other_orgs':
where_filters.append("e.type = 'organization' and mbom.lobbying_firm = 'f'")
where_filters.append("(coalesce(mbea.value, '') = '' or mbom.is_corporation = 't' or mbom.is_cooperative = 't' or mbom.is_corp_w_o_capital_stock = 't')")
extra_joins.append("left join (select distinct on (entity_id) * from matchbox_entityattribute where namespace = 'urn:fec:committee') mbea on e.id = mbea.entity_id")
extra_joins.append("left join (select distinct on (entity_id) * from matchbox_organizationmetadata order by entity_id, cycle desc) mbom on e.id = mbom.entity_id")
else:
# subtype implies type, so only use explicit type if there's not a subtype
etype_raw = request.GET.get('type', None)
if etype_raw:
allowed_types = set(('organization', 'industry', 'individual', 'politician'))
entity_type = [etype for etype in etype_raw.split(',') if etype in allowed_types]
if entity_type:
where_filters.append("e.type in (%s)" % ','.join(["'%s'" % etype for etype in entity_type]))
filters['type'] = entity_type
where_clause = "where %s" % (" and ".join(where_filters)) if where_filters else ""
join_clause = " ".join(extra_joins)
query_params = (parsed_query,)
raw_result = execute_top(stmt.replace("WHERE", where_clause).replace("JOINS", join_clause), *query_params)
total = len(raw_result)
results = [dict(zip(self.fields, row)) for row in raw_result[start:end]]
print raw_result, results
if total:
ids = ','.join(["'%s'" % row['id'] for row in results])
totals = dict()
for row in execute_top(search_totals_stmt.replace("%s", ids)):
totals[row[0]] = dict(zip(EntityHandler.totals_fields, row[1:]))
# grab the metadata
entities = {e.id: e for e in Entity.objects.select_related().filter(id__in=[row['id'] for row in results])}
for row in results:
row['totals'] = totals.get(row['id'], None)
# match metadata, but strip out year keys
meta = entities[row['id']].metadata if row['id'] in entities else None
row['metadata'] = {k: v for k, v in meta.items() if not (type(k) == int or k.isdigit())} if meta else None
row['external_ids'] = [{'namespace': attr.namespace, 'id': attr.value} for attr in entities[row['id']].attributes.all()] if row['id'] in entities else None
return {
'results': results,
'page': page,
'per_page': per_page,
'total': total,
'pages': math.ceil(float(total) / per_page),
'filters': filters
}
