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