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"),
