def handle_info_json(self, model, info, fulltextsearch=None):
"""Handle info JSON query filter."""
clauses = []
headlines = []
order_by_ranks = []
if '::' in info:
pairs = info.split('|')
for pair in pairs:
if pair != '':
k,v = pair.split("::")
if fulltextsearch == '1':
vector = _entity_descriptor(model, 'info')[k].astext
clause = func.to_tsvector(vector).match(v)
clauses.append(clause)
if len(headlines) == 0:
headline = func.ts_headline(self.language, vector, func.to_tsquery(v))
headlines.append(headline)
order = func.ts_rank_cd(func.to_tsvector(vector), func.to_tsquery(v), 4).label('rank')
order_by_ranks.append(order)
else:
clauses.append(_entity_descriptor(model,
'info')[k].astext == v)
else:
info = json.dumps(info)
clauses.append(cast(_entity_descriptor(model, 'info'),
Text) == info)
return clauses, headlines, order_by_ranks
def handle_info_json(self, model, info, fulltextsearch=None):
"""Handle info JSON query filter."""
clauses = []
headlines = []
order_by_ranks = []
if '::' in info:
pairs = info.split('|')
for pair in pairs:
if pair != '':
k, v = pair.split("::")
if fulltextsearch == '1':
vector = _entity_descriptor(model, 'info')[k].astext
clause = func.to_tsvector(vector).match(v)
clauses.append(clause)
if len(headlines) == 0:
headline = func.ts_headline(
self.language, vector, func.to_tsquery(v))
headlines.append(headline)
order = func.ts_rank_cd(func.to_tsvector(vector),
func.to_tsquery(v),
4).label('rank')
order_by_ranks.append(order)
else:
clauses.append(
_entity_descriptor(model, 'info')[k].astext == v)
else:
info = json.dumps(info)
clauses.append(
cast(_entity_descriptor(model, 'info'), Text) == info)
return clauses, headlines, order_by_ranks
def _apply_dynamic_fulltext_filters(ingredients, backup_search=False):
"""
Applies full text filters similar to v1 but uses full text search approach for lexemes and speed up with
index usage. N.B. that spaces in ingredient names will be replaced with '&'
:param ingredients: List<string>: ["onion", "chicken", "pork tenderloin"]
:return:
"""
dynamic_filters = []
max_ingredients = 500 if len(
ingredients
) > 2 else 25 # 5 if backup_search else 250 # limits the amount of match ingredients; necessary in large or backup search
title_subquery = lambda _: IngredientRecipe.recipe.in_(
[-1]) # function to add title checking fo backup query
if backup_search:
max_ingredients = 10
# searches for some combination of ingredients instead of individual ingredients in the backup search
ingredients = ['|'.join(i) for i in combinations(ingredients, 3)] \
if len(ingredients) >= 3 else ['|'.join(ingredients)]
# print(len(ingredients))
random.shuffle(ingredients)
ingredients = ingredients[:5]
title_subquery = lambda ingredient: (IngredientRecipe.recipe.in_(
db.session.query(Recipe.pk).filter(
func.to_tsquery(FULLTEXT_INDEX_CONFIG, ingredient).op('@@')
(func.to_tsvector(FULLTEXT_INDEX_CONFIG,
func.coalesce(Recipe.title)))).limit(50)))
# print(ingredients)
for ingredient in ingredients:
dynamic_filters.append(
or_(
IngredientRecipe.recipe.in_(
db.session.query(IngredientRecipe.recipe).filter(
IngredientRecipe.ingredient.in_(
db.session.query(Ingredient.pk).filter(
func.to_tsquery(FULLTEXT_INDEX_CONFIG, func.coalesce(ingredient)).op('@@')(
func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Ingredient.name))
)
).\
order_by(
desc(
func.similarity(
ingredient,
Ingredient.name
)
)
).limit(max_ingredients)
)
),
),
title_subquery(ingredient)
)
)
return dynamic_filters
def search(self, search_string, limit=None, offset=None):
query = (db.session.query(IndexedCategory.id)
.filter(IndexedCategory.title_vector.op('@@')(
func.to_tsquery('simple', preprocess_ts_string(search_string))))
.limit(limit)
.offset(offset))
return [x[0] for x in query]
def _get_fts_phrase_clauses(self, data):
"""Return full-text search phrase clauses."""
q = self._parse_json('fts_phrase', data)
err_base = 'invalid "fts_phrase" clause'
clauses = []
for col, settings in q.items():
vector = self._get_vector(col)
# Check params
if not isinstance(settings, dict):
msg = '{0}: {1} is not {2}'.format(err_base, col, dict)
raise ValueError(msg)
query = settings.get('query')
if not query:
msg = '{0}: "query" is required'.format(err_base)
raise ValueError(msg)
distance = settings.get('distance', 1)
operator = ' <{}> '.format(distance)
# Generate clause
tokens = query.split()
word_clauses = []
query_str = operator.join(tokens)
ts_query = func.to_tsquery(query_str)
clause = func.to_tsvector(vector).op('@@')(ts_query)
clauses.append(clause)
return clauses
def search(self, qs):
qs_ = get_ts_search_string(qs)
if qs_:
query = func.to_tsquery('english', qs_)
else:
query = func.plainto_tsquery('english', qs)
return self.model_col.op('@@')(query)
def search(self, search_string, limit=None, offset=None):
query = (db.session.query(IndexedCategory.id)
.filter(IndexedCategory.title_vector.op('@@')(
func.to_tsquery('simple', preprocess_ts_string(search_string))))
.limit(limit)
.offset(offset))
return [x[0] for x in query]
def make_fts_expr_tsvec(languages, target, searchstring, op="&"):
"""Searches tsvector column. `target` should have a gin index.
:param language: postgresql language string
:param target: SQLAlchemy expression with type tsvector
:param searchstring: string of space-separated words to search
:param op: operator used to join searchterms separated by space, | or &
"""
languages = list(languages)
prepared_searchstring = _prepare_searchstring(op, searchstring)
ts_query = func.to_tsquery(languages[0], prepared_searchstring)
for language in languages[1:]:
ts_query = ts_query.op("||")(func.to_tsquery(language, prepared_searchstring))
return target.op("@@")(ts_query)
def finalize_query(self, query, fltr, session, qstring=None, order_by=None):
search_query = None
ranked = False
if qstring is not None:
ft_query = and_(SearchObjectIndex.so_uuid == ObjectInfoIndex.uuid, query)
q = session.query(
ObjectInfoIndex,
func.ts_rank_cd(
SearchObjectIndex.search_vector,
func.plainto_tsquery(qstring)
).label('rank'))\
.options(subqueryload(ObjectInfoIndex.search_object))\
.options(subqueryload(ObjectInfoIndex.properties)).filter(ft_query)
query_result = search(q, qstring, vector=SearchObjectIndex.search_vector, sort=order_by is None, regconfig='simple')
ranked = True
else:
query_result = session.query(ObjectInfoIndex).options(subqueryload(ObjectInfoIndex.properties)).filter(query)
if order_by is not None:
query_result = query_result.order_by(order_by)
elif ranked is True:
query_result = query_result.order_by(
desc(
func.ts_rank_cd(
SearchObjectIndex.search_vector,
func.to_tsquery(search_query)
)
)
)
if 'limit' in fltr:
query_result = query_result.limit(fltr['limit'])
return query_result, ranked
def create_fulltext_ingredient_search(ingredients,
limit=DEFAULT_SEARCH_RESULT_SIZE,
op=and_,
backup_search=False):
"""
Function to create a fulltext query to filter out all recipes not containing <min_ingredients> ingredients. Ranks by
recipe that contains the most ingredients, and then ranks by match of ingredients list to the title. This could
probably be improved by adding additional search criteria similar to the previous fulltext search approach in
create_fulltext_search_query.
:param ingredients: List<string> ["onion", "chicken", "peppers"]
:param limit: number of recipes to return
:param order_by: the operation/func with which to order searches
:return: List<Recipe>
"""
ingredients = _clean_and_stringify_ingredients_query(ingredients)
return db.session.query(Recipe). \
join(IngredientRecipe). \
join(Ingredient). \
filter(
op(
*_apply_dynamic_fulltext_filters(ingredients, backup_search=backup_search)
)
). \
group_by(Recipe.pk). \
order_by(desc(
func.ts_rank_cd(
func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Recipe.title)),
func.to_tsquery(FULLTEXT_INDEX_CONFIG, '|'.join(i for i in ingredients)),
32
) * RECIPE_TITLE_WEIGHT +
func.ts_rank_cd(
func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Recipe.recipe_ingredients_text)),
func.to_tsquery(FULLTEXT_INDEX_CONFIG, '|'.join(i for i in ingredients)),
32
) * RECIPE_INGREDIENTS_WEIGHT +
func.sum(
func.ts_rank(
func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Ingredient.name)),
func.to_tsquery(FULLTEXT_INDEX_CONFIG, '|'.join(i for i in ingredients))
)
) * INGREDIENTS_WEIGHT +
func.ts_rank_cd(
func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Recipe.recipe_ingredients_text)),
func.to_tsquery(FULLTEXT_INDEX_CONFIG, '&'.join(i for i in ingredients)),
32
) * RECIPE_MODIFIERS_WEIGHT
)).limit(limit).all()
def _db_match(self, dbquery, size, start, query, search_vector):
if query:
query = re.sub("\W+", " ", query).strip().replace(" ", " & ")
dbquery = dbquery.filter(search_vector.match(query))
total = dbquery.count()
if query:
dbquery = dbquery.order_by(func.ts_rank(search_vector, func.to_tsquery(query)).desc())
dbquery = dbquery.offset(start).limit(size)
return dbquery, total
def finalize_query(self,
query,
fltr,
session,
qstring=None,
order_by=None):
search_query = None
if qstring is not None:
search_query = parse_search_query(qstring)
ft_query = and_(
SearchObjectIndex.search_vector.match(
search_query,
sort=order_by is None,
postgresql_regconfig='simple'),
SearchObjectIndex.so_uuid == ObjectInfoIndex.uuid, query)
else:
ft_query = query
ranked = False
if search_query is not None:
query_result = session.query(
ObjectInfoIndex,
func.ts_rank_cd(
SearchObjectIndex.search_vector,
func.to_tsquery(search_query)).label('rank')).options(
joinedload(ObjectInfoIndex.search_object)).options(
joinedload(
ObjectInfoIndex.properties)).filter(ft_query)
ranked = True
else:
query_result = session.query(ObjectInfoIndex).options(
joinedload(ObjectInfoIndex.properties)).filter(ft_query)
if order_by is not None:
query_result = query_result.order_by(order_by)
elif ranked is True:
query_result = query_result.order_by(
desc(
func.ts_rank_cd(SearchObjectIndex.search_vector,
func.to_tsquery(search_query))))
if 'limit' in fltr:
query_result = query_result.limit(fltr['limit'])
return query_result, ranked
def handle_info_json(self, model, info, fulltextsearch=None):
"""Handle info JSON query filter."""
clauses = []
headlines = []
order_by_ranks = []
if info and '::' in info:
pairs = info.split('|')
for pair in pairs:
if pair != '':
k, v = pair.split("::")
if fulltextsearch == '1':
vector = _entity_descriptor(model, 'info')[k].astext
clause = func.to_tsvector(vector).match(v)
clauses.append(clause)
if len(headlines) == 0:
headline = func.ts_headline(
self.language,
vector,
func.to_tsquery(v))
headlines.append(headline)
order = func.ts_rank_cd(
func.to_tsvector(vector),
func.to_tsquery(v), 4).label('rank')
order_by_ranks.append(order)
else:
clauses.append(
_entity_descriptor(model, 'info')[k].astext == v)
else:
if type(info) == dict:
clauses.append(_entity_descriptor(model, 'info') == info)
if type(info) == str or type(info) == str:
try:
info = json.loads(info)
if type(info) == int or type(info) == float:
info = '"%s"' % info
except ValueError:
info = '"%s"' % info
clauses.append(_entity_descriptor(model,
'info').contains(info))
return clauses, headlines, order_by_ranks
def search(self, search_string, order_by='start'):
if order_by == 'start':
order = IndexedEvent.start_date.desc()
elif order_by == 'id':
order = IndexedEvent.id
else:
order = None
return (db.session.query(IndexedEvent.id)
.filter(IndexedEvent.title_vector.op('@@')(
func.to_tsquery('simple', preprocess_ts_string(search_string))))
.order_by(order))
def text_search_by_words(self, words: list) -> list:
search_words = ' & '.join(words)
with session_scope(self.get_session) as session:
results = session.query(Tournament) \
.filter(Tournament.tsv.match(search_words, postgresql_regconfig='english')).all()
return [Tournament(id=t.id,
name=t.name,
description=session.query(
func.ts_headline('english', t.description,
func.to_tsquery(search_words, postgresql_regconfig='english')))
.first())
for t in results]
def _make_languages_tsquery(languages, searchstring, op):
"""
Prepares a `tsquery` expression to be used
in the construction of search expressions.
:param languages: postgresql language string
:param searchstring: string of space-separated words to search
:param op: operator used to join searchterms separated by space, | or &
"""
prepared_searchstring = _prepare_searchstring(op, searchstring)
mk_query = lambda lang: func.to_tsquery(lang, prepared_searchstring)
ts_queries = _itertools.imap(mk_query, languages)
return reduce(lambda query1, query2: query1.op("||")(query2), ts_queries)
def text_search(self, tquery_str: str) -> "BookmarkViewQueryBuilder":
self._query = self._query.outerjoin(
FullText, FullText.url_uuid == SQLABookmark.url_uuid)
# necessary to coalesce this as there may be no fulltext
fulltext = func.coalesce(FullText.tsvector, func.to_tsvector(""))
self._combined_tsvector = (func.to_tsvector(
SQLABookmark.title).op("||")(func.to_tsvector(
SQLABookmark.description)).op("||")(fulltext))
self._tsquery = func.to_tsquery(tquery_str)
self._query = self._query.filter(
self._combined_tsvector.op("@@")(self._tsquery))
return self
def search(name):
name = name.lower()
queries = name.split() # makes an array
print(queries)
tup = ()
a = and_(Character.tsvector_col.match(q) for q in queries)
o = or_(Character.tsvector_col.match(q) for q in queries)
#achar = and_(*tup)
#ochar = or_(tup)
a_string = ''
for index in range(0, len(queries)):
a_string += queries[index]
if index + 1 is not len(queries):
a_string += ' & '
print(a_string)
o_string = ''
for index in range(0, len(queries)):
o_string += queries[index]
if index + 1 is not len(queries):
o_string += ' | '
print(o_string)
acharacters = db.session.query(Character, func.ts_headline('english', Character.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Character.publisher_name, func.plainto_tsquery(a_string)).label('hpub'), func.ts_headline('english', Character.appear, func.plainto_tsquery(a_string)).label('happear')).filter(and_(Character.tsvector_col.match(q) for q in queries)).all()
ocharacters = db.session.query(Character, func.ts_headline('english', Character.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Character.publisher_name, func.plainto_tsquery(o_string)).label('hpub'), func.ts_headline('english', Character.appear, func.plainto_tsquery(o_string)).label('happear')).filter(or_(Character.tsvector_col.match(q) for q in queries)).all()
apublishers = db.session.query(Publisher, func.ts_headline('english', Publisher.state, func.to_tsquery(a_string)).label('hstate'), func.ts_headline('english', Publisher.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Publisher.address, func.plainto_tsquery(a_string)).label('haddress'), func.ts_headline('english', Publisher.city, func.plainto_tsquery(name)).label('hcity')).filter(and_(Publisher.tsvector_col.match(q) for q in queries)).all()
opublishers = db.session.query(Publisher, func.ts_headline('english', Publisher.state, func.to_tsquery(o_string)).label('hstate'), func.ts_headline('english', Publisher.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Publisher.address, func.plainto_tsquery(o_string)).label('haddress'), func.ts_headline('english', Publisher.city, func.plainto_tsquery(name)).label('hcity')).filter(or_(Publisher.tsvector_col.match(q) for q in queries)).all()
ateams = db.session.query(Team, func.ts_headline('english', Team.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Team.publisher_name, func.plainto_tsquery(a_string)).label('hpub'), func.ts_headline('english', Team.appear, func.plainto_tsquery(a_string)).label('happear')).filter(and_(Team.tsvector_col.match(q) for q in queries)).all()
oteams = db.session.query(Team, func.ts_headline('english', Team.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Team.publisher_name, func.plainto_tsquery(o_string)).label('hpub'), func.ts_headline('english', Team.appear, func.plainto_tsquery(o_string)).label('happear')).filter(or_(Team.tsvector_col.match(q) for q in queries)).all()
avolumes = db.session.query(Volume, func.ts_headline('english', Volume.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Volume.publisher_name, func.plainto_tsquery(a_string)).label('hpub'), func.ts_headline('english', Volume.start_year, func.plainto_tsquery(a_string)).label('hstart')).filter(and_(Volume.tsvector_col.match(q) for q in queries)).all()
ovolumes = db.session.query(Volume, func.ts_headline('english', Volume.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Volume.publisher_name, func.plainto_tsquery(o_string)).label('hpub'), func.ts_headline('english', Volume.start_year, func.plainto_tsquery(o_string)).label('hstart')).filter(or_(Volume.tsvector_col.match(q) for q in queries)).all()
#print(publishers)
#print(teams)
#print(volumes)
return render_template('search_result_template.html', acharacters=acharacters, ocharacters=ocharacters, opublishers=opublishers, apublishers=apublishers, avolumes=avolumes, ovolumes=ovolumes, ateams=ateams, oteams=oteams)
def search(self, search_string, order_by='start'):
if order_by == 'start':
order = IndexedEvent.start_date.desc()
elif order_by == 'id':
order = IndexedEvent.id
else:
order = None
return (db.session.query(IndexedEvent.id).join(
Event,
Event.id == IndexedEvent.id).filter(~Event.is_deleted).filter(
IndexedEvent.title_vector.op('@@')(func.to_tsquery(
'simple',
preprocess_ts_string(search_string)))).order_by(order))
def handle_info_json(self, model, info, fulltextsearch=None):
"""Handle info JSON query filter."""
clauses = []
headlines = []
order_by_ranks = []
if info and '::' in info:
pairs = info.split('|')
for pair in pairs:
if pair != '':
k,v = pair.split("::")
if fulltextsearch == '1':
vector = _entity_descriptor(model, 'info')[k].astext
clause = func.to_tsvector(vector).match(v)
clauses.append(clause)
if len(headlines) == 0:
headline = func.ts_headline(self.language, vector, func.to_tsquery(v))
headlines.append(headline)
order = func.ts_rank_cd(func.to_tsvector(vector), func.to_tsquery(v), 4).label('rank')
order_by_ranks.append(order)
else:
clauses.append(_entity_descriptor(model,
'info')[k].astext == v)
else:
if type(info) == dict:
clauses.append(_entity_descriptor(model, 'info') == info)
if type(info) == str or type(info) == unicode:
try:
info = json.loads(info)
if type(info) == int or type(info) == float:
info = '"%s"' % info
except ValueError:
info = '"%s"' % info
clauses.append(_entity_descriptor(model,
'info').contains(info))
return clauses, headlines, order_by_ranks
def text_search_by_word(self, word) -> list:
with session_scope(self.get_session) as session:
results = session.query(Team).filter(
Team.tsv.match(word, postgresql_regconfig='english')).all()
return [
Team(id=t.id,
name=t.name,
description=session.query(
func.ts_headline(
'english', t.description,
func.to_tsquery(
word,
postgresql_regconfig='english'))).first())
for t in results
]
def fulltext_search(self, query: str, including: bool):
"""
SELECT id, ts_headline(description, q) FROM (
SELECT id, description, q
FROM goods, to_tsquery('english', 'query & here') q
WHERE to_tsvector('english', description) @@ q
) search_t;
"""
query = self.__prepare_query(query, including)
q = func.to_tsquery('english', query)
inner_statement = self.__session \
.query(Goods.id, Goods.description, q) \
.select_from(q) \
.filter(func.to_tsvector('english', Goods.description).match(query, postgresql_regconfig='english')) \
.subquery()
return self.__session.query(
inner_statement.c.id,
func.ts_headline(inner_statement.c.description, q)).all()
def resolve_search(self, info, title, types=None, result=None):
tsquery = func.to_tsquery(f'\'{title}\'')
query = (
TitleModel
.query
.filter(TitleModel.title_search_col.op('@@')(tsquery))
)
query = (
query.filter(TitleModel._type.in_(types))
if types is not None else query
)
query = (
query
.join(TitleModel.rating)
.order_by(
desc(RatingModel.numVotes >= 1000),
desc(TitleModel.primaryTitle.ilike(title)),
desc(RatingModel.numVotes),
desc(func.ts_rank_cd(TitleModel.title_search_col, tsquery, 1))
)
.limit(result)
)
return query
def fulltextsearch(self):
lang = locale_negotiator(self.request)
try:
language = self.languages[lang]
except KeyError:
return HTTPInternalServerError(
detail="%s not defined in languages" % lang)
if "query" not in self.request.params:
return HTTPBadRequest(detail="no query")
terms = self.request.params.get("query")
maxlimit = self.settings.get("maxlimit", 200)
try:
limit = int(self.request.params.get(
"limit",
self.settings.get("defaultlimit", 30)))
except ValueError:
return HTTPBadRequest(detail="limit value is incorrect")
if limit > maxlimit:
limit = maxlimit
try:
partitionlimit = int(self.request.params.get("partitionlimit", 0))
except ValueError:
return HTTPBadRequest(detail="partitionlimit value is incorrect")
if partitionlimit > maxlimit:
partitionlimit = maxlimit
terms_ts = "&".join(w + ":*"
for w in IGNORED_CHARS_RE.sub(" ", terms).split(" ") if w != "")
_filter = FullTextSearch.ts.op("@@")(func.to_tsquery(language, terms_ts))
if self.request.user is None or self.request.user.role is None:
_filter = and_(_filter, FullTextSearch.public.is_(True))
else:
_filter = and_(
_filter,
or_(
FullTextSearch.public.is_(True),
FullTextSearch.role_id.is_(None),
FullTextSearch.role_id == self.request.user.role.id
)
)
if "interface" in self.request.params:
_filter = and_(_filter, or_(
FullTextSearch.interface_id.is_(None),
FullTextSearch.interface_id == self._get_interface_id(
self.request.params["interface"]
)
))
else:
_filter = and_(_filter, FullTextSearch.interface_id.is_(None))
_filter = and_(_filter, or_(
FullTextSearch.lang.is_(None),
FullTextSearch.lang == lang,
))
# The numbers used in ts_rank_cd() below indicate a normalization method.
# Several normalization methods can be combined using |.
# 2 divides the rank by the document length
# 8 divides the rank by the number of unique words in document
# By combining them, shorter results seem to be preferred over longer ones
# with the same ratio of matching words. But this relies only on testing it
# and on some assumptions about how it might be calculated
# (the normalization is applied two times with the combination of 2 and 8,
# so the effect on at least the one-word-results is therefore stronger).
rank = func.ts_rank_cd(FullTextSearch.ts, func.to_tsquery(language, terms_ts), 2 | 8)
if partitionlimit:
# Here we want to partition the search results based on
# layer_name and limit each partition.
row_number = func.row_number().over(
partition_by=FullTextSearch.layer_name,
order_by=(desc(rank), FullTextSearch.label)
).label("row_number")
subq = DBSession.query(FullTextSearch) \
.add_columns(row_number).filter(_filter).subquery()
query = DBSession.query(
subq.c.id, subq.c.label, subq.c.params, subq.c.layer_name,
subq.c.the_geom, subq.c.actions
)
query = query.filter(subq.c.row_number <= partitionlimit)
else:
query = DBSession.query(FullTextSearch).filter(_filter)
query = query.order_by(desc(rank))
query = query.order_by(FullTextSearch.label)
query = query.limit(limit)
objs = query.all()
features = []
for o in objs:
properties = {
"label": o.label,
}
if o.layer_name is not None:
properties["layer_name"] = o.layer_name
if o.params is not None:
properties["params"] = o.params
if o.actions is not None:
properties["actions"] = o.actions
if o.actions is None and o.layer_name is not None:
properties["actions"] = [{
"action": "add_layer",
"data": o.layer_name,
}]
if o.the_geom is not None:
geom = to_shape(o.the_geom)
feature = Feature(
id=o.id, geometry=geom,
properties=properties, bbox=geom.bounds
)
features.append(feature)
else:
feature = Feature(
id=o.id, properties=properties
)
features.append(feature)
# TODO: add callback function if provided in self.request, else return geojson
return FeatureCollection(features)
def fulltextsearch(self):
lang = locale_negotiator(self.request)
try:
language = self.languages[lang]
except KeyError:
return HTTPInternalServerError(
detail="{0!s} not defined in languages".format(lang))
if "query" not in self.request.params:
return HTTPBadRequest(detail="no query")
terms = self.request.params.get("query")
maxlimit = self.settings.get("maxlimit", 200)
try:
limit = int(
self.request.params.get("limit",
self.settings.get("defaultlimit", 30)))
except ValueError:
return HTTPBadRequest(detail="limit value is incorrect")
if limit > maxlimit:
limit = maxlimit
try:
partitionlimit = int(self.request.params.get("partitionlimit", 0))
except ValueError:
return HTTPBadRequest(detail="partitionlimit value is incorrect")
if partitionlimit > maxlimit:
partitionlimit = maxlimit
terms_ts = "&".join(
w + ":*" for w in IGNORED_CHARS_RE.sub(" ", terms).split(" ")
if w != "")
_filter = FullTextSearch.ts.op("@@")(func.to_tsquery(
language, terms_ts))
if self.request.user is None:
_filter = and_(_filter, FullTextSearch.public.is_(True))
else:
_filter = and_(
_filter,
or_(
FullTextSearch.public.is_(True),
FullTextSearch.role_id.is_(None),
FullTextSearch.role_id.in_(
[r.id for r in self.request.user.roles]),
),
)
if "interface" in self.request.params:
_filter = and_(
_filter,
or_(
FullTextSearch.interface_id.is_(None),
FullTextSearch.interface_id == self._get_interface_id(
self.request.params["interface"]),
),
)
else:
_filter = and_(_filter, FullTextSearch.interface_id.is_(None))
_filter = and_(
_filter,
or_(FullTextSearch.lang.is_(None), FullTextSearch.lang == lang))
rank_system = self.request.params.get("ranksystem")
if rank_system == "ts_rank_cd":
# The numbers used in ts_rank_cd() below indicate a normalization method.
# Several normalization methods can be combined using |.
# 2 divides the rank by the document length
# 8 divides the rank by the number of unique words in document
# By combining them, shorter results seem to be preferred over longer ones
# with the same ratio of matching words. But this relies only on testing it
# and on some assumptions about how it might be calculated
# (the normalization is applied two times with the combination of 2 and 8,
# so the effect on at least the one-word-results is therefore stronger).
rank = func.ts_rank_cd(FullTextSearch.ts,
func.to_tsquery(language, terms_ts), 2 | 8)
else:
# Use similarity ranking system from module pg_trgm.
rank = func.similarity(FullTextSearch.label, terms)
if partitionlimit:
# Here we want to partition the search results based on
# layer_name and limit each partition.
row_number = (func.row_number().over(
partition_by=FullTextSearch.layer_name,
order_by=(desc(rank),
FullTextSearch.label)).label("row_number"))
subq = DBSession.query(FullTextSearch).add_columns(
row_number).filter(_filter).subquery()
query = DBSession.query(subq.c.id, subq.c.label, subq.c.params,
subq.c.layer_name, subq.c.the_geom,
subq.c.actions)
query = query.filter(subq.c.row_number <= partitionlimit)
else:
query = DBSession.query(FullTextSearch).filter(_filter)
query = query.order_by(desc(rank))
query = query.order_by(FullTextSearch.label)
query = query.limit(limit)
objs = query.all()
features = []
for o in objs:
properties = {"label": o.label}
if o.layer_name is not None:
properties["layer_name"] = o.layer_name
if o.params is not None:
properties["params"] = o.params
if o.actions is not None:
properties["actions"] = o.actions
if o.actions is None and o.layer_name is not None:
properties["actions"] = [{
"action": "add_layer",
"data": o.layer_name
}]
if o.the_geom is not None:
geom = to_shape(o.the_geom)
feature = Feature(id=o.id,
geometry=geom,
properties=properties,
bbox=geom.bounds)
features.append(feature)
else:
feature = Feature(id=o.id, properties=properties)
features.append(feature)
return FeatureCollection(features)
def search(name):
name = name.lower()
queries = name.split() # makes an array
print(queries)
tup = ()
a = and_(Character.tsvector_col.match(q) for q in queries)
o = or_(Character.tsvector_col.match(q) for q in queries)
#achar = and_(*tup)
#ochar = or_(tup)
a_string = ''
for index in range(0, len(queries)):
a_string += queries[index]
if index + 1 is not len(queries):
a_string += ' & '
print(a_string)
o_string = ''
for index in range(0, len(queries)):
o_string += queries[index]
if index + 1 is not len(queries):
o_string += ' | '
print(o_string)
acharacters = db.session.query(
Character,
func.ts_headline('english', Character.name,
func.to_tsquery(a_string)).label('hname'),
func.ts_headline('english', Character.publisher_name,
func.plainto_tsquery(a_string)).label('hpub'),
func.ts_headline(
'english', Character.appear,
func.plainto_tsquery(a_string)).label('happear')).filter(
and_(Character.tsvector_col.match(q) for q in queries)).all()
ocharacters = db.session.query(
Character,
func.ts_headline('english', Character.name,
func.to_tsquery(o_string)).label('hname'),
func.ts_headline('english', Character.publisher_name,
func.plainto_tsquery(o_string)).label('hpub'),
func.ts_headline(
'english', Character.appear,
func.plainto_tsquery(o_string)).label('happear')).filter(
or_(Character.tsvector_col.match(q) for q in queries)).all()
apublishers = db.session.query(
Publisher,
func.ts_headline('english', Publisher.state,
func.to_tsquery(a_string)).label('hstate'),
func.ts_headline('english', Publisher.name,
func.to_tsquery(a_string)).label('hname'),
func.ts_headline('english', Publisher.address,
func.plainto_tsquery(a_string)).label('haddress'),
func.ts_headline(
'english', Publisher.city,
func.plainto_tsquery(name)).label('hcity')).filter(
and_(Publisher.tsvector_col.match(q) for q in queries)).all()
opublishers = db.session.query(
Publisher,
func.ts_headline('english', Publisher.state,
func.to_tsquery(o_string)).label('hstate'),
func.ts_headline('english', Publisher.name,
func.to_tsquery(o_string)).label('hname'),
func.ts_headline('english', Publisher.address,
func.plainto_tsquery(o_string)).label('haddress'),
func.ts_headline(
'english', Publisher.city,
func.plainto_tsquery(name)).label('hcity')).filter(
or_(Publisher.tsvector_col.match(q) for q in queries)).all()
ateams = db.session.query(
Team,
func.ts_headline('english', Team.name,
func.to_tsquery(a_string)).label('hname'),
func.ts_headline('english', Team.publisher_name,
func.plainto_tsquery(a_string)).label('hpub'),
func.ts_headline(
'english', Team.appear,
func.plainto_tsquery(a_string)).label('happear')).filter(
and_(Team.tsvector_col.match(q) for q in queries)).all()
oteams = db.session.query(
Team,
func.ts_headline('english', Team.name,
func.to_tsquery(o_string)).label('hname'),
func.ts_headline('english', Team.publisher_name,
func.plainto_tsquery(o_string)).label('hpub'),
func.ts_headline(
'english', Team.appear,
func.plainto_tsquery(o_string)).label('happear')).filter(
or_(Team.tsvector_col.match(q) for q in queries)).all()
avolumes = db.session.query(
Volume,
func.ts_headline('english', Volume.name,
func.to_tsquery(a_string)).label('hname'),
func.ts_headline('english', Volume.publisher_name,
func.plainto_tsquery(a_string)).label('hpub'),
func.ts_headline(
'english', Volume.start_year,
func.plainto_tsquery(a_string)).label('hstart')).filter(
and_(Volume.tsvector_col.match(q) for q in queries)).all()
ovolumes = db.session.query(
Volume,
func.ts_headline('english', Volume.name,
func.to_tsquery(o_string)).label('hname'),
func.ts_headline('english', Volume.publisher_name,
func.plainto_tsquery(o_string)).label('hpub'),
func.ts_headline(
'english', Volume.start_year,
func.plainto_tsquery(o_string)).label('hstart')).filter(
or_(Volume.tsvector_col.match(q) for q in queries)).all()
#print(publishers)
#print(teams)
#print(volumes)
return render_template('search_result_template.html',
acharacters=acharacters,
ocharacters=ocharacters,
opublishers=opublishers,
apublishers=apublishers,
avolumes=avolumes,
ovolumes=ovolumes,
ateams=ateams,
oteams=oteams)
def cond_func(lang):
return func.to_tsvector(lang, func.replace(target, ";", " ")).op("@@")(func.to_tsquery(lang, prepared_searchstring))
expr3 = c2 / 5
expr4 = -c2
expr5 = ~(c2 == 5)
q = column("q", Boolean)
expr6 = ~q
expr7 = c1 + "x"
expr8 = c2 + 10
stmt = select(column("q")).where(lambda: column("g") > 5).where(c2 == 5)
expr9 = c1.bool_op("@@")(func.to_tsquery("some & query"))
if typing.TYPE_CHECKING:
# as far as if this is ColumnElement, BinaryElement, SQLCoreOperations,
# that might change. main thing is it's SomeSQLColThing[bool] and
# not 'bool' or 'Any'.
# EXPECTED_RE_TYPE: sqlalchemy..*ColumnElement\[builtins.bool\]
reveal_type(expr1)
# EXPECTED_RE_TYPE: sqlalchemy..*ColumnClause\[builtins.str.?\]
reveal_type(c1)
# EXPECTED_RE_TYPE: sqlalchemy..*ColumnClause\[builtins.int.?\]
reveal_type(c2)
class UserSearch(object):
def __init__(self, request):
self.request = request
self.page_size = 50
self.frm = make_user_search_form(request)
def get(self):
if 'query' in self.request.GET and \
self.request.GET['query'].strip() and \
'clear_search' not in self.request.GET:
return self.run_search()
column = self.request.GET.get('column', 'username')
order = self.request.GET.get('order', 'asc')
# Capture the form values for re-use
appstruct = {}
appstruct['column'] = column
appstruct['order'] = order
query = self.request.db_session.query(UserProfile)
orderby = self.get_order_by()
query = query.order_by(orderby)
results = query.all()
paged = self.paginate_results(results)
pager = paged.pager()
sort_class = 'icon-arrow-down'
if order == 'desc':
sort_class = 'icon-arrow-up'
return {
'forms': [self.frm],
'rendered_form': self.frm.render(appstruct=appstruct),
'results': paged,
'ran_search': False,
'pager': pager,
'column': column,
'sort_class': sort_class,
}
def run_search(self):
results = []
pager = None
ran_search = False
try:
controls = self.request.GET.items()
appstruct = self.frm.validate(controls)
except ValidationFailure, e:
return {
'forms': [self.frm],
'rendered_form': e.render(),
'results': results,
'ran_search': ran_search,
'pager': None
}
#XXX: always default to treating the query as a prefix query??
tsquery = func.to_tsquery("'%s':*" % re.escape(appstruct['query']))
# build the shared query bit
query_select = select([tsquery.label('query')]).cte('query_select')
# build the ordered-by clause, using the shared query
orderby = self.get_order_by(query_select)
res = self.request.db_session.query(UserProfile)
res = res.filter(
UserProfile.searchable_text.op('@@')(select([query_select.c.query
])))
res = res.order_by(orderby)
results = res.all()
paged = self.paginate_results(results)
pager = paged.pager()
ran_search = True
sort_class = 'icon-arrow-down'
if self.request.GET.get('order', '') == 'desc':
sort_class = 'icon-arrow-up'
return {
'forms': [self.frm],
'rendered_form': self.frm.render(appstruct=appstruct),
'results': paged,
'ran_search': ran_search,
'pager': pager,
'column': self.request.GET.get('column', 'username'),
'sort_class': sort_class,
}
def get_exercises(favorited_by=None):
'''Get exercise collection, if favorited_by is set then get the
collection of favorites of the user.'''
user_id = auth.current_user.id if auth.current_user else None
# client requests favorites that are not his.
if favorited_by and favorited_by != user_id:
raise AuthorizationError
search = request.args.get('search')
category = request.args.get('category')
order_by = request.args.get('order_by')
author = request.args.get('author')
orderfunc = desc
if order_by and len(order_by) > 0 and order_by[-1] in '+ -'.split():
if order_by[-1] == '+':
orderfunc = asc
order_by = order_by[:-1]
query = Exercise.query
if search:
search_terms = (' | ').join(search.split())
query = query.add_columns(func.ts_rank(
Exercise.tsv, func.to_tsquery(search_terms)).label('search_rank')).\
filter(Exercise.tsv.match(search_terms))
if order_by == 'relevance':
query = query.order_by(nullslast(orderfunc('search_rank')))
if user_id:
user_rating = aliased(Rating, name='user_rating')
query = query.add_entity(user_rating).\
outerjoin(user_rating, and_(user_rating.exercise_id == Exercise.id,
user_rating.user_id == user_id))
if order_by == 'user_rating':
query = query.order_by(nullslast(orderfunc(user_rating.rating)))
elif order_by == 'user_fun_rating':
query = query.order_by(nullslast(orderfunc(user_rating.fun)))
elif order_by == 'user_effective_rating':
query = query.order_by(nullslast(orderfunc(user_rating.effective)))
elif order_by == 'user_clear_rating':
query = query.order_by(nullslast(orderfunc(user_rating.clear)))
# when if favorited_by is not None then we only want the user favorites
# and isouter will be set to False. Meaning we will do an inner join If
# favorited_by is None then isouter will be True and we will do an
# outer join meaning we want to know which exercises the user favorited
# but we want all of them.
isouter = not bool(favorited_by)
# include a column from the UserFavoriteExercise table or `0`.
# this will get serialized as a Boolean to signify favorited or not.
query = query.\
add_columns(func.coalesce(UserFavoriteExercise.exercise_id, 0).label('favorited')).\
join(UserFavoriteExercise,
and_(UserFavoriteExercise.exercise_id == Exercise.id,
UserFavoriteExercise.user_id == user_id),
isouter=isouter)
if author:
query = query.join(
User, and_(User.id == Exercise.author_id, User.username == author))
if category:
category = parse_query_params(request.args, key='category')
query = query.join(Category).filter(Category.name.in_(category))
if 'author' in parse_query_params(request.args, key='expand'):
query = query.options(joinedload(Exercise.author))
if order_by in ['average_rating', 'rating']:
query = query.order_by(nullslast(orderfunc(Exercise.avg_rating)))
elif order_by == 'average_fun_rating':
query = query.order_by(nullslast(orderfunc(Exercise.avg_fun_rating)))
elif order_by == 'average_clear_rating':
query = query.order_by(nullslast(orderfunc(Exercise.avg_clear_rating)))
elif order_by == 'average_effective_rating':
query = query.order_by(
nullslast(orderfunc(Exercise.avg_effective_rating)))
elif order_by == 'popularity':
query = query.order_by(nullslast(orderfunc(Exercise.popularity)))
elif order_by == 'updated_at':
query = query.order_by(orderfunc(Exercise.updated_at))
else:
query = query.order_by(orderfunc(Exercise.created_at))
page = Pagination(request, query=query)
return Serializer(ExerciseSchema, request.args).dump_page(page)
def __eq__(self, other):
return self.op('@@')(func.to_tsquery(other))
def match(column, language='german'):
return column.op('@@')(func.to_tsquery(language, term))
def cond_func(lang):
tsvector = func.to_tsvector(lang, func.replace(target, ";", " "))
tsquery = func.to_tsquery(lang, prepared_searchstring)
return tsvector.op("@@")(tsquery)
def my_bookmarks() -> flask.Response:
# FIXME: This viewfunc really needs to get split up and work via the data
# layer to get what it wants.
page_size = flask.current_app.config["PAGE_SIZE"]
page = int(flask.request.args.get("page", "1"))
offset = (page - 1) * page_size
user = get_current_user()
query = db.session.query(SQLABookmark).filter(
SQLABookmark.user_uuid == user.user_uuid
)
if "q" in flask.request.args:
query = query.outerjoin(FullText, FullText.url_uuid == SQLABookmark.url_uuid)
search_str = flask.request.args["q"]
tquery_str = parse_search_str(search_str)
log.debug('search_str, tquery_str = ("%s", "%s")', search_str, tquery_str)
# necessary to coalesce this as there may be no fulltext
fulltext = func.coalesce(FullText.tsvector, func.to_tsvector(""))
combined_tsvector = (
func.to_tsvector(SQLABookmark.title)
.op("||")(func.to_tsvector(SQLABookmark.description))
.op("||")(fulltext)
)
tsquery = func.to_tsquery(tquery_str)
query = query.filter(combined_tsvector.op("@@")(tsquery))
query = query.order_by(func.ts_rank(combined_tsvector, tsquery, 1))
page_title = search_str
else:
page_title = "Quarchive"
if page > 1:
page_title += " (page %s)" % page
# omit deleted bookmarks
query = query.filter(~SQLABookmark.deleted)
sqla_objs = (
query.order_by(SQLABookmark.created.desc()).offset(offset).limit(page_size)
)
prev_page_exists = page > 1
next_page_exists: bool = db.session.query(
query.order_by(SQLABookmark.created.desc()).offset(offset + page_size).exists()
).scalar()
bookmarks = []
for sqla_obj in sqla_objs:
url = sqla_obj.url_obj.to_url()
bookmarks.append((url, bookmark_from_sqla(url.to_string(), sqla_obj)))
return flask.make_response(
flask.render_template(
"my_bookmarks.html",
page_title=page_title,
bookmarks=bookmarks,
page=page,
prev_page_exists=prev_page_exists,
next_page_exists=next_page_exists,
q=flask.request.args.get("q"),
)
)
def search_filter(cls, term):
return (func.to_tsvector("simple", cls.name).op("||")(func.to_tsvector(
"simple",
cls.slug))).op("@@")(func.to_tsquery("simple", "'" + term + "':*"))
