def search(conn: directive.connection, tables: directive.tables, locale: directive.locale, query: hug.types.text, limit: hug.types.in_range(1, 100) = 20, page: hug.types.in_range(1, 10) = 1): """ Search a route by name. `query` contains the string to search for. _limit_ ist the maximum number of results to return. _page_ the batch number of results to return, i.e. the requests returns results `[(page - 1) * limit, page * limit[`. """ maxresults = page * limit res = RouteList(query=query, page=page) r = tables.routes.data base = sa.select(RouteItem.make_selectables(r)) # First try: exact match of ref sql = base.where( sa.func.lower(r.c.ref) == query.lower()).limit(maxresults + 1) res.set_items(conn.execute(sql), locale) # If that did not work and the search term is a number, maybe a relation # number? if len(res) == 0 and len(query) > 3 and query.isdigit(): sql = base.where(r.c.id == int(query)) res.set_items(conn.execute(sql), locale) if len(res) > 0: return res # Second try: fuzzy matching of text if len(res) <= maxresults: remain = maxresults - len(res) # Preselect matches by doing a word match on name and intnames. primary_sim = r.c.name + sa.func.jsonb_path_query_array( r.c.intnames, '$.*', type_=sa.Text) primary_sim = primary_sim.op('<->>>', return_type=sa.Float)(query) primary_sim = primary_sim.label('sim') # Rerank by full match against main name second_sim = r.c.name.op('<->', return_type=sa.Float)(query) second_sim = second_sim.label('secsim') inner = base.add_columns(primary_sim, second_sim)\ .order_by(primary_sim)\ .limit(min(1100, remain * 10))\ .alias('inner') # Rerank by full match against main name rematch_sim = (inner.c.sim + inner.c.secsim).label('finsim') sql = sa.select(inner.c)\ .add_columns(rematch_sim)\ .order_by(rematch_sim)\ .limit(remain) minsim = None for o in conn.execute(sql): if minsim is None: minsim = o['finsim'] elif o['finsim'] - 0.3 > minsim: break res.add_item(o, locale) if page > 1: res.drop_leading_results((page - 1) * limit) return res
def search(conn: directive.connection, tables: directive.tables, locale: directive.locale, query: hug.types.text, limit: hug.types.in_range(1, 100) = 20, page: hug.types.in_range(1, 10) = 1): """ Search a route by name. `query` contains the string to search for. _limit_ ist the maximum number of results to return. _page_ the batch number of results to return, i.e. the requests returns results `[(page - 1) * limit, page * limit[`. """ maxresults = page * limit r = tables.routes.data sels = RouteItem.make_selectables(r) sels.append(sa.literal('relation').label('type')) rbase = sa.select(sels) w = tables.ways.data ws = tables.joined_ways.data wbase = sa.select([sa.func.coalesce(ws.c.id, w.c.id).label('id'), sa.case([(ws.c.id == None, 'way')], else_='wayset').label('type'), w.c.name, w.c.intnames, w.c.symbol, w.c.piste], distinct=True)\ .select_from(w.outerjoin(ws, w.c.id == ws.c.child)) todos = ((r, rbase), (w, wbase)) objs = RouteList(query=query, page=page) # First try: exact match of ref for t, base in todos: if len(objs) <= maxresults: refmatch = base.where(t.c.name == '[%s]' % query).limit(maxresults - len(objs) + 1) objs.add_items(conn.execute(refmatch), locale) # If that did not work and the search term is a number, maybe a relation # number? if not objs and len(query) > 3 and query.isdigit(): for t, base in todos: idmatch = base.where(t.c.id == int(query)) objs.add_items(conn.execute(idmatch), locale) if objs: return objs # Second try: fuzzy matching of text for t, base in todos: if len(objs) <= maxresults: sim = sa.func.similarity(t.c.name, query) res = base.add_columns(sim.label('sim'))\ .where(t.c.name.notlike('(%'))\ .order_by(sa.desc(sim))\ .limit(maxresults - len(objs) + 1) if objs: res = res.where(sim > 0.5) else: res = res.where(sim > 0.1) maxsim = None for r in conn.execute(res): if maxsim is None: maxsim = r['sim'] elif maxsim > r['sim'] * 3: break objs.add_item(r, locale) return objs