def search(self): terms = request.args.get('search') q = db.session.query(Content).filter('content.search_vector '\ '@@ plainto_tsquery(:sterms)') q = q.params(sterms=terms) q = q.add_column(func.ts_headline('pg_catalog.english', Content.content, func.plainto_tsquery(terms), 'MaxFragments=5,FragmentDelimiter=|||,'\ 'StartSel="<span class=""shighlight"">", '\ 'StopSel = "</span>", ', )) q = q.add_column(func.ts_headline('pg_catalog.english', Content.title, func.plainto_tsquery(terms), 'HighlightAll=TRUE, '\ 'StartSel="<span class=""shighlight"">", '\ 'StopSel = "</span>"')) q = q.order_by('ts_rank_cd(content.search_vector, '\ 'plainto_tsquery(:sterms)) DESC') results = [(entry, fragments.split('|||'), title) for entry, fragments, title in q] return render_template('content/search.html', term=terms, results=results)
def _by_search_tsearch(self, query: Query, operand: str, maybe_negate: ConditionTransform) -> Query: tsquery = func.plainto_tsquery(literal("zulip.english_us_search"), literal(operand)) query = query.column(ts_locs_array(literal("zulip.english_us_search"), column("rendered_content"), tsquery).label("content_matches")) # We HTML-escape the topic in Postgres to avoid doing a server round-trip query = query.column(ts_locs_array(literal("zulip.english_us_search"), func.escape_html(topic_column_sa()), tsquery).label("topic_matches")) # Do quoted string matching. We really want phrase # search here so we can ignore punctuation and do # stemming, but there isn't a standard phrase search # mechanism in Postgres for term in re.findall(r'"[^"]+"|\S+', operand): if term[0] == '"' and term[-1] == '"': term = term[1:-1] term = '%' + connection.ops.prep_for_like_query(term) + '%' cond = or_(column("content").ilike(term), topic_column_sa().ilike(term)) query = query.where(maybe_negate(cond)) cond = column("search_tsvector").op("@@")(tsquery) return query.where(maybe_negate(cond))
def search_software(): term = request.args.get('term') if not term: flash('The search term was invalid.') return redirect(url_for('portal.index')) with session_scope() as session: q = session.query(SoftwareComponent) \ .join(SoftwareComponent.bin_packages) \ .filter(SoftwareComponent.__ts_vector__.op('@@')(func.plainto_tsquery(term))) \ .order_by(SoftwareComponent.cid, BinaryPackage.version.desc()) \ .distinct(SoftwareComponent.cid) results_count = q.count() software = q.all() results_per_page = results_count page_count = math.ceil(results_count / results_per_page) if results_per_page > 0 else 1 return render_template('software_search_results.html', term=term, results_count=results_count, results_per_page=results_per_page, page_count=page_count, software=software)
def search(self, search_string, page=0): tq = func.plainto_tsquery('english', search_string) q = self.session.query(BinComment) if self.ts_label is not None: q = q.join(Bin).filter(Bin.ts_label==self.ts_label) q = q.filter(BinComment.comment.op('@@')(tq)) return page_query(q, page, self.page_size)
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 search(): ''' Generic search page ''' form = SearchForm() if request.method == 'GET': return render_template('search.html', form=form) if request.method == 'POST': query = User.query #pylint: disable=no-member if form.country.data and form.country.data != 'ZZ': query = query.filter(User.country == form.country.data) if form.locales.data: query = query.join(User.languages).filter(UserLanguage.locale.in_( form.locales.data)) if form.expertise_domain_names.data: query = query.join(User.expertise_domains).filter(UserExpertiseDomain.name.in_( form.expertise_domain_names.data)) if form.fulltext.data: query = query.filter(func.to_tsvector(func.array_to_string(array([ User.first_name, User.last_name, User.organization, User.position, User.projects]), ' ')).op('@@')(func.plainto_tsquery(form.fulltext.data))) # TODO ordering by relevance return render_template('search-results.html', title='Expertise search', form=form, results=query.limit(20).all())
def _by_search_tsearch(self, query, operand, maybe_negate): # type: (Query, str, ConditionTransform) -> Query tsquery = func.plainto_tsquery(literal("zulip.english_us_search"), literal(operand)) ts_locs_array = func.ts_match_locs_array query = query.column(ts_locs_array(literal("zulip.english_us_search"), column("rendered_content"), tsquery).label("content_matches")) # We HTML-escape the subject in Postgres to avoid doing a server round-trip query = query.column(ts_locs_array(literal("zulip.english_us_search"), func.escape_html(column("subject")), tsquery).label("subject_matches")) # Do quoted string matching. We really want phrase # search here so we can ignore punctuation and do # stemming, but there isn't a standard phrase search # mechanism in Postgres for term in re.findall('"[^"]+"|\S+', operand): if term[0] == '"' and term[-1] == '"': term = term[1:-1] term = '%' + connection.ops.prep_for_like_query(term) + '%' cond = or_(column("content").ilike(term), column("subject").ilike(term)) query = query.where(maybe_negate(cond)) cond = column("search_tsvector").op("@@")(tsquery) return query.where(maybe_negate(cond))
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 mangle(self, results): return results.add_columns( func.ts_headline( 'pg_catalog.english', func.strip_tags(Content.content), func.plainto_tsquery(self.text), 'MaxFragments=3, FragmentDelimiter=" ... ", StartSel="<b>", StopSel="</b>", MinWords=7, MaxWords=15', type_=Unicode).label("content_short"))
def search(): results = [] form = SearchForm(request.form) if form.validate_on_submit(): search_terms = form.search_terms.data q = db_session.query(Search).\ filter('search.search_vector @@ plainto_tsquery(:terms)') q = q.params(terms=search_terms) q = q.add_column(func.ts_headline('pg_catalog.english', Search.text, func.plainto_tsquery(search_terms), 'MaxFragments=3,FragmentDelimiter=|||,' 'StartSel="<span class=""search-highlight"">", ' 'StopSel = "</span>", ', type_= Unicode)) q = q.add_column(func.ts_headline('pg_catalog.english', Search.title, func.plainto_tsquery(search_terms), 'HighlightAll=TRUE, ' 'StartSel="<span class=""search-title-highlight"">", ' 'StopSel = "</span>"', type_= Unicode)) q = q.order_by( 'ts_rank_cd(search.search_vector, plainto_tsquery(:terms)) DESC') q = q.limit(10) results=[{ 'object': db_session.query(getattr(getattr(getattr( chicagoclimateonline, SEARCH_MODELS[entry.object]), 'models'), entry.object.capitalize())).get(entry.object_id), 'fragments': fragments.split('|||'), 'title': title, 'module': SEARCH_MODELS[entry.object], 'class': entry.object } for entry, fragments, title in q] flash('Your results for <b>{}</b>'.format(search_terms)) else: flash('''Terribly sorry, but it seems that there was a problem with the search terms you entered.''') return render_template( 'etc/search.html', # search_terms=search_terms, results=results, search_form=form, )
def _order_by_offer_name_containing_keyword_string(keywords_string: str, query: Query) -> Query: offer_alias = aliased(Offer) return query.order_by( desc( Offer.query .filter(Offer.id == offer_alias.id) .filter(Offer.name.op('@@')(func.plainto_tsquery(keywords_string))) .order_by(offer_alias.name) .exists() ) )
def search(request, query, begin=None, end=None): if end is not None: end = end + timedelta(days=1) q = request.db.query(File) \ .filter_by(ownerid=request.authenticated_userid) if query.strip(): q = q.filter(File.search_text.op('@@')(func.plainto_tsquery(query))) if begin is not None: q = q.filter(File.modified > begin) if end is not None: q = q.filter(File.modified < end) q = q.order_by(File.modified.desc()).limit(10) return { 'files': q.all() }
def search(page): form = SearchForm() if form.validate_on_submit(): # TODO: preprocess the search term? g.search_term = form.data['search_term'] results = Writing.query.\ filter(Writing.tsvector.op('@@')( func.plainto_tsquery(form.data['search_term']))).\ paginate(page) return render_template( 'search.html', paginated=results, search=SearchForm(placeholder=form.data['search_term'])) else: return render_template('search.html')
def query(q_object): results = FullTextIndex.query.filter(FullTextIndex.index_name == q_object['index_field']) if q_object['query'] != '': tq = func.plainto_tsquery('english', q_object['query']) results = results.filter(FullTextIndex.document.op('@@')(tq)) if q_object['active']: results = results.filter(FullTextIndex.status == 'Active') if q_object['start_date'] and q_object['end_date']: results = results.filter(FullTextIndex.dt_origin >= q_object['start_date']).filter(FullTextIndex.dt_origin <= q_object['end_date']) if q_object['business_type']: if q_object['business_type'] == ['All Entities']: pass else: results = results.filter(FullTextIndex.type.in_(q_object['business_type'])) if q_object['sort_order'] == 'desc': results = results.order_by(desc(q_object['sort_by'])) else: results = results.order_by(q_object['sort_by']) return results
def query(q_object): results = FullTextIndex.query.filter(FullTextIndex.index_name == q_object['index_field']) if q_object['index_field'] == 'place_of_business_city': str = FullTextIndex.city results = results.filter(str.startswith(q_object['query'].upper())) if q_object['query'] != '': tq = func.plainto_tsquery('english', q_object['query']) results = results.filter(FullTextIndex.document.op('@@')(tq)) if q_object['active'] == 'True': results = results.filter(FullTextIndex.status == 'Active') if q_object['start_date'] and q_object['end_date']: results = results.filter(FullTextIndex.dt_filing >= q_object['start_date']).filter(FullTextIndex.dt_filing <= q_object['end_date']) if q_object['business_type']: if q_object['business_type'] == ['All Entities']: pass else: results = results.filter(FullTextIndex.type.in_(q_object['business_type'])) if q_object['sort_order'] == 'desc': results = results.order_by(desc(q_object['sort_by'])) else: results = results.order_by(q_object['sort_by']) return results
def search_pkg(): term = request.args.get('term') if not term: flash('The search term was invalid.') return redirect(url_for('portal.index')) with session_scope() as session: q = session.query(BinaryPackage) \ .filter(BinaryPackage.__ts_vector__.op('@@')(func.plainto_tsquery(term))) \ .distinct(BinaryPackage.name, BinaryPackage.version) results_count = q.count() packages = q.all() results_per_page = results_count page_count = math.ceil(results_count / results_per_page) if results_per_page > 0 else 1 return render_template('pkg_search_results.html', term=term, results_count=results_count, results_per_page=results_per_page, page_count=page_count, packages=packages)
def search_text(): terms = request.args['q'] page = int(request.args.get('page', '1')) format = request.args.get('format', 'html') query = CadorsReport.query.filter( 'cadors_report.narrative_agg_idx_col @@ plainto_tsquery(:terms)') query = query.params(terms=terms) query = query.order_by( 'ts_rank_cd(narrative_agg_idx_col, plainto_tsquery(:terms)) DESC', CadorsReport.timestamp.desc()) if format == 'html': query = query.add_column( func.ts_headline('pg_catalog.english', CadorsReport.narrative_agg, func.plainto_tsquery(terms), '''MaxFragments=2, MinWords=15, MaxWords=20, FragmentDelimiter=|||, StartSel="<b>", StopSel = "</b>"''', type_=types.Unicode)) pagination = query.paginate(page) response = make_response( render_template('sr_text.html', reports=pagination.items, pagination=pagination, terms=terms)) return prepare_response(response, 300) else: pagination = query.paginate(page) title = "Results for '%s'" % (terms) return render_list(pagination, title, format)
def dispatch_request(self): form = ObjectListFilter(request.args) form.platform.query = self.query(Platform) form.session.query = self.query(Session) form.object_link.platform.query = self.query(Platform) query = ( self.query(ExternalObject) .options(undefer(ExternalObject.links_count)) .order_by(ExternalObject.id) ) # Join the needed columns for filtering if ( form.platform.data or (form.object_link.platform.data and form.object_link.external_id.data) or form.session.data or form.scrap.data or form.import_file.data ): query = query.join(ExternalObject.links) if form.session.data or form.scrap.data: query = query.outerjoin(ObjectLink.scraps) if form.session.data or form.import_file.data: query = query.outerjoin(ObjectLink.imports) if form.search.data or form.country.data: query = query.join(ExternalObject.attributes).options( contains_eager(ExternalObject.attributes) ) else: # If we are not filtering using the attributes we need to join-load them query = query.options(joinedload(ExternalObject.attributes)) # Apply the filters if form.search.data: q = func.plainto_tsquery(form.search.data) query = query.filter(AttributesView.search_vector.op("@@")(q)).order_by( func.ts_rank(AttributesView.search_vector, q) ) else: query = query.order_by(ExternalObject.id) if form.country.data: query = query.filter( AttributesView.countries[1].in_( c.strip() for c in form.country.data.upper().split(",") ) ) if form.type.data: query = query.filter(ExternalObject.type.in_(form.type.data)) else: # FIXME: ignore PERSONs by default for now query = query.filter(ExternalObject.type != ExternalObjectType.PERSON) if form.platform.data: query = query.filter( ObjectLink.platform_id.in_( platform.id for platform in form.platform.data ) ) if form.scrap.data: query = query.filter(Scrap.id == form.scrap.data) if form.import_file.data: query = query.filter(ImportFile.id == form.import_file.data) if form.session.data: session_ids = [s.id for s in form.session.data] scrap_sessions = aliased(Session) file_sessions = aliased(Session) query = ( query.outerjoin(scrap_sessions, Scrap.sessions) .outerjoin(file_sessions, ImportFile.sessions) .filter( or_( scrap_sessions.id.in_(session_ids), file_sessions.id.in_(session_ids), ) ) ) if form.object_link.platform.data and form.object_link.external_id.data: query = query.filter( ObjectLink.platform == form.object_link.platform.data, ObjectLink.external_id.contains(form.object_link.external_id.data), ) ctx = {} ctx["page"] = query.paginate() ctx["filter_form"] = form return render_template("objects/list.html", **ctx)
def search(cls, session, terms): return session.query(cls).filter( cls.search_vector.op('@@')(func.plainto_tsquery(terms)))
def __eq__(self, other): print func.plainto_tsquery(other) return self.op("@@")(func.plainto_tsquery("russian", other))
def search(self, qs): # sanitize, normalize, and & (AND) the resulting stems # see also https://bitbucket.org/zzzeek/sqlalchemy/issues/3160/postgresql-to_tsquery-docs-and query = func.plainto_tsquery('english', qs) return self.model_col.op('@@')(query)
def search(self, query: str) -> "TopicQuery": """Restrict the topics to ones that match a search query (generative).""" return self.filter( Topic.search_tsv.op("@@")(func.plainto_tsquery(query)))
def search(self): with SessionLocal() as session: query = select(self.model) query = query.where( self.field.op("@@")(func.plainto_tsquery(self.terms))) return session.execute(query).scalars().all()
def filter(self): """ Return token filter for use in SQLAlchemy query """ # print 'filter for token:', self, self.prefix if self.prefix == 'word:': tsquery = db.session.execute(sqlalchemy.sql.select([func.plainto_tsquery('english', self.value)])).fetchone()[0] if tsquery != '': where_story = sqlalchemy.and_(Item.kind == 'story', Item.title_tsv.match(tsquery)) where_comment = sqlalchemy.and_(Item.kind == 'comment', Item.body_tsv.match(tsquery)) where = sqlalchemy.or_(where_story, where_comment) else: # print 'empty word: %s' % self.value return None elif self.prefix == 'author:': if self.value != '': # print 'ok author' where = (func.lower(Item.author) == self.value.lower()) else: # print 'empty author: %s' % self.value return None elif self.prefix == 'host:': if self.value != '': # print 'ok host' if self.value[:4] == 'www.': host = self.value[4:] else: host = self.value where = (Item.domain == host.lower()) else: # print 'empty host: %s' % self.value return None elif self.prefix == 'type:': value = self.value.lower() if value == 'story': where = (Item.kind == value) elif value in ('comment', 'link', 'job', 'poll', 'ask', 'show'): where = (Item.subkind == value) else: return None # <<< TODO Not sure if this operator should exist. # elif self.prefix == 'front:': # if self.value == 'yes': # where = (Item.date_entered_fp != None) # SQLAlchemy == operator (not "is") # elif self.value == 'no': # where = (Item.date_entered_fp == None) # SQLAlchemy == operator (not "is") # else: # return None elif self.prefix == 'score>': if self.value.isdigit(): where = (Item.score > int(self.value.lstrip('0'))) else: return None elif self.prefix == 'score<': if self.value.isdigit(): where = (Item.score < int(self.value.lstrip('0'))) else: return None elif self.prefix == 'comments>': if self.value.isdigit(): where = (Item.num_comments > int(self.value.lstrip('0'))) else: return None elif self.prefix == 'comments<': if self.value.isdigit(): where = (Item.num_comments < int(self.value.lstrip('0'))) else: return None else: raise AppError(u'Token prefix not found: %s' % self.prefix) if self.negate: return sqlalchemy.not_(where) else: return where
'le': lambda f, a: f <= a, 'lte': lambda f, a: f <= a, 'leq': lambda f, a: f <= a, '<<': lambda f, a: f.op('<<')(a), '<<=': lambda f, a: f.op('<<=')(a), '>>': lambda f, a: f.op('>>')(a), '>>=': lambda f, a: f.op('>>=')(a), '<>': lambda f, a: f.op('<>')(a), '&&': lambda f, a: f.op('&&')(a), 'ilike': lambda f, a: f.ilike(a), 'like': lambda f, a: f.like(a), 'not_like': lambda f, a: ~f.like(a), 'in': lambda f, a: f.in_(a), 'not_in': lambda f, a: ~f.in_(a), 'to_tsquery': lambda f, a: f.match(a), 'plainto_tsquery': lambda f, a: f.op('@@')(func.plainto_tsquery(a)), # Operators which accept three arguments. 'has': lambda f, a, fn: f.has(_sub_operator(f, a, fn)), 'any': lambda f, a, fn: f.any(_sub_operator(f, a, fn)), } class Filter(object): """Represents a filter to apply to a SQLAlchemy query object. A filter can be, for example, a comparison operator applied to a field of a model and a value or a comparison applied to two fields of the same model. For more information on possible filters, see :ref:`search`. `fieldname` is the name of the field of a model which will be on the left side of the operator.
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 get_alias_infos_with_pagination_v3( user, page_id=0, query=None, sort=None, alias_filter=None ) -> [AliasInfo]: # subquery on alias annotated with nb_reply, nb_blocked, nb_forward, max_created_at, latest_email_log_created_at alias_activity_subquery = ( db.session.query( Alias.id, func.sum(case([(EmailLog.is_reply, 1)], else_=0)).label("nb_reply"), func.sum( case( [(and_(EmailLog.is_reply.is_(False), EmailLog.blocked), 1)], else_=0, ) ).label("nb_blocked"), func.sum( case( [ ( and_( EmailLog.is_reply.is_(False), EmailLog.blocked.is_(False), ), 1, ) ], else_=0, ) ).label("nb_forward"), func.max(EmailLog.created_at).label("latest_email_log_created_at"), ) .join(EmailLog, Alias.id == EmailLog.alias_id, isouter=True) .filter(Alias.user_id == user.id) .group_by(Alias.id) .subquery() ) alias_contact_subquery = ( db.session.query(Alias.id, func.max(Contact.id).label("max_contact_id")) .join(Contact, Alias.id == Contact.alias_id, isouter=True) .filter(Alias.user_id == user.id) .group_by(Alias.id) .subquery() ) latest_activity = case( [ (Alias.created_at > EmailLog.created_at, Alias.created_at), (Alias.created_at < EmailLog.created_at, EmailLog.created_at), ], else_=Alias.created_at, ) q = ( db.session.query( Alias, Contact, EmailLog, CustomDomain, alias_activity_subquery.c.nb_reply, alias_activity_subquery.c.nb_blocked, alias_activity_subquery.c.nb_forward, ) .options(joinedload(Alias.hibp_breaches)) .join(Contact, Alias.id == Contact.alias_id, isouter=True) .join(CustomDomain, Alias.custom_domain_id == CustomDomain.id, isouter=True) .join(EmailLog, Contact.id == EmailLog.contact_id, isouter=True) .filter(Alias.id == alias_activity_subquery.c.id) .filter(Alias.id == alias_contact_subquery.c.id) .filter( or_( EmailLog.created_at == alias_activity_subquery.c.latest_email_log_created_at, and_( # no email log yet for this alias alias_activity_subquery.c.latest_email_log_created_at.is_(None), # to make sure only 1 contact is returned in this case or_( Contact.id == alias_contact_subquery.c.max_contact_id, alias_contact_subquery.c.max_contact_id.is_(None), ), ), ) ) ) if query: q = ( # to find mailbox whose email match the query q.join(AliasMailbox, Alias.id == AliasMailbox.alias_id, isouter=True) .join( Mailbox, or_( Mailbox.id == Alias.mailbox_id, Mailbox.id == AliasMailbox.mailbox_id, ), ) .filter( or_( Alias.email.ilike(f"%{query}%"), Alias.note.ilike(f"%{query}%"), # can't use match() here as it uses to_tsquery that expected a tsquery input # Alias.ts_vector.match(query), Alias.ts_vector.op("@@")(func.plainto_tsquery("english", query)), Alias.name.ilike(f"%{query}%"), Mailbox.email.ilike(f"%{query}%"), ) ) ) if alias_filter == "enabled": q = q.filter(Alias.enabled) elif alias_filter == "disabled": q = q.filter(Alias.enabled.is_(False)) q = q.order_by(Alias.pinned.desc()) if sort == "old2new": q = q.order_by(Alias.created_at) elif sort == "new2old": q = q.order_by(Alias.created_at.desc()) elif sort == "a2z": q = q.order_by(Alias.email) elif sort == "z2a": q = q.order_by(Alias.email.desc()) elif alias_filter == "hibp": q = q.filter(Alias.hibp_breaches.any()) else: # default sorting q = q.order_by(latest_activity.desc()) q = list(q.limit(PAGE_LIMIT).offset(page_id * PAGE_LIMIT)) ret = [] for alias, contact, email_log, custom_domain, nb_reply, nb_blocked, nb_forward in q: ret.append( AliasInfo( alias=alias, mailbox=alias.mailbox, mailboxes=alias.mailboxes, nb_forward=nb_forward, nb_blocked=nb_blocked, nb_reply=nb_reply, latest_email_log=email_log, latest_contact=contact, custom_domain=custom_domain, ) ) return ret
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(term): # Parse it term = term.lower().replace("%20", " ") terms = term.split() queryAndArtist = session.query(Artist, func.ts_headline('english', Artist.name, func.plainto_tsquery(term)).label('h_name')) \ .filter(and_(Artist.tsvector_col.match(s) for s in terms)).all() queryOrArtist = session.query(Artist, func.ts_headline('english', Artist.name, func.plainto_tsquery(term)).label('h_name')) \ .filter(or_(Artist.tsvector_col.match(s) for s in terms)).all() queryAndSong = session.query(Song, func.ts_headline('english', Song.song_name, func.plainto_tsquery(term)).label('h_song_name'), \ func.ts_headline('english', Song.artist_name, \ func.plainto_tsquery(term)).label('h_artist_name'), \ func.ts_headline('english', Song.album_name, func.plainto_tsquery(term)).label('h_album_name')) \ .filter(and_(Song.tsvector_col.match(s) for s in terms)).all() queryOrSong = session.query(Song, \ func.ts_headline('english', Song.song_name, func.plainto_tsquery(term)).label('h_song_name'), \ func.ts_headline('english', Song.artist_name, \ func.plainto_tsquery(term)).label('h_artist_name'), \ func.ts_headline('english', Song.album_name, func.plainto_tsquery(term)).label('h_album_name')) \ .filter(or_(Song.tsvector_col.match(s) for s in terms)).all() queryAndYear = session.query(Year, \ func.ts_headline('english', Year.year, func.plainto_tsquery(term)).label('h_year'), \ func.ts_headline('english', Year.top_genre_name, func.plainto_tsquery(term)).label('h_top_genre_name'), \ func.ts_headline('english', Year.top_album_name, func.plainto_tsquery(term)).label('h_top_album_name')) \ .filter(and_(Year.tsvector_col.match(s) for s in terms)).all() queryOrYear = session.query(Year, \ func.ts_headline('english', Year.year, func.plainto_tsquery(term)).label('h_year'), \ func.ts_headline('english', Year.top_genre_name, func.plainto_tsquery(term)).label('h_top_genre_name'), \ func.ts_headline('english', Year.top_album_name, func.plainto_tsquery(term)).label('h_top_album_name')) \ .filter(or_(Year.tsvector_col.match(s) for s in terms)).all() queryAndGenre = session.query(Genre, \ func.ts_headline('english', Genre.name, func.plainto_tsquery(term)).label('h_name'), \ func.ts_headline('english', Genre.description, func.plainto_tsquery(term)).label('h_description')) \ .filter(and_(Genre.tsvector_col.match(s) for s in terms)).all() queryOrGenre = session.query(Genre, \ func.ts_headline('english', Genre.name, func.plainto_tsquery(term)).label('h_name'), \ func.ts_headline('english', Genre.description, func.plainto_tsquery(term)).label('h_description')) \ .filter(or_(Genre.tsvector_col.match(s) for s in terms)).all() return render_template('search.html', andArtist = queryAndArtist, orArtist = queryOrArtist, andSong = queryAndSong, orSong = queryOrSong, andYear = queryAndYear, orYear = queryOrYear, andGenre = queryAndGenre, orGenre = queryOrGenre, term=term)
def loadData(): res_dic = {} try: latitude = request.args.get("lat") longitude = request.args.get("lon") address = request.args.get("address") meta = request.args.get("meta") meta2 = request.args.get("meta2") radius = request.args.get("radius"); point = WKTElement('POINT({0} {1})'.format(latitude, longitude), srid=4326) #query = db.session.query(TastyPlace).filter(func.ST_DWithin(TastyPlace.point, point, float(radius)))query = db.session.query(TastyPlace).filter(db.TastyPlace.addr=address) query = db.session.query(TastyPlace).filter(TastyPlace.search_address_vector.op('@@')(func.plainto_tsquery(address))) restaurant_cnt = query.count() print (query) if restaurant_cnt == 0 : print ("No Data") html = mod.get_html_soup(DININGCODEURL + "query=" +address + meta) cnt = mod.get_rest_cnt(html) page = cnt / 10 + 1 if page > 10: page = 10 for i in range(1,page+1) : url = DININGCODEURL + 'page='+str(i)+'&chunk=10&query='+address + meta soup = mod.get_html_soup(url) insert_res_info(soup) res_dic = get_res_info(point,radius,meta,meta2) else: print ("Exist Data") res_dic = get_res_info(point,radius,meta,meta2) return jsonify(**res_dic) except ValueError: return jsonify(**res_dic) except Exception as e: print (e) return jsonify(**res_dic)
def bind_expression(self, bindvalue): if bindvalue is None: bindvalue = '' return func.plainto_tsquery('ru', bindvalue)
def search(term): # Parse it term = term.lower().replace("%20", " ") terms = term.split() queryAndArtist = session.query(Artist, func.ts_headline('english', Artist.name, func.plainto_tsquery(term)).label('h_name')) \ .filter(and_(Artist.tsvector_col.match(s) for s in terms)).all() queryOrArtist = session.query(Artist, func.ts_headline('english', Artist.name, func.plainto_tsquery(term)).label('h_name')) \ .filter(or_(Artist.tsvector_col.match(s) for s in terms)).all() queryAndSong = session.query(Song, func.ts_headline('english', Song.song_name, func.plainto_tsquery(term)).label('h_song_name'), \ func.ts_headline('english', Song.artist_name, \ func.plainto_tsquery(term)).label('h_artist_name'), \ func.ts_headline('english', Song.album_name, func.plainto_tsquery(term)).label('h_album_name')) \ .filter(and_(Song.tsvector_col.match(s) for s in terms)).all() queryOrSong = session.query(Song, \ func.ts_headline('english', Song.song_name, func.plainto_tsquery(term)).label('h_song_name'), \ func.ts_headline('english', Song.artist_name, \ func.plainto_tsquery(term)).label('h_artist_name'), \ func.ts_headline('english', Song.album_name, func.plainto_tsquery(term)).label('h_album_name')) \ .filter(or_(Song.tsvector_col.match(s) for s in terms)).all() queryAndYear = session.query(Year, \ func.ts_headline('english', Year.year, func.plainto_tsquery(term)).label('h_year'), \ func.ts_headline('english', Year.top_genre_name, func.plainto_tsquery(term)).label('h_top_genre_name'), \ func.ts_headline('english', Year.top_album_name, func.plainto_tsquery(term)).label('h_top_album_name')) \ .filter(and_(Year.tsvector_col.match(s) for s in terms)).all() queryOrYear = session.query(Year, \ func.ts_headline('english', Year.year, func.plainto_tsquery(term)).label('h_year'), \ func.ts_headline('english', Year.top_genre_name, func.plainto_tsquery(term)).label('h_top_genre_name'), \ func.ts_headline('english', Year.top_album_name, func.plainto_tsquery(term)).label('h_top_album_name')) \ .filter(or_(Year.tsvector_col.match(s) for s in terms)).all() queryAndGenre = session.query(Genre, \ func.ts_headline('english', Genre.name, func.plainto_tsquery(term)).label('h_name'), \ func.ts_headline('english', Genre.description, func.plainto_tsquery(term)).label('h_description')) \ .filter(and_(Genre.tsvector_col.match(s) for s in terms)).all() queryOrGenre = session.query(Genre, \ func.ts_headline('english', Genre.name, func.plainto_tsquery(term)).label('h_name'), \ func.ts_headline('english', Genre.description, func.plainto_tsquery(term)).label('h_description')) \ .filter(or_(Genre.tsvector_col.match(s) for s in terms)).all() return render_template('search.html', andArtist=queryAndArtist, orArtist=queryOrArtist, andSong=queryAndSong, orSong=queryOrSong, andYear=queryAndYear, orYear=queryOrYear, andGenre=queryAndGenre, orGenre=queryOrGenre, term=term)
def search(col, qs): # pragma: no cover # https://bitbucket.org/zzzeek/sqlalchemy/issues/3160/postgresql-to_tsquery-docs-and query = func.plainto_tsquery('english', qs) return col.op('@@')(query)
def get_alias_infos_with_pagination_v3( user, page_id=0, query=None, sort=None, alias_filter=None, mailbox_id=None, directory_id=None, page_limit=PAGE_LIMIT, page_size=PAGE_LIMIT, ) -> [AliasInfo]: q = construct_alias_query(user) if query: q = q.filter( or_( Alias.email.ilike(f"%{query}%"), Alias.note.ilike(f"%{query}%"), # can't use match() here as it uses to_tsquery that expected a tsquery input # Alias.ts_vector.match(query), Alias.ts_vector.op("@@")(func.plainto_tsquery("english", query)), Alias.name.ilike(f"%{query}%"), ) ) if mailbox_id: q = q.join( AliasMailbox, Alias.id == AliasMailbox.alias_id, isouter=True ).filter( or_(Alias.mailbox_id == mailbox_id, AliasMailbox.mailbox_id == mailbox_id) ) if directory_id: q = q.filter(Alias.directory_id == directory_id) if alias_filter == "enabled": q = q.filter(Alias.enabled) elif alias_filter == "disabled": q = q.filter(Alias.enabled.is_(False)) elif alias_filter == "pinned": q = q.filter(Alias.pinned) elif alias_filter == "hibp": q = q.filter(Alias.hibp_breaches.any()) if sort == "old2new": q = q.order_by(Alias.created_at) elif sort == "new2old": q = q.order_by(Alias.created_at.desc()) elif sort == "a2z": q = q.order_by(Alias.email) elif sort == "z2a": q = q.order_by(Alias.email.desc()) else: # default sorting latest_activity = case( [ (Alias.created_at > EmailLog.created_at, Alias.created_at), (Alias.created_at < EmailLog.created_at, EmailLog.created_at), ], else_=Alias.created_at, ) q = q.order_by(Alias.pinned.desc()) q = q.order_by(latest_activity.desc()) q = list(q.limit(page_limit).offset(page_id * page_size)) ret = [] for alias, contact, email_log, nb_reply, nb_blocked, nb_forward in q: ret.append( AliasInfo( alias=alias, mailbox=alias.mailbox, mailboxes=alias.mailboxes, nb_forward=nb_forward, nb_blocked=nb_blocked, nb_reply=nb_reply, latest_email_log=email_log, latest_contact=contact, custom_domain=alias.custom_domain, ) ) return ret
def build_facts_query(self, db: Session, *, user: models.User, filters: schemas.FactSearch = schemas.FactSearch()): visible_decks = (db.query(models.Deck.id).join( models.User_Deck).filter( models.User_Deck.owner_id == user.id).subquery()) user_facts = (db.query(models.Fact).join( visible_decks, models.Fact.deck_id == visible_decks.c.id).filter( models.Fact.user_id == user.id)) deck_owners = (db.query( models.User_Deck.deck_id, models.User_Deck.owner_id).outerjoin(visible_decks).filter( models.User_Deck.permissions == schemas.Permission.owner).subquery()) filtered_facts = (db.query(models.Fact).join( visible_decks, models.Fact.deck_id == visible_decks.c.id).join( deck_owners, and_(models.Fact.deck_id == deck_owners.c.deck_id, models.Fact.user_id == deck_owners.c.owner_id))) facts_query = (user_facts.union(filtered_facts)) # Don't allow Jeopardy facts facts_query = facts_query.filter(models.Fact.deck_id != 2) if filters.studyable: facts_query = (facts_query.outerjoin( models.Deleted, and_(models.Fact.fact_id == models.Deleted.fact_id, models.Deleted.user_id == user.id) ).filter(models.Deleted.user_id == None).outerjoin( models.Reported, and_(models.Fact.fact_id == models.Reported.fact_id, models.Reported.user_id == user.id)).filter( models.Reported.user_id == None).outerjoin( models.Suspended, and_( models.Fact.fact_id == models.Suspended.fact_id, models.Suspended.user_id == user.id)).filter( models.Suspended.user_id == None)) else: facts_query = (facts_query.outerjoin( models.Deleted, and_(models.Fact.fact_id == models.Deleted.fact_id, models.Deleted.user_id == user.id)).filter( models.Deleted.user_id == None)) if filters.suspended is not None: if filters.suspended: facts_query = facts_query.join(models.Suspended).filter( models.Suspended.user_id == user.id) else: facts_query = (facts_query.outerjoin( models.Suspended, and_(models.Fact.fact_id == models.Suspended.fact_id, models.Suspended.user_id == user.id)).filter( models.Suspended.user_id == None)) if filters.reported is not None: if filters.reported: facts_query = facts_query.join(models.Reported) if not user.is_superuser: facts_query = facts_query.filter( models.Reported.user_id == user.id) else: facts_query = (facts_query.outerjoin( models.Reported, and_(models.Fact.fact_id == models.Reported.fact_id, models.Reported.user_id == user.id)).filter( models.Reported.user_id == None)) if filters.all: facts_query = facts_query.filter( models.Fact.__ts_vector__.op('@@')(func.plainto_tsquery( 'english', filters.all))) if filters.text: facts_query = facts_query.filter( models.Fact.text.ilike(filters.text)) if filters.answer: facts_query = facts_query.filter( models.Fact.answer.ilike(filters.answer)) if filters.category: facts_query = facts_query.filter( models.Fact.category.ilike(filters.category)) if filters.identifier: facts_query = facts_query.filter( models.Fact.identifier.ilike(filters.identifier)) if filters.deck_ids: facts_query = facts_query.filter( models.Fact.deck_id.in_(filters.deck_ids)) if filters.deck_id: facts_query = facts_query.filter( models.Fact.deck_id == filters.deck_id) if filters.marked is not None: if filters.marked: facts_query = facts_query.filter( models.Fact.markers.any(id=user.id)) else: facts_query = facts_query.filter( not_(models.Fact.markers.any(id=user.id))) if filters.randomize: facts_query = facts_query.order_by(func.random()) return facts_query
from application.tracker.remote import RemotePetition from application.tracker.geographies.choices.regions import REGIONS from application.tracker.geographies.choices.countries import COUNTRIES from application.tracker.geographies.choices.constituencies import CONSTITUENCIES from application.tracker.exceptions import PetitionsNotFound, RecordsNotFound from math import ceil, floor from datetime import datetime as dt from datetime import timedelta from copy import deepcopy from collections.abc import Iterable import operator as py_operator import datetime, json, sys, logging get_operator = lambda opr: getattr(py_operator, opr) plainto_tsquery = lambda qs: sqlfuncgen.plainto_tsquery("english", qs) def match_text(_cls, column, value): column = getattr(_cls, column) text_query = plainto_tsquery(value) return column.op("@@")(text_query) logger = logging.getLogger(__name__) class Petition(db.Model): __tablename__ = "petition" STATE_CHOICES = [ ("C", "closed"),