def query_badge_report(self,
min_badges: int) -> Iterable[Tuple[User, int]]:
""" Get a summary of number of badges per user (in descending order). """
total = db.func.count(Badge.id).label('total')
return self.session.query(User, total).having(total >= min_badges)\
.filter(User.user_id == Badge.user_id)\
.group_by(Badge.user_id).order_by(db.desc(total)).all()
def query_user_records(user_group: Union[User, List[User], None], removed=False) -> List[Record]:
"""
:param user_group: User or user group as an iterable of users.
:param removed: Whether to search for non-removed or removed records.
:return:
"""
# Input validation
# type: Optional[List[User]]
user_list = [user_group] if isinstance(user_group, User) else user_group
# Query
query = session.query(Record).filter_by(is_removed=removed)
if user_list:
query = query.filter(Record.user_id.in_(u.user_id for u in user_list))
results = query.order_by(db.desc(Record.timestamp)).all()
logger.info("query_user_records: "
"Found {:d} records for user group: {!r}".format(len(results), user_group))
return results
def query_unexpired_records(*,
users: Union[User, Iterable[User]]=None,
types: Union[RecordType, Iterable[RecordType]]=None
):
"""
:param users: User or user group as a list of users.
:param types: type of record, or an iterable of them
"""
# Input validation
user_list = [users] if isinstance(users, User) else users # type: Optional[List[User]]
rtypes = [types] if isinstance(types, RecordType) else types # type: Optional[List[RecordType]]
# Query
query = session.query(Record).filter_by(is_removed=False) \
.filter(db.or_(datetime.utcnow() < Record.expires, Record.expires == None))
if user_list:
query = query.filter(Record.user_id.in_(u.user_id for u in user_list))
if rtypes:
query = query.filter(Record.type.in_(rtypes))
results = query.order_by(db.desc(Record.timestamp)).all()
logger.info("query_unexpired_records: "
"Found {:d} records for users={!r} types={!r}".format(len(results), users, rtypes))
return results
def get_top_saved(num: int = 3) -> List[Quote]:
total = db.func.count(Quote.quote_id).label('total')
return session.query(User, total).join(User.saved_quotes) \
.group_by(Quote.saved_by_id).order_by(db.desc(total)).limit(num).all()