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)