def materialize(self, conditions="1=1", order_by=None):
""" Generate a fully denormalized view of the entries on this
table. """
joins = self.alias
for f in self.fields:
joins = f.join(joins)
query = db.select([f.selectable for f in self.fields],
conditions, joins, order_by=order_by,
use_labels=True)
rp = self.bind.execute(query)
while True:
row = rp.fetchone()
if row is None:
break
result = {}
for k, v in row.items():
field, attr = k.split('_', 1)
if field == 'entry':
result[attr] = v
else:
if not field in result:
result[field] = dict()
result[field][attr] = v
yield result
def aggregate(self, metric='amount', drilldowns=None, cuts=None,
page=1, pagesize=10000, order=None):
cuts = cuts or []
drilldowns = drilldowns or []
joins = self.alias
for dimension in set(drilldowns + [k for k,v in cuts]):
joins = self[dimension.split('.')[0]].join(joins)
group_by = []
fields = [db.func.sum(self.alias.c.amount).label(metric),
db.func.count(self.alias.c.id).label("entries")]
for key in drilldowns:
column = self.key(key)
if '.' in key or column.table == self.alias:
fields.append(column)
else:
fields.append(column.table)
group_by.append(column)
conditions = db.and_()
filters = defaultdict(set)
for key, value in cuts:
column = self.key(key)
filters[column].add(value)
for attr, values in filters.items():
conditions.append(db.or_(*[attr==v for v in values]))
order_by = []
for key, direction in order or []:
# TODO: handle case in which order criterion is not joined.
column = self.key(key)
order_by.append(column.desc() if direction else column.asc())
query = db.select(fields, conditions, joins,
order_by=order_by or [metric + ' desc'],
group_by=group_by, use_labels=True)
#print query
summary = {metric: 0.0, 'num_entries': 0}
drilldown = []
rp = self.bind.execute(query)
while True:
row = rp.fetchone()
if row is None:
break
result = {}
for key, value in row.items():
if key == metric:
summary[metric] += value
if key == 'entries':
summary['num_entries'] += value
if '_' in key:
dimension, attribute = key.split('_', 1)
if dimension == 'entry':
result[attribute] = value
else:
if not dimension in result:
result[dimension] = {}
result[dimension][attribute] = value
else:
if key == 'entries':
key = 'num_entries'
result[key] = value
drilldown.append(result)
offset = ((page-1)*pagesize)
return {'drilldown': drilldown[offset:offset+pagesize],
'summary': summary}