def autocomplete():
conn = psycopg2.connect("dbname=autocomplete-tribute user=christian",
connection_factory=psycopg2.extras.RealDictConnection)
cursor = conn.cursor()
where = {}
if request.args['query']:
query_tokens = request.args['query'].split()
fields = ['adjective', 'animal', 'version']
# MUST be a set in this context!
where[('||'.join(fields), 'ilike')] = set(['%%%s%%' % v for v in query_tokens])
# else: we want everything!
return jsonify(success=True,
data=db.select(cursor, 'ubuntu', where=where, order_by='id'))
def get(g, request, tables, query_fields=None, query_op='ilike', what='*',
join=None, where=None, field_map=None, order_by=None):
if query_fields is None: query_fields = ()
if join is None: join = {}
if where is None: where = {}
if field_map is None: field_map = {}
assert query_fields.__class__ is tuple
cursor = g.db.cursor()
if 'sort' in request.args:
assert order_by is None
sort_args = json.loads(request.args['sort'])
order_by = ','.join(['%s %s' % (sa['property'], sa['direction']) for sa in sort_args])
# todo: put that somewhere that makes more sense
comp_op_map = {'lt':'<', 'gt':'>', 'le':'<=', 'ge':'>=', 'eq':'='}
if request.args.get('filter', '').strip():
for filter_arg in json.loads(request.args['filter']):
f = filter_arg['field']
if filter_arg['type'] == 'string':
where[(field_map.get(f, f), 'ilike', 'unaccent')] = set(['%%%s%%' % v for v in filter_arg['value'].split()])
elif filter_arg['type'] == 'list':
where[field_map.get(f, f)] = tuple(filter_arg['value'])
else:
where[(field_map.get(f, f), comp_op_map[filter_arg.get('comparison', 'eq')])] = filter_arg['value']
elif request.args.get('query', '').strip():
# autocomplete query
if query_op.lower() in ['ilike', 'like']:
# this is needed because the concat operator (||) doesn't work with null values
query_fields = ["coalesce(%s::text, '')" % f for f in query_fields]
where[('||'.join(query_fields), query_op, 'unaccent')] = set(['%%%s%%' % v for v in request.args['query'].split()])
# exact field query: assumes only 1 field, because there is only 1 query value
else:
assert len(query_fields) == 1
where[(query_fields[0], query_op)] = request.args['query']
json_out = {'success': True}
json_out['total'] = pg.count(cursor, tables, what=what, join=join, where=where, debug_assert=False)
json_out['rows'] = pg.select(cursor, tables, what=what, join=join, where=where,
offset=request.args.get('start', None), limit=request.args.get('limit', None) or None,
order_by=order_by, debug_assert=False)
return json_out