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'))
