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