def get_reactions_by_molecule(molecule, product=None):
if product is None:
q = left_join(
rs.reaction for m in Molecules if m.fear == fear.get_cgr_string(molecule) for rs in m.reactions)
else:
q = left_join(rs.reaction for m in Molecules if m.fear == fear.get_cgr_string(molecule) for rs in m.reactions if
rs.product == product)
return list(q)
def get(self, task):
"""
Task with modeling results of structures with conditions
all structures include only models with nonempty results lists.
see /task/model get doc.
"""
try:
task = int(task)
except ValueError:
abort(404, message='invalid task id. Use int Luke')
page = results_fetch.parse_args().get('page')
with db_session:
result = Task.get(id=task)
if not result:
abort(404, message='Invalid task id. Perhaps this task has already been removed')
if result.user.id != current_user.id:
abort(403, message='User access deny. You do not have permission to this task')
models = get_models_list(skip_destinations=True)
for v in models.values():
v['type'] = v['type'].value
additives = get_additives()
s = select(s for s in Structure if s.task == result).order_by(Structure.id)
if page:
s = s.page(page, pagesize=BLOG_POSTS_PER_PAGE)
structures = {x.id: dict(structure=x.id, data=x.structure, temperature=x.temperature, pressure=x.pressure,
type=x.structure_type, status=x.structure_status, additives=[], models=[])
for x in s}
r = left_join((s.id, r.model.id, r.key, r.value, r.result_type)
for s in Structure for r in s.results if s.id in structures.keys() and r is not None)
a = left_join((s.id, a.additive.id, a.amount)
for s in Structure for a in s.additives if s.id in structures.keys() and a is not None)
for s, a, aa in a:
tmp = dict(amount=aa)
tmp.update(additives[a])
structures[s]['additives'].append(tmp)
tmp_models = defaultdict(dict)
for s, m, rk, rv, rt in r:
tmp_models[s].setdefault(m, []).append(dict(key=rk, value=rv, type=rt))
for s, mr in tmp_models.items():
for m, r in mr.items():
tmp = dict(results=r)
tmp.update(models[m])
structures[s]['models'].append(tmp)
return dict(task=task, status=TaskStatus.DONE.value, date=result.date.strftime("%Y-%m-%d %H:%M:%S"),
type=result.task_type, user=result.user.id, structures=list(structures.values())), 200
def find_reactions_by_molecule(cls, structure, product=None):
if product is None:
q = left_join(r for m in Molecules
if m.fear == cls.get_fear(structure)
for rs in m.reactions for r in rs.reactions)
else:
q = left_join(r for m in Molecules
if m.fear == cls.get_fear(structure)
for rs in m.reactions if rs.product == product
for r in rs.reactions)
return list(q)
def _get_reactions(cls,
molecule,
number,
page,
product=None,
set_all=False):
q = left_join(x.reaction for x in cls._database_.MoleculeReaction
if x.molecule == molecule).order_by(lambda x: x.id)
if product is not None:
q = q.where(lambda x: x.is_product == product)
if number > 0:
reactions = q.page(page, number)
if not reactions:
raise StopIteration
if set_all:
cls._database_.Reaction.load_structures_combinations(reactions)
else:
cls._database_.Reaction.load_structures(reactions)
yield from reactions
else:
while True: # emulate buffered stream
reactions = q.page(page, 100)
if not reactions:
raise StopIteration
page += 1
if set_all:
cls._database_.Reaction.load_structures_combinations(
reactions)
else:
cls._database_.Reaction.load_structures(reactions)
yield from reactions
def participants(event):
m = Meeting.get(id=event, post_type=MeetingPostType.MEETING.value)
if not m:
return redirect(url_for('.blog'))
subs = Subscription.select(lambda x: x.meeting == m).order_by(
Subscription.id.desc())
users = {
x.id: x
for x in left_join(
x for x in User for s in x.subscriptions if s.meeting == m)
}
data = [
dict(type=x.type.fancy,
status=ProfileStatus(users[x.user.id].status).fancy,
country=countries.get(alpha_3=users[x.user.id].country).name,
user=users[x.user.id].full_name,
useid=x.user.id) for x in subs
]
return render_template('participants.html',
data=data,
title=m.title,
subtitle='Participants',
crumb=dict(url=url_for('.blog_post', post=event),
title='Participants',
parent='Event main page'))
def update_articles(self):
data = self.__get_articles(self.scopus_id)
if not data:
return False
articles = left_join(aa.article for aa in ArticleAuthor
if aa.author == self)
self.__update_or_create_article(data, articles)
return True
async def get_results(self, request):
"""Получение результатов голосования (список диктов)"""
with db_session:
result = left_join(
(o.name, count(r))
for o in OptionModel
for r in o.vote
)[:]
return web.json_response(result)
def get_reactions_by_molecules(molecule, product=None, reagent=None):
d = dict()
if product is not None:
for i in product:
d[fear.get_cgr_string(molecule)] = set()
for m, r in left_join(
(m.fear, r) for m in Molecules if m.fear in [fear.get_cgr_string(x) for x in product] for rs in
m.reactions if rs.product for r in rs.reactions):
d[m].add(r)
if reagent is not None:
for i in reagent:
d[fear.get_cgr_string(molecule)] = set()
for m, r in left_join(
(m.fear, r) for m in Molecules if m.fear in [fear.get_cgr_string(x) for x in reagent] for rs in
m.reactions if not rs.product for r in rs.reactions):
d[m].add(r)
return reduce(set.intersection, d.values())
def search_keyword(self, query, lim=100):
# Requires FTS5 table "FtsIndex" to be generated and populated.
# FTS table is maintained automatically by SQL triggers.
# BM25 ranking is embedded in FTS5.
# Sanitize FTS query
if not query or query == "*":
return []
fts_ids = raw_sql(
"""SELECT rowid FROM ChannelNode WHERE rowid IN (SELECT rowid FROM FtsIndex WHERE FtsIndex MATCH $query
ORDER BY bm25(FtsIndex) LIMIT $lim) GROUP BY coalesce(infohash, rowid)"""
)
return left_join(g for g in self.MetadataNode if g.rowid in fts_ids) # pylint: disable=E1135
def search_keyword(cls, query, lim=100):
# Requires FTS5 table "FtsIndex" to be generated and populated.
# FTS table is maintained automatically by SQL triggers.
# BM25 ranking is embedded in FTS5.
# Sanitize FTS query
if not query or query == "*":
return []
# TODO: optimize this query by removing unnecessary select nests (including Pony-manages selects)
fts_ids = raw_sql(
"""SELECT rowid FROM ChannelNode WHERE rowid IN (SELECT rowid FROM FtsIndex WHERE FtsIndex MATCH $query
ORDER BY bm25(FtsIndex) LIMIT $lim) GROUP BY coalesce(infohash, rowid)"""
)
return left_join(g for g in cls if g.rowid in fts_ids) # pylint: disable=E1135
def update_statistics(self):
tmp = sorted(
left_join(aa.article.cited for aa in ArticleAuthor
if aa.author == self
and aa.article.cited > 0).without_distinct(),
reverse=True)
h = 0
for i in tmp:
if i > h:
h += 1
if self.h_index != h:
self.h_index = h
counted = sum(tmp)
if self.citations != counted:
self.citations = counted
self.update = datetime.utcnow()
def _preload_reactions_structures(self):
# NEED PR
# select(y for x in db.MoleculeReaction if x.molecule == self for y in db.MoleculeReaction if
# x.reaction == y.reaction).order_by(lambda x: x.id)
reactions = {}
for r in left_join(x.reaction for x in db.MoleculeReaction if x.molecule == self):
reactions[r] = {True: [], False: []}
molecules = set()
for mr in select(x for x in db.MoleculeReaction if x.reaction in reactions.keys()).order_by(lambda x: x.id):
reactions[mr.reaction][mr.is_product].append(mr)
molecules.add(mr.molecule.id)
molecules_structures = defaultdict(list)
for ms in select(x for x in db.MoleculeStructure if x.molecule.id in molecules):
molecules_structures[ms.molecule.id].append(ms)
return {k: v[False] + v[True] for k, v in reactions.items()}, dict(molecules_structures), \
{k: len(v[False]) for k, v in reactions.items()}
def reactions_entities(self, page=1, pagesize=100, product=None):
"""
list of reactions entities including this molecule. chunks-separated for memory saving
:param page: slice of reactions
:param pagesize: maximum number of reactions in list
:param product: if True - reactions including this molecule in product side returned.
if None any reactions including this molecule.
:return: list of reaction entities
"""
q = left_join(x.reaction for x in self._database_.MoleculeReaction
if x.molecule == self).order_by(lambda x: x.id)
if product is not None:
q = q.where(lambda x: x.is_product == product)
reactions = q.page(page, pagesize)
if not reactions:
return []
self._database_.Reaction.prefetch_structure(reactions)
return list(reactions)
def participants(event):
m = Meeting.get(id=event, _type=MeetingPostType.MEETING.value)
if not m:
return redirect(url_for('.blog'))
_admin = True if current_user.is_authenticated and (current_user.role_is(UserRole.ADMIN) or
current_user.role_is(UserRole.SECRETARY)) else False
header = ('#', 'Participant', 'Country', 'Status', 'Degree', 'Presentation type', 'Town', 'Affiliation', 'Email') \
if _admin else ('#', 'Participant', 'Country', 'Status', 'Degree', 'Presentation type')
# cache users entities
list(left_join(x for x in User for s in x.subscriptions if s.meeting == m))
subs = Subscription.select(lambda x: x.meeting == m).order_by(Subscription.id.desc())
data = [row_admin(n, cell(url_for('.user', _user=x.user.id), x.user.full_name), x.user.country_name,
x.user.sci_status.fancy, x.user.sci_degree.fancy, x.type.fancy, x.user.town, x.user.affiliation,
x.user.email) if _admin else
row(n, cell(url_for('.user', _user=x.user.id), x.user.full_name), x.user.country_name,
x.user.sci_status.fancy, x.user.sci_degree.fancy, x.type.fancy)
for n, x in enumerate(subs, start=1)]
return render_template('table.html', table=table(header=header, rows=data), title=m.title, subtitle='Participants',
crumb=dict(url=url_for('.blog_post', post=event), title='Participants', parent='Event page'))
def highscores(year=None, month=None, day=None, week=None):
mode = 'all_time'
title = 'All Time'
subtitle = None
dt, f = None, None
today = datetime.datetime.now().date()
r = today # dummy for filter lambdas
# daily highscores
if year and month and day:
mode = 'day'
dt = datetime.date(year, month, day)
f = lambda: r.start_time.date() == dt
title = dt.strftime('%B %d, %Y')
# weekly highscores
elif year and week:
mode = 'week'
dt, dt_week_end = get_week_tuple(
datetime.date(year, 1, 1) + datetime.timedelta(weeks=week - 1))
f = lambda: r.start_time >= dt and r.start_time <= dt_week_end
title = 'Week {}, {}'.format(dt.isocalendar()[1], dt.year)
end_fmt = ' - %B %d' if dt.month != dt_week_end.month else '-%d'
subtitle = ' ({}{})'.format(dt.strftime('%B %d'),
dt_week_end.strftime(end_fmt))
# monthly highscores
elif year and month:
mode = 'month'
dt = datetime.date(year, month, 1)
f = lambda: r.start_time.year == dt.year and r.start_time.month == dt.month
title = dt.strftime('%B %Y')
# yearly highscores
elif year:
mode = 'year'
dt = datetime.date(year, 1, 1)
f = lambda: r.start_time.year == dt.year
title = dt.strftime('%Y')
if dt is not None and today < dt:
# Can't see into the future :(
abort(400, 'Cannot see into the future: {}'.format(dt))
highscores = left_join((p, sum(r.points), count(r)) for p in Player
for r in p.rounds_solved).order_by(-2)
if f is not None:
highscores = highscores.filter(f)
if mode == 'day':
if dt == today:
title = 'Today\'s'
if dt == today - datetime.timedelta(days=1):
title = 'Yesterday\'s'
backlink = None
if 'player' in request.args:
backlink = '{}?name={}'.format(url_for('stats_user'),
request.args.get('player'))
prevlink, nextlink = _highscore_nav_links(mode, dt)
return render_template('stats/highscores.html',
backlink=backlink,
prevlink=prevlink,
nextlink=nextlink,
title=title,
subtitle=subtitle,
mode=mode,
dt=dt,
today=today,
highscores=highscores[:10])
def get_entries_query(
cls,
metadata_type=None,
channel_pk=None,
exclude_deleted=False,
hide_xxx=False,
exclude_legacy=False,
origin_id=None,
sort_by=None,
sort_desc=True,
txt_filter=None,
subscribed=None,
category=None,
attribute_ranges=None,
infohash=None,
id_=None,
complete_channel=None,
self_checked_torrent=None,
):
"""
This method implements REST-friendly way to get entries from the database. It is overloaded by the higher
level classes to add some more conditions to the query.
:return: PonyORM query object corresponding to the given params.
"""
# Warning! For Pony magic to work, iteration variable name (e.g. 'g') should be the same everywhere!
pony_query = cls.search_keyword(txt_filter, lim=1000) if txt_filter else left_join(g for g in cls)
if metadata_type is not None:
try:
pony_query = pony_query.where(lambda g: g.metadata_type in metadata_type)
except TypeError:
pony_query = pony_query.where(lambda g: g.metadata_type == metadata_type)
pony_query = (
pony_query.where(public_key=(b"" if channel_pk == NULL_KEY_SUBST else channel_pk))
if channel_pk is not None
else pony_query
)
if attribute_ranges is not None:
for attr, left, right in attribute_ranges:
getattr(cls, attr) # Check against code injection
if left is not None:
pony_query = pony_query.where(f"g.{attr} >= left")
if right is not None:
pony_query = pony_query.where(f"g.{attr} < right")
# origin_id can be zero, for e.g. root channel
pony_query = pony_query.where(id_=id_) if id_ is not None else pony_query
pony_query = pony_query.where(origin_id=origin_id) if origin_id is not None else pony_query
pony_query = pony_query.where(lambda g: g.subscribed) if subscribed is not None else pony_query
pony_query = pony_query.where(lambda g: g.tags == category) if category else pony_query
pony_query = pony_query.where(lambda g: g.status != TODELETE) if exclude_deleted else pony_query
pony_query = pony_query.where(lambda g: g.xxx == 0) if hide_xxx else pony_query
pony_query = pony_query.where(lambda g: g.status != LEGACY_ENTRY) if exclude_legacy else pony_query
pony_query = pony_query.where(lambda g: g.infohash == infohash) if infohash else pony_query
pony_query = (
pony_query.where(lambda g: g.health.self_checked == self_checked_torrent)
if self_checked_torrent is not None
else pony_query
)
# ACHTUNG! Setting complete_channel to True forces the metadata type to Channels only!
pony_query = (
pony_query.where(lambda g: g.metadata_type == CHANNEL_TORRENT and g.timestamp == g.local_version)
if complete_channel
else pony_query
)
# Sort the query
pony_query = pony_query.sort_by("desc(g.rowid)" if sort_desc else "g.rowid")
if sort_by == "HEALTH":
pony_query = pony_query.sort_by(
"(desc(g.health.seeders), desc(g.health.leechers))"
if sort_desc
else "(g.health.seeders, g.health.leechers)"
)
elif sort_by == "size" and not issubclass(cls, db.ChannelMetadata):
# TODO: optimize this check to skip cases where size field does not matter
# When querying for mixed channels / torrents lists, channels should have priority over torrents
sort_expression = "desc(g.num_entries), desc(g.size)" if sort_desc else "g.num_entries, g.size"
pony_query = pony_query.sort_by(sort_expression)
elif sort_by:
sort_expression = "g." + sort_by
sort_expression = desc(sort_expression) if sort_desc else sort_expression
pony_query = pony_query.sort_by(sort_expression)
if txt_filter:
if sort_by is None:
pony_query = pony_query.sort_by(
f"""
1 if g.metadata_type == {CHANNEL_TORRENT} else
2 if g.metadata_type == {COLLECTION_NODE} else
3 if g.health.seeders > 0 else 4
"""
)
return pony_query
def test_queries():
print("All USA customers")
print()
result = select(c for c in Customer if c.country == "USA")[:]
print(result)
print()
print("The number of customers for each country")
print()
result = select((c.country, count(c)) for c in Customer)[:]
print(result)
print()
print("Max product price")
print()
result = max(p.price for p in Product.select())
print(result)
print()
print("Max SSD price")
print()
result = max(
p.price
for p in Product.select()
for cat in p.categories
if cat.name == "Solid State Drives"
)
print(result)
print()
print("Three most expensive products:")
print()
result = select(p for p in Product.select()).order_by(desc(Product.price))[:3]
print(result)
print()
print("Out of stock products")
print()
result = select(p for p in Product.select() if p.quantity == 0)[:]
print(result)
print()
print("Most popular product")
print()
result = (
select(p for p in Product.select())
.order_by(lambda p: desc(sum(p.order_items.quantity)))
.first()
)
print(result)
print()
print("Products that have never been ordered")
print()
result = select(p for p in Product.select() if not p.order_items)[:]
print(result)
print()
print("Customers who made several orders")
print()
result = select(c for c in Customer if count(c.orders) > 1)[:]
print(result)
print()
print("Three most valuable customers")
print()
result = select(c for c in Customer).order_by(
lambda c: desc(sum(c.orders.total_price))
)[:3]
print(result)
print()
print("Customers whose orders were shipped")
print()
result = select(c for c in Customer if SHIPPED in c.orders.state)[:]
print(result)
print()
print("The same query with the INNER JOIN instead of IN")
print()
result = select(c for c in Customer if JOIN(SHIPPED in c.orders.state))[:]
print(result)
print()
print("Customers with no orders")
print()
result = select(c for c in Customer if not c.orders)[:]
print(result)
print()
print("The same query with the LEFT JOIN instead of NOT EXISTS")
print()
result = left_join(c for c in Customer for o in c.orders if o is None)[:]
print(result)
print()
print("Customers which ordered several different tablets")
print()
result = select(
c
for c in Customer
for p in c.orders.items.product
if "Tablets" in p.categories.name and count(p) > 1
)[:]
print(result)
print()
print("Customers which ordered several products from the same category")
print()
result = select(
(customer, category.name)
for customer in Customer
for product in customer.orders.items.product
for category in product.categories
if count(product) > 1
)[:]
print(result)
print()
print(
"Customers which ordered several products from the same category in the same order"
)
print()
result = select(
(customer, order, category.name)
for customer in Customer
for order in customer.orders
for product in order.items.product
for category in product.categories
if count(product) > 1
)[:]
print(result)
print()
print("Products whose price varies over time")
print()
result = select(p.name for p in Product.select() if count(p.order_items.price) > 1)[
:
]
print(result)
print()
print("The same query, but with min and max price for each product")
print()
result = select(
(p.name, min(p.order_items.price), max(p.order_items.price))
for p in Product.select()
if count(p.order_items.price) > 1
)[:]
print(result)
print()
print("Orders with a discount (order total price < sum of order item prices)")
print()
result = select(
o for o in Order if o.total_price < sum(o.items.price * o.items.quantity)
)[:]
print(result)
print()
def get_articles(self):
list(
left_join(x.author for x in ArticleAuthor
if x.article in select(
y.article for y in ArticleAuthor
if y.author == self))) # cache coauthors
arts = list(
left_join(x.article for x in ArticleAuthor
if x.author == self).order_by(desc(
Article.date)).prefetch(Journal, JournalISSN))
authors = defaultdict(list)
for x in select(x for x in db.ArticleAuthor if x.article in select(
a.article for a in db.ArticleAuthor
if a.author == self)).order_by(ArticleAuthor.id):
authors[x.article.id].append(x.author)
issns = {x.journal.issn for x in arts if x.journal.issn}
scores, quarts = defaultdict(list), defaultdict(OrderedDict)
for x in select(x for x in Score
if x.issn in issns).order_by(desc(Score.year)):
scores[x.issn.id].append(x)
for x in select(x for x in Quartile
if x.issn in issns).order_by(desc(Quartile.year)):
quarts[x.issn.id].setdefault(x.year, []).append(x)
data = [
'***h***-index: **{0.h_index}**, citations count: **{0.citations}**, counted articles: '
'**{1}**) *[Provided by SCOPUS API]*\n\n'.format(
self, len(arts)), '**List of published articles:**\n\n',
'|Published|Meta|Cited Count|CiteScore|Quartiles|\n|---|---|---|---|---|\n'
]
f_scores, f_quarts = {}, {}
for k, v in scores.items():
f_scores[k] = ', '.join('{0.score:.2f}({0.year})'.format(x)
for x in v)
for k, v in quarts.items():
tmp = []
for y, x in v.items():
p = max((i.percentile
for i in x if i.subject_code in SCOPUS_SUBJECT),
default=None)
if p is not None:
tmp.append('{0}({1})'.format(
p > 74 and 'Q1' or p > 49 and 'Q2'
or p > 24 and 'Q3' or 'Q4', y))
f_quarts[k] = ', '.join(tmp)
for i in arts:
a = ', '.join('{0.initials} {0.surname}'.format(x)
for x in authors[i.id])
d = datetime.strftime(i.date, '%Y-%m-%d')
data.append(
'|**{1}**|*{0.title}* / {2} // ***{0.journal.title}***.— {0.date.year}'
.format(i, d, a))
if i.volume:
data.append('.— V.{.volume}'.format(i))
if i.issue:
data.append('.— Is.{.issue}'.format(i))
if i.pages:
data.append('.— P.{.pages}'.format(i))
if i.doi:
data.append(' [[doi](//dx.doi.org/{.doi})]'.format(i))
if i.journal.issn:
score = f_scores.get(i.journal.issn.id, '')
quart = f_quarts.get(i.journal.issn.id, '')
else:
score = quart = ''
data.append('|{0.cited}|{1}|{2}|\n'.format(i, score, quart))
return ''.join(data)
def get_entries_query(
self,
metadata_type=None,
channel_pk=None,
exclude_deleted=False,
hide_xxx=False,
exclude_legacy=False,
origin_id=None,
sort_by=None,
sort_desc=True,
max_rowid=None,
txt_filter=None,
subscribed=None,
category=None,
attribute_ranges=None,
infohash=None,
infohash_set=None,
id_=None,
complete_channel=None,
self_checked_torrent=None,
cls=None,
health_checked_after=None,
popular=None,
):
"""
This method implements REST-friendly way to get entries from the database.
:return: PonyORM query object corresponding to the given params.
"""
# Warning! For Pony magic to work, iteration variable name (e.g. 'g') should be the same everywhere!
if cls is None:
cls = self.ChannelNode
pony_query = self.search_keyword(txt_filter, lim=1000) if txt_filter else left_join(g for g in cls)
infohash_set = infohash_set or ({infohash} if infohash else None)
if popular:
if metadata_type != REGULAR_TORRENT:
raise TypeError('With `popular=True`, only `metadata_type=REGULAR_TORRENT` is allowed')
t = time() - POPULAR_TORRENTS_FRESHNESS_PERIOD
health_list = list(
select(
health
for health in self.TorrentState
if health.last_check >= t and (health.seeders > 0 or health.leechers > 0)
).order_by(
lambda health: (desc(health.seeders), desc(health.leechers), desc(health.last_check))
)[:POPULAR_TORRENTS_COUNT]
)
pony_query = pony_query.where(lambda g: g.health in health_list)
if max_rowid is not None:
pony_query = pony_query.where(lambda g: g.rowid <= max_rowid)
if metadata_type is not None:
try:
pony_query = pony_query.where(lambda g: g.metadata_type in metadata_type)
except TypeError:
pony_query = pony_query.where(lambda g: g.metadata_type == metadata_type)
pony_query = (
pony_query.where(public_key=(b"" if channel_pk == NULL_KEY_SUBST else channel_pk))
if channel_pk is not None
else pony_query
)
if attribute_ranges is not None:
for attr, left, right in attribute_ranges:
if (
self.ChannelNode._adict_.get(attr) # pylint: disable=W0212
or self.ChannelNode._subclass_adict_.get(attr) # pylint: disable=W0212
) is None: # Check against code injection
raise AttributeError("Tried to query for non-existent attribute")
if left is not None:
pony_query = pony_query.where(f"g.{attr} >= left")
if right is not None:
pony_query = pony_query.where(f"g.{attr} < right")
# origin_id can be zero, for e.g. root channel
pony_query = pony_query.where(id_=id_) if id_ is not None else pony_query
pony_query = pony_query.where(origin_id=origin_id) if origin_id is not None else pony_query
pony_query = pony_query.where(lambda g: g.subscribed) if subscribed is not None else pony_query
pony_query = pony_query.where(lambda g: g.tags == category) if category else pony_query
pony_query = pony_query.where(lambda g: g.status != TODELETE) if exclude_deleted else pony_query
pony_query = pony_query.where(lambda g: g.xxx == 0) if hide_xxx else pony_query
pony_query = pony_query.where(lambda g: g.status != LEGACY_ENTRY) if exclude_legacy else pony_query
pony_query = pony_query.where(lambda g: g.infohash in infohash_set) if infohash_set else pony_query
pony_query = (
pony_query.where(lambda g: g.health.self_checked == self_checked_torrent)
if self_checked_torrent is not None
else pony_query
)
# ACHTUNG! Setting complete_channel to True forces the metadata type to Channels only!
pony_query = (
pony_query.where(lambda g: g.metadata_type == CHANNEL_TORRENT and g.timestamp == g.local_version)
if complete_channel
else pony_query
)
if health_checked_after is not None:
pony_query = pony_query.where(lambda g: g.health.last_check >= health_checked_after)
# Sort the query
pony_query = pony_query.sort_by("desc(g.rowid)" if sort_desc else "g.rowid")
if sort_by == "HEALTH":
pony_query = pony_query.sort_by(
"(desc(g.health.seeders), desc(g.health.leechers))"
if sort_desc
else "(g.health.seeders, g.health.leechers)"
)
elif sort_by == "size" and not issubclass(cls, self.ChannelMetadata):
# Remark: this can be optimized to skip cases where size field does not matter
# When querying for mixed channels / torrents lists, channels should have priority over torrents
sort_expression = "desc(g.num_entries), desc(g.size)" if sort_desc else "g.num_entries, g.size"
pony_query = pony_query.sort_by(sort_expression)
elif sort_by:
sort_expression = raw_sql(f"g.{sort_by} COLLATE NOCASE" + (" DESC" if sort_desc else ""))
pony_query = pony_query.sort_by(sort_expression)
if sort_by is None:
if txt_filter:
pony_query = pony_query.sort_by(
f"""
(1 if g.metadata_type == {CHANNEL_TORRENT} else 2 if g.metadata_type == {COLLECTION_NODE} else 3),
desc(g.health.seeders), desc(g.health.leechers)
"""
)
elif popular:
pony_query = pony_query.sort_by('(desc(g.health.seeders), desc(g.health.leechers))')
return pony_query
