def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
custom_print(
cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total)
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def dashboard(sort='top', topic_name=None):
days = {'today': 0, 'week': 7, 'month': 30, 'year': 365}
topic = None
sidebar_active = sort
user_id = current_user.id
page = int(request.args.get('page', 1))
target = request.args.get('target', 1)
timeframe = request.args.get('timeframe')
topics = current_user.following_topics
terms = db.and_(Voter.target_id == Issue.id, Voter.user_id == user_id)
case = db.case([(Voter.user_id == user_id, True)], else_=False)
query = db.session.query(Issue, case.label('has_voted'))
if topic_name:
if user_id > 0:
for topic in current_user.following_topics:
if topic.name == topic_name:
break
else:
topic = None
else:
topic = Topic.query.filter_by(name=topic_name).first()
if topic:
sidebar_active = None
query = query.filter(Issue.topics.any(Topic.name == topic_name))
elif len(current_user.following_topics) > 0:
topic_terms = []
for t in current_user.following_topics:
topic_terms.append(Issue.topics.any(Topic.name == t.name))
query = query.filter(db.or_(*topic_terms))
if sort == 'top':
now = datetime.now()
time = datetime(now.year, now.month, now.day)
if days.get(timeframe) is None:
timeframe = 'year'
time -= timedelta(days=days[timeframe])
query = query.filter(Issue.created_at > time)
query = query.order_by(Issue.score.desc(), Issue.created_at.desc())
else:
query = query.order_by(Issue.created_at.desc())
query = query.outerjoin(Voter, terms)
feeds = query.paginate(page, 15)
ctx = {
'feeds': feeds,
'topics': topics,
'timeframe': timeframe,
'sidebar_active': sidebar_active,
'navbar_active': '',
'topic_name': topic_name,
'topic': topic
}
if target == '#feeds-container':
return render_template('components/_feeds.html', **ctx)
return render_template('dashboard/index.html', **ctx)
def search(cls, expression, page, per_page):
"""
Class method to execute an Elasticsearch search of the input "expression"
for the associated input "cls" (with input options values "page" and "per_page")
:param cls: a class
:type cls: class
:param expression: the searched text
:type expression: str
:param page: the page number from the query results
:type page: int
:param per_page: the number of results per page from the query results
:type per_page: int
:return: ...
:rtype: tuple(, int)
"""
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for list_index, list_value in enumerate(ids):
when.append((list_value, list_index))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
# return empty
return cls.query.filter_by(id=0), 0
"""
CASE x_field
WHEN 'f' THEN 1
WHEN 'p' THEN 2
WHEN 'i' THEN 3
WHEN 'a' THEN 4
ELSE 5 --needed only is no IN clause above. eg when = 'b'
END, id
case-when-value statements below is equivalent to:
supose when[0]=(1000, 1), value=cls.id
WHEN cls.id==1000 THEN 1
ensure the return of sql query are the same order in ids.
that is, the result are sorted form the most relevant (same as ES's return)
"""
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def stations2_filtered_pl(start, end):
last_10_minutes = datetime.utcnow() - timedelta(minutes=10)
query = (db.session.query(
Receiver.name.label("s"),
db.label(
"lt",
db.func.round(db.func.ST_Y(Receiver.location_wkt) * 10000) /
10000),
db.label(
"lg",
db.func.round(db.func.ST_X(Receiver.location_wkt) * 10000) /
10000),
db.case([(Receiver.lastseen > last_10_minutes, "U")],
else_="D").label("u"),
Receiver.lastseen.label("ut"),
db.label("v", Receiver.version + "." + Receiver.platform),
).order_by(Receiver.lastseen).filter(
db.or_(db.and_(start < Receiver.firstseen, end > Receiver.firstseen),
db.and_(start < Receiver.lastseen, end > Receiver.lastseen))))
res = db.session.execute(query)
stations = json.dumps({"stations": [dict(r) for r in res]},
default=alchemyencoder)
return stations
def search(cls, expression, page, per_page):
"""
Actual search method for every class that class the search engine method
"""
if current_app.config['ELASTICSEARCH_URL']:
ids, total = query_index(cls.__tablename__, expression, page,
per_page)
else:
idss = db.session.\
execute(
"SELECT id FROM "+cls.__tablename__+" "
"WHERE "+cls.__searchable__[0]+" "
"LIKE '%"+expression+"%'; "
)
ids = list([i[0] for i in idss])
del idss
total = db.session.\
execute(
"SELECT COUNT(*) FROM "+cls.__tablename__+" "
"WHERE "+cls.__searchable__[0]+" "
"LIKE '%"+expression+"%'; "
).first()[0]
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = [(id, i) for i, id in enumerate(ids)]
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def has_active_event_promo(cls):
return db.select([
db.case([(db.func.count(EventPromotion.promotion_id) > 0, True)],
else_=False)
]).where(db.and_(EventPromotion.is_active == True,
EventPromotion.promotion_id == cls.promotion_id))\
.correlate(cls).as_scalar()
def configurations_all(self):
# конфигурации с признаком отслеживания пользователем
xpr = db.case([(followers.c.user_id != None, True), ], else_=False).label("active")
query = db.session.query(Configuration, xpr)\
.outerjoin(followers,
db.and_(Configuration.id == followers.c.configuration_id, self.id == followers.c.user_id))\
.filter(Configuration.active == True).order_by(Configuration.description)
return query.all()
def search(email_id):
email = query_index(cls.__tablename__, email)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.email_id.in_(ids)).order_by(
db.case(when, value=cls.email_id)), total
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(c, query):
ids = search.query(c.__tablename__, query)
if len(ids) == 0:
return c.query.filter_by(id=0)
order = []
for i in range(len(ids)):
order.append((ids[i], i))
return c.query.filter(c.id.in_(ids)).order_by(
db.case(order, value=c.id))
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = [] # use in sql order_by to keep search relevancy order
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page): # cls.search, not self.search
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = [] # this list must be called "when"
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def query_search(cls, expression):
ids = query_index(cls.__tablename__, expression)
if len(ids) == 0:
return cls.query.filter_by(id=0)
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id))
def search(cls, expression):
ids, total = query_index(cls.__tablename__, expression)
if total == 0:
return cls.query.filter_by(bug_id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.bug_id.in_(ids)).order_by(
db.case(when, value=cls.bug_id)), total
def search(cls, expression, page, per_page):
# calls query_index() with cls.__tablename__ to use SQLAlchemy table names as index names in Elastic
ids, total = query_index(cls.__tablename__, expression, page, per_page) #cls used instead of self because this method recieves a class, not an instance of it's first arg.
if total == 0: # for instance, once Post class is attached to search() as Post.search() we don't need to have an instance of the Post class
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for counter, value in enumerate(ids):
when.append((value, counter))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
# 将self重命名为cls旨在表明此方法接受的是一个类而不是实例
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
"""返回替换ID列表的查询结果集，以及搜索结果的总数"""
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
# case sql - results from the database come in the same order as the IDs are given
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, qstring):
ids, total = query_index(qstring)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
# Wrapper for the query_index() function from app/search.py. It replaces id's by actual objects.
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
""" Replaces the list of object IDs with actual objects. """
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page, per_page):
ids, total = query_index(cls.__tablename__, expression, page, per_page)
# all indexes will be named with name Flask-SQLAlchemy assigned to the relational table.
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression, page,
per_page): # first argument is a class, for example Post model
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = [
] # list of tuples, where tuples are pairs of id index and id value at that index
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def index():
group, groups = [], []
user_id = current_user.id if current_user.is_authenticated else 0
terms = db.and_(UserTopics.c.user_id==user_id, UserTopics.c.topic_id==Topic.id)
case = db.case([(UserTopics.c.user_id==user_id, True)], else_=False)
query = db.session.query(Topic, case.label('has_following'))
query = query.outerjoin(UserTopics, terms)
topics = query.order_by(
Topic.issues_count.desc(),
Topic.followers_count.desc()
).limit(36).all()
for t in topics:
group.append(t)
if len(group) >= 3:
groups.append(group)
group = []
if len(group) > 0:
groups.append(group)
return render_template('topics/index.html',
groups=groups, navbar_active='topics')
def search(cls, expression, page, per_page):
""""""
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
# case 该语句需要用于确保数据库中的结果与给定ID的顺序相同
return cls.query.filter(cls.id.in_(ids)).order_by(
db.case(when, value=cls.id)), total
def search(cls, expression: str, page: int,
per_page: int) -> Tuple[List[Tuple[int]], int]:
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return (cls.query.filter_by(id=0), 0)
when = []
for i in range(len(ids)):
when.append((ids[i], i))
query_set: List[Tuple[int]] = cls.query.filter(
cls.id.in_(ids)).order_by(db.case(when, value=cls.id))
return (query_set, total)
def search(cls, expression, page, per_page):
try:
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(db.case(when, value=cls.id)), total
except Exception as error:
flash(f'Search server not running.')
return [], 0
def search(cls, expression, page, per_page):
# 返回结果ID列表和总数
ids, total = query_index(cls.__tablename__, expression, page, per_page)
if total == 0:
return cls.query.filter_by(id=0), 0
when = []
for i in range(len(ids)):
when.append((ids[i], i))
return cls.query.filter(cls.id.in_(ids)).order_by(
# 使用case语句，确保数据库中的结果与给定ID的顺序相同
# 因为使用elasticsearch搜索返回的结果不是有序的
db.case(when, value=cls.id)), total