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 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 + "':*"))