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