def list(self): # Parse the form. We don't really care if it doesn't validate; we'll # just ignore that field and show an error c.form = GameSearchForm( request.params, # Defaults player=None, role=None, race=None, gender=None, alignment=None, end_type=None, recency=u'anytime', sort=u'end_time', sortdir=u'desc', ) if not c.form.validate(): c.valid_form = False return render('/games/list.mako') c.valid_form = True # Perform filtering query = model.Game.query for column in ['player', 'role', 'race', 'gender', 'alignment', 'end_type']: if c.form[column].data: query = query.filter_by(**{ column: c.form[column].data }) if c.form.recency.data != 'anytime': now = datetime.now() earliest_date = now - timedelta(days=int(c.form.recency.data)) query = query.filter(model.Game.end_time >= earliest_date) # Sorting sort_col = c.form.sort.data sort_dir = c.form.sortdir.data sort_order = getattr(model.Game, sort_col) sort_order = getattr(sort_order, sort_dir)() query = query.order_by(sort_order) # TODO: paging c.games = query.all() c.descending_sort_fields = self.descending_sort_fields return render('/games/list.mako')
def view(self, name, end_time): """Show details for a particular game, selected by end timestamp.""" end_datetime = datetime.fromtimestamp( int(end_time) ) try: c.game = model.Game.query \ .join(model.Player) \ .filter(model.Player.name == name) \ .filter(model.Game.end_time == end_datetime) \ .one() except: abort(404) return render('/games/view.mako')
def view(self, name): try: c.player = model.Player.query.filter_by(name=name).one() except: abort(404) games_q = model.Game.query.filter_by(player=c.player) # Pull some simple stats c.game_count = games_q.count() # Count how this player's games are distributed across various # categories session = elixir.session count = func.count(model.Game.id).label('count') c.breakdowns = {} # label => [(table, count), ...] for table, label in [ (model.EndType, 'Ending'), (model.Role, 'Role'), (model.Race, 'Race'), (model.Gender, 'Gender'), (model.Alignment, 'Alignment'), ]: # Create a LEFT JOIN to get all of this player's games without # eliminating options s/he has never used # property.primaryjoin mumbo-jumbo courtsey of: # http://www.mail-archive.com/[email protected]/msg16304.html join = and_(getattr(table, 'games').property.primaryjoin, model.Game.player == c.player) # To be correct, the query must get the ids and their counts, then # join THAT again to get the full table rows. # Excellent subquery example from: # http://www.sqlalchemy.org/docs/ormtutorial.html#using-subqueries subq = session.query(table.id.label('table_id'), count) \ .outerjoin((model.Game, join)) \ .group_by(table.id) \ .subquery() q = session.query(table, subq.c.count) \ .outerjoin((subq, table.id == subq.c.table_id)) \ .order_by(subq.c.count.desc()) c.breakdowns[label] = q.all() return render('/players/view.mako')
def list(self): q = elixir.session.query( Game.epitaph_simple, EndType.identifier .label('end_type_identifier'), func.count(Game.id) .label('count'), func.sum(Game.points) .label('total_points'), func.avg(Game.points) .label('average_points'), func.max(Game.deepest_dlvl) .label('max_dlvl'), func.avg(Game.deepest_dlvl) .label('average_dlvl'), ) \ .join(Game.end_type) \ .group_by(Game.epitaph_simple, EndType.identifier) \ .order_by(func.count(Game.id).desc(), Game.epitaph_simple.asc()) c.deaths = q.all() return render('/deaths.mako')
def trophy(self): """The nethack.veekun.com trophy gallery.""" return render('/trophy.mako')
def index(self): """Main page, with a server overview and some links and whatever.""" return render('/index.mako')
def list(self): c.players = model.Player.query.order_by( func.lower(model.Player.name).asc() ).all() return render('/players/list.mako')