Beispiel #1
0
 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
Beispiel #2
0
    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}