def parse_where(rules): from query.clause import Column where = {} for rule in rules: if not rule.get('field1') or not rule.get('field2'): continue table1, field1 = rule['field1'] table2, field2 = rule['field2'] keys = where.keys() for key in keys: if table1 in key or table2 in key: value = where.pop(key) value.append( Column(table1, field1).label('%s.%s' % (table1, field1)) == Column(table2, field2).label('%s.%s' % (table2, field2))) key = tuple(set(key) | {table1, table2}) where[key] = value break else: where[(table1, table2)] = [ Column(table1, field1).label('%s.%s' % (table1, field1)) == Column( table2, field2).label('%s.%s' % (table2, field2)) ] return where
def function(func_name, name): from query.clause import Function, Column if func_name == 'distinct': return Function('count', Column(name).distinct()).label( '%s_%s' % (name, func_name)).json() return Function(func_name, Column(name)).label('%s_%s' % (name, func_name)).json()
def where(self): wheres = [] filters = self.args['rules'].get('filters', []) for condition in filters: if not condition['name'] or not condition['operator']: continue name = condition['name'] operator = condition['operator'] value = condition['value'] c = Condition(Column(name), operator, value) wheres.append(c.json()) return wheres
def where(self): wheres = [] for condition in self.args.get('filters', []): if not condition['name'] or not condition['operator']: continue name = condition['name'] operator = condition['operator'] if condition.get('value_type', 'value') == 'value': value = condition['value'] else: value = Text(condition['value']) c = Condition(Column(name), operator, value) wheres.append(c.json()) return wheres
def join_table(self): wheres = parse_where(self.args['rules']) ds = self.meta.datasource(id=self.args['ds_id']).single() engine = Engine( Connector(user_id=self.user_id, type=ds.type, db=ds.name, **ds.params)) if len(wheres) != 1: self.response(422, '需要最终合成一张表') return tablenames, clause = wheres.items()[0] columns = [] vtables = self.meta.vtable(ds_id=self.args['ds_id']).all() vtables = {tb.name: tb.query for tb in vtables} for name in tablenames: columns += [ Column(name, _['name']).label('%s.%s' % (name, _['name'])) for _ in self.schema(engine, vtables, name) ] return Table(*columns).select_from(*tablenames).where(*clause).json()
def group_by(self): if not self.args['xFields']: return None return [Column(_).json() for _ in self.args['xFields']]
def group_by(self): x_fields = self.args['rules']['x_fields'] if not x_fields: return None return [Column(_).json() for _ in x_fields]
def get(self): args = self.parse_args([{ 'name': 'ds_id', 'required': True, 'location': 'args' }, { 'name': 'tables', 'required': True, 'location': 'args', 'cast': json.loads }, { 'name': 'rules', 'required': False, 'location': 'args', 'cast': json.loads }]) result = [] rules = args['rules'] tables = args['tables'] dbmeta = DBMeta(self.user_id) ds = dbmeta.datasource(id=args['ds_id']).single() connector = Connector(user_id=self.user_id, type=ds.type, db=ds.name, **ds.params) engine = Engine(connector) wheres = parse_where(rules) vtables = dbmeta.vtable(ds_id=self.args['ds_id']).all() vtables = [(tb.name, tb.query) for tb in vtables] for tablenames, clause in wheres.items(): columns = [] for name in tablenames: columns += [ Column(name, _['name']).label('%s.%s' % (name, _['name'])) for _ in engine.schema(name) ] table_json = Table(*columns).select_from(*tablenames).where( *clause).json() q = Query(table=table_json, limit=10) q.bind(connector) ret = q.execute() result.append({ 'data': ret.json_data, 'sql': q.sql, 'columns': ret.columns, 'names': tablenames }) keys = sum(wheres.keys(), ()) tables = [(_, dict(vtables).get(_, _)) for _ in tables if _ not in keys] for name, tb in tables: q = get_query(tb, 10) q.bind(connector) ret = q.execute() result.append({ 'data': ret.json_data, 'columns': ret.columns, 'names': [name], 'schema': ret.schema }) self.response(total=len(result), tables=result)