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