def tune_history(self, tunes, desc=False, dates=None): order = PlayData.date if desc: order = sqlalchemy.desc(order) query = object_session(self).\ query(PlayData.score.label('value'), PlayData.date, func.unix_timestamp(PlayData.date).label('unixtime')).\ filter(PlayData.friend_id == self.friend_id).\ filter(util.tune_conds(tunes, PlayData)) if dates is not None: query = util.cutoff_dates(query, PlayData.date, dates) return query
def ranking(cls, tunes, friend_id=0, date=None): """Export ranking for selected tunes. Each tune is described as like a tuple (music_id, dif_id) """ query = session.\ query(cls.friend_id, func.max(cls.score).label('score')).\ filter(cls.friend_id > 10).\ filter(util.tune_conds(tunes, PlayData)) if date is not None: query = query.filter(cls.date <= date) query = query.group_by(cls.friend_id, cls.music_id, cls.dif_id) max_set = query.subquery().alias() return session.\ query( func.sum(max_set.c.score).label('value'), (max_set.c.friend_id == friend_id).label('is_mine')).\ group_by(max_set.c.friend_id).order_by(desc('value'))