def query(self, collection, query):
solr_query = {}
solr_query['collection'] = collection['name']
if query.get('download'):
solr_query['rows'] = 1000
solr_query['start'] = 0
else:
solr_query['rows'] = int(collection['template']['rows'] or 10)
solr_query['start'] = int(query['start'])
solr_query['rows'] = min(solr_query['rows'], 1000)
solr_query['start'] = min(solr_query['start'], 10000)
params = self._get_params() + (
('q', self._get_q(query)),
('wt', 'json'),
('rows', solr_query['rows']),
('start', solr_query['start']),
)
if any(collection['facets']):
params += (
('facet', 'true'),
('facet.mincount', 0),
('facet.limit', 10),
)
json_facets = {}
timeFilter = self._get_range_borders(collection, query)
for facet in collection['facets']:
if facet['type'] == 'query':
params += (('facet.query', '%s' % facet['field']),)
elif facet['type'] == 'range' or facet['type'] == 'range-up':
keys = {
'id': '%(id)s' % facet,
'field': facet['field'],
'key': '%(field)s-%(id)s' % facet,
'start': facet['properties']['start'],
'end': facet['properties']['end'],
'gap': facet['properties']['gap'],
'mincount': int(facet['properties']['mincount'])
}
if timeFilter and timeFilter['time_field'] == facet['field'] and (facet['id'] not in timeFilter['time_filter_overrides'] or facet['widgetType'] != 'histogram-widget'):
keys.update(self._get_time_filter_query(timeFilter, facet))
params += (
('facet.range', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.range.start=%(start)s f.%(field)s.facet.range.end=%(end)s f.%(field)s.facet.range.gap=%(gap)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s' % keys),
)
elif facet['type'] == 'field':
keys = {
'id': '%(id)s' % facet,
'field': facet['field'],
'key': '%(field)s-%(id)s' % facet,
'limit': int(facet['properties'].get('limit', 10)) + (1 if facet['widgetType'] == 'facet-widget' else 0),
'mincount': int(facet['properties']['mincount'])
}
params += (
('facet.field', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s' % keys),
)
elif facet['type'] == 'nested':
_f = {}
if facet['properties']['facets']:
self._n_facet_dimension(facet, _f, facet['properties']['facets'], 1, timeFilter)
if facet['properties'].get('domain'):
if facet['properties']['domain'].get('blockParent') or facet['properties']['domain'].get('blockChildren'):
_f['domain'] = {}
if facet['properties']['domain'].get('blockParent'):
_f['domain']['blockParent'] = ' OR '.join(facet['properties']['domain']['blockParent'])
if facet['properties']['domain'].get('blockChildren'):
_f['domain']['blockChildren'] = ' OR '.join(facet['properties']['domain']['blockChildren'])
if _f:
sort = {'count': facet['properties']['facets'][0]['sort']}
for i, agg in enumerate(self._get_dimension_aggregates(facet['properties']['facets'][1:])):
if agg['sort'] != 'default':
agg_function = self._get_aggregate_function(agg)
sort = {'agg_%02d_%02d:%s' % (1, i, agg_function): agg['sort']}
if sort.get('count') == 'default':
sort['count'] = 'desc'
dim_key = [key for key in _f['facet'].keys() if 'dim' in key][0]
_f['facet'][dim_key].update({
'excludeTags': facet['id'],
'offset': 0,
'numBuckets': True,
'allBuckets': True,
'sort': sort
#'prefix': '' # Forbidden on numeric fields
})
json_facets[facet['id']] = _f['facet'][dim_key]
elif facet['type'] == 'function':
if facet['properties']['facets']:
json_facets[facet['id']] = self._get_aggregate_function(facet['properties']['facets'][0])
if facet['properties']['compare']['is_enabled']:
# TODO: global compare override
unit = re.split('\d+', facet['properties']['compare']['gap'])[1]
json_facets[facet['id']] = {
'type': 'range',
'field': collection['timeFilter'].get('field'),
'start': 'NOW/%s-%s-%s' % (unit, facet['properties']['compare']['gap'], facet['properties']['compare']['gap']),
'end': 'NOW/%s' % unit,
'gap': '+%(gap)s' % facet['properties']['compare'],
'facet': {facet['id']: json_facets[facet['id']]}
}
if facet['properties']['filter']['is_enabled']:
json_facets[facet['id']] = {
'type': 'query',
'q': facet['properties']['filter']['query'] or EMPTY_QUERY.get(),
'facet': {facet['id']: json_facets[facet['id']]}
}
json_facets['processEmpty'] = True
elif facet['type'] == 'pivot':
if facet['properties']['facets'] or facet['widgetType'] == 'map-widget':
fields = facet['field']
fields_limits = []
for f in facet['properties']['facets']:
fields_limits.append('f.%s.facet.limit=%s' % (f['field'], f['limit']))
fields_limits.append('f.%s.facet.mincount=%s' % (f['field'], f['mincount']))
fields += ',' + f['field']
keys = {
'id': '%(id)s' % facet,
'key': '%(field)s-%(id)s' % facet,
'field': facet['field'],
'fields': fields,
'limit': int(facet['properties'].get('limit', 10)),
'mincount': int(facet['properties']['mincount']),
'fields_limits': ' '.join(fields_limits)
}
params += (
('facet.pivot', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s %(fields_limits)s}%(fields)s' % keys),
)
if json_facets:
params += (
('json.facet', json.dumps(json_facets)),
)
params += self._get_fq(collection, query)
fl = urllib.unquote(utf_quoter(','.join(Collection2.get_field_list(collection))))
nested_fields = self._get_nested_fields(collection)
if nested_fields:
fl += urllib.unquote(utf_quoter(',[child parentFilter="%s"]' % ' OR '.join(nested_fields)))
if collection['template']['moreLikeThis'] and fl != ['*']: # Potential conflict with nested documents
id_field = collection.get('idField', 'id')
params += (
('mlt', 'true'),
('mlt.fl', fl.replace(',%s' % id_field, '')),
('mlt.mintf', 1),
('mlt.mindf', 1),
('mlt.maxdf', 50),
('mlt.maxntp', 1000),
('mlt.count', 10),
#('mlt.minwl', 1),
#('mlt.maxwl', 1),
)
fl = '*'
params += (('fl', fl),)
params += (
('hl', 'true'),
('hl.fl', '*'),
('hl.snippets', 5),
('hl.fragsize', 1000),
)
if collection['template']['fieldsSelected']:
fields = []
for field in collection['template']['fieldsSelected']:
attribute_field = filter(lambda attribute: field == attribute['name'], collection['template']['fieldsAttributes'])
if attribute_field:
if attribute_field[0]['sort']['direction']:
fields.append('%s %s' % (field, attribute_field[0]['sort']['direction']))
if fields:
params += (
('sort', ','.join(fields)),
)
response = self._root.get('%(collection)s/select' % solr_query, params)
return self._get_json(response)
def query(self, dashboard, query, facet=None):
if query['qs'] == [{'q': '_root_:*'}]:
return {'response': {'numFound': 0}}
filters = [q['q'] for q in query['qs'] if q['q']]
filters.extend(self._get_fq(dashboard, query, facet))
result_properties = {}
timeFilter = self._get_time_filter_query(dashboard, query)
if timeFilter:
filters.append(timeFilter)
if self.source == 'query':
sql_from = '(%(query)s) t' % {
'query': self._get_query(dashboard['name'])
}
database, table = '', ''
else:
database, table = self._get_database_table_names(dashboard['name'])
sql_from = '%(backticks)s%(database)s%(backticks)s.%(backticks)s%(table)s%(backticks)s' % {
'database': database,
'table': table,
'backticks': self.backticks
}
if facet and facet['properties']['facets']:
for i, _facet in enumerate(facet['properties']['facets']):
_facet['position'] = i
if facet['type'] == 'nested':
fields_dimensions = [
self._get_dimension_field(f)['name']
for f in self._get_dimension_fields(facet)
]
last_dimension_seen = False
fields = []
for f in reversed(facet['properties']['facets']):
if f['aggregate']['function'] == 'count':
if not last_dimension_seen:
fields.insert(
0, 'COUNT(*) AS %(field)s_%(position)s' % f)
last_dimension_seen = True
fields.insert(
0,
self._get_dimension_field(f)
['name' if last_dimension_seen else 'select'])
else:
if not last_dimension_seen:
fields.insert(
0,
self._get_aggregate_function(f) +
'AS %(field)s_%(position)s' % f)
has_facet_mincount_greater_than_one = [
f for f in facet['properties']['facets']
if f['mincount'] > 1
]
if has_facet_mincount_greater_than_one and self._supports_count_over(
):
mincount_fields_name = []
mincount_fields_operation = []
mincount_where = []
for f in facet['properties']['facets']:
mincount_fields_name.append(f['field'])
mincount_field_name = 'count__' + '_'.join(
mincount_fields_name)
mincount_fields_operation.append(
'COUNT(*) OVER (PARTITION BY %s) AS %s' %
(', '.join(mincount_fields_name),
mincount_field_name))
mincount_where.append(
'%s >= %s' %
(mincount_field_name, str(f['mincount'])))
sql_from = '''(SELECT * FROM (SELECT *, %(fields)s
FROM %(sql_from)s) default
WHERE %(where)s) default''' % {
'fields': ', '.join(mincount_fields_operation),
'sql_from': sql_from,
'where': ' AND '.join(mincount_where)
}
order_by = ', '.join([
self._get_dimension_field(f)['order_by']
for f in reversed(facet['properties']['facets'])
if f['sort'] != 'default'
])
sql = '''SELECT %(fields)s
FROM %(sql_from)s
%(filters)s
GROUP BY %(fields_dimensions)s
%(order_by)s
LIMIT %(limit)s''' % {
'sql_from': sql_from,
'fields': ', '.join(fields),
'fields_dimensions': ', '.join(fields_dimensions),
'order_by': 'ORDER BY %s' % order_by if order_by else '',
'filters': self._convert_filters_to_where(filters),
'limit': LIMIT
}
elif facet['type'] == 'function': # 1 dim only now
aggregate_function = facet['properties']['facets'][0][
'aggregate']['function']
if (aggregate_function == 'percentile' or aggregate_function
== 'median') and not self._supports_percentile(
) and self._supports_cume_dist():
sql_from = '''
(SELECT *
FROM
(
SELECT %(field)s, cume_dist() OVER (ORDER BY %(field)s) * 100 AS cume_dist__%(field)s
FROM %(sql_from)s
) DEFAULT
WHERE cume_dist__%(field)s >= %(value)s) DEFAULT
''' % {
'field':
facet['properties']['facets'][0]['field'],
'value':
facet['properties']['facets'][0]['aggregate']
['percentile']
if aggregate_function == 'percentile' else 50,
'sql_from':
sql_from,
}
sql = '''SELECT %(fields)s
FROM %(sql_from)s
%(filters)s''' % {
'sql_from':
sql_from,
'fields':
self._get_aggregate_function(
facet['properties']['facets'][0]),
'filters':
self._convert_filters_to_where(filters),
}
elif facet['type'] == 'statement':
doc = Document2.objects.get_by_uuid(
user=self.user,
uuid=facet['properties']['statementUuid'],
perm_type='read')
snippets = doc.data_dict.get('snippets', [])
sql = snippets[0].get('statement', '')
result_properties = facet['properties']['result']
else:
fields = Collection2.get_field_list(dashboard)
order_by = ', '.join([
'%(backticks)s%(name)s%(backticks)s %(direction)s' % {
'backticks': self.backticks,
'name': f['name'],
'direction': f['sort']['direction']
} for f in dashboard['template']['fieldsAttributes']
if f['sort']['direction'] and f['name'] in fields
])
sql = '''
SELECT %(fields)s
FROM %(sql_from)s
%(filters)s
%(order_by)s
%(limit)s''' % {
'sql_from':
sql_from,
'fields':
', '.join([
'%(backticks)s%(column)s%(backticks)s as %(backticks)s%(column)s%(backticks)s'
% {
'backticks': self.backticks,
'column': f
} if f != '*' else '*' for f in fields
]),
'filters':
self._convert_filters_to_where(filters) if filters else '',
'order_by':
'ORDER BY %s' % order_by if order_by else '',
'limit':
'LIMIT %s' % dashboard['template']['rows'] or LIMIT
}
editor = make_notebook(name='Execute and watch',
editor_type=dashboard['engine'],
statement=sql,
database=database,
status='ready-execute',
skip_historify=True,
result_properties=result_properties)
response = editor.execute(MockRequest(self.user, self.cluster))
if 'handle' in response and response['handle'].get('sync'):
response['result'] = self._convert_result(response['result'],
dashboard, facet, query)
return response
def query(self, collection, query):
solr_query = {}
solr_query['collection'] = collection['name']
if query.get('download'):
solr_query['rows'] = 1000
solr_query['start'] = 0
else:
solr_query['rows'] = int(collection['template']['rows'] or 10)
solr_query['start'] = int(query['start'])
solr_query['rows'] = min(solr_query['rows'], 1000)
solr_query['start'] = min(solr_query['start'], 10000)
params = self._get_params() + (
('q', self._get_q(query)),
('wt', 'json'),
('rows', solr_query['rows']),
('start', solr_query['start']),
)
if any(collection['facets']):
params += (
('facet', 'true'),
('facet.mincount', 0),
('facet.limit', 10),
)
json_facets = {}
timeFilter = self._get_range_borders(collection, query)
for facet in collection['facets']:
if facet['type'] == 'query':
params += (('facet.query', '%s' % facet['field']), )
elif facet['type'] == 'range' or facet['type'] == 'range-up':
keys = {
'id': '%(id)s' % facet,
'field': facet['field'],
'key': '%(field)s-%(id)s' % facet,
'start': facet['properties']['start'],
'end': facet['properties']['end'],
'gap': facet['properties']['gap'],
'mincount': int(facet['properties']['mincount'])
}
if timeFilter and timeFilter['time_field'] == facet[
'field'] and (
facet['id']
not in timeFilter['time_filter_overrides']
or facet['widgetType'] != 'histogram-widget'):
keys.update(
self._get_time_filter_query(timeFilter, facet))
params += ((
'facet.range',
'{!key=%(key)s ex=%(id)s f.%(field)s.facet.range.start=%(start)s f.%(field)s.facet.range.end=%(end)s f.%(field)s.facet.range.gap=%(gap)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s'
% keys), )
elif facet['type'] == 'field':
keys = {
'id':
'%(id)s' % facet,
'field':
facet['field'],
'key':
'%(field)s-%(id)s' % facet,
'limit':
int(facet['properties'].get('limit', 10)) +
(1 if facet['widgetType'] == 'facet-widget' else 0),
'mincount':
int(facet['properties']['mincount'])
}
params += ((
'facet.field',
'{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s'
% keys), )
elif facet['type'] == 'nested':
_f = {
'field':
facet['field'],
'limit':
int(facet['properties'].get('limit', 10)) +
(1
if facet['widgetType'] == 'text-facet-widget' else 0),
'mincount':
int(facet['properties']['mincount']),
'sort': {
'count': facet['properties']['sort']
},
}
if facet['properties']['domain'].get(
'blockParent'
) or facet['properties']['domain'].get('blockChildren'):
_f['domain'] = {}
if facet['properties']['domain'].get('blockParent'):
_f['domain']['blockParent'] = ' OR '.join(
facet['properties']['domain']['blockParent'])
if facet['properties']['domain'].get('blockChildren'):
_f['domain']['blockChildren'] = ' OR '.join(
facet['properties']['domain']['blockChildren'])
if 'start' in facet['properties'] and not facet[
'properties'].get('type') == 'field':
_f.update({
'type': 'range',
'start': facet['properties']['start'],
'end': facet['properties']['end'],
'gap': facet['properties']['gap'],
})
if timeFilter and timeFilter['time_field'] == facet[
'field'] and (
facet['id']
not in timeFilter['time_filter_overrides']
or facet['widgetType'] != 'bucket-widget'):
_f.update(
self._get_time_filter_query(timeFilter, facet))
else:
_f.update({
'type': 'terms',
'field': facet['field'],
'excludeTags': facet['id'],
'offset': 0,
'numBuckets': True,
'allBuckets': True,
#'prefix': '' # Forbidden on numeric fields
})
if facet['properties']['canRange'] and not facet[
'properties']['isDate']:
del _f['mincount'] # Numeric fields do not support
if facet['properties']['facets']:
self._n_facet_dimension(facet, _f,
facet['properties']['facets'],
1)
if facet['widgetType'] == 'text-facet-widget':
_fname = _f['facet'].keys()[0]
_f['sort'] = {_fname: facet['properties']['sort']}
# domain = '-d2:NaN' # Solr 6.4
json_facets[facet['id']] = _f
elif facet['type'] == 'function':
json_facets[facet['id']] = self._get_aggregate_function(
facet)
json_facets['processEmpty'] = True
elif facet['type'] == 'pivot':
if facet['properties']['facets'] or facet[
'widgetType'] == 'map-widget':
fields = facet['field']
fields_limits = []
for f in facet['properties']['facets']:
fields_limits.append('f.%s.facet.limit=%s' %
(f['field'], f['limit']))
fields_limits.append('f.%s.facet.mincount=%s' %
(f['field'], f['mincount']))
fields += ',' + f['field']
keys = {
'id': '%(id)s' % facet,
'key': '%(field)s-%(id)s' % facet,
'field': facet['field'],
'fields': fields,
'limit': int(facet['properties'].get('limit', 10)),
'mincount': int(facet['properties']['mincount']),
'fields_limits': ' '.join(fields_limits)
}
params += ((
'facet.pivot',
'{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s %(fields_limits)s}%(fields)s'
% keys), )
if json_facets:
params += (('json.facet', json.dumps(json_facets)), )
params += self._get_fq(collection, query)
from dashboard.models import Collection2
fl = urllib.unquote(
utf_quoter(','.join(Collection2.get_field_list(collection))))
nested_fields = self._get_nested_fields(collection)
if nested_fields:
fl += urllib.unquote(
utf_quoter(',[child parentFilter="%s"]' %
' OR '.join(nested_fields)))
params += (('fl', fl), )
params += (
('hl', 'true'),
('hl.fl', '*'),
('hl.snippets', 5),
('hl.fragsize', 1000),
)
if collection['template']['fieldsSelected']:
fields = []
for field in collection['template']['fieldsSelected']:
attribute_field = filter(
lambda attribute: field == attribute['name'],
collection['template']['fieldsAttributes'])
if attribute_field:
if attribute_field[0]['sort']['direction']:
fields.append(
'%s %s' %
(field, attribute_field[0]['sort']['direction']))
if fields:
params += (('sort', ','.join(fields)), )
response = self._root.get('%(collection)s/select' % solr_query, params)
return self._get_json(response)
def query(self, dashboard, query, facet=None):
database, table = self._get_database_table_names(dashboard['name'])
if query['qs'] == [{'q': '_root_:*'}]:
return {'response': {'numFound': 0}}
filters = [q['q'] for q in query['qs'] if q['q']]
filters.extend(self._get_fq(dashboard, query, facet))
timeFilter = self._get_time_filter_query(dashboard, query)
if timeFilter:
filters.append(timeFilter)
if facet:
if facet['type'] == 'nested':
fields_dimensions = [
self._get_dimension_field(f)['name']
for f in self._get_dimension_fields(facet)
]
last_dimension_seen = False
fields = []
for f in reversed(facet['properties']['facets']):
if f['aggregate']['function'] == 'count':
if not last_dimension_seen:
fields.insert(0, 'COUNT(*) AS Count')
last_dimension_seen = True
fields.insert(0,
self._get_dimension_field(f)['select'])
else:
if not last_dimension_seen:
fields.insert(0, self._get_aggregate_function(f))
if not last_dimension_seen:
fields.insert(0, 'COUNT(*) as Count')
fields.insert(0, self._get_dimension_field(facet)['select'])
sql = '''SELECT %(fields)s
FROM %(database)s.%(table)s
%(filters)s
GROUP BY %(fields_dimensions)s
ORDER BY %(order_by)s
LIMIT %(limit)s''' % {
'database':
database,
'table':
table,
'fields':
', '.join(fields),
'fields_dimensions':
', '.join(fields_dimensions),
'order_by':
', '.join([
self._get_dimension_field(f)['order_by']
for f in self._get_dimension_fields(facet)
]),
'filters':
self._convert_filters_to_where(filters),
'limit':
LIMIT
}
elif facet['type'] == 'function': # 1 dim only now
sql = '''SELECT %(fields)s
FROM %(database)s.%(table)s
%(filters)s''' % {
'database': database,
'table': table,
'fields': self._get_aggregate_function(facet),
'filters': self._convert_filters_to_where(filters),
}
else:
fields = Collection2.get_field_list(dashboard)
sql = "SELECT %(fields)s FROM `%(database)s`.`%(table)s`" % {
'database':
database,
'table':
table,
'fields':
', '.join(['`%s`' % f if f != '*' else '*' for f in fields])
}
if filters:
sql += ' ' + self._convert_filters_to_where(filters)
sql += ' LIMIT %s' % LIMIT
editor = make_notebook(name='Execute and watch',
editor_type=dashboard['engine'],
statement=sql,
database=database,
status='ready-execute',
skip_historify=True)
response = editor.execute(MockRequest(self.user))
if 'handle' in response and response['handle'].get('sync'):
response['result'] = self._convert_result(response['result'],
dashboard, facet, query)
return response
def query(self, dashboard, query, facet=None):
database, table = self._get_database_table_names(dashboard['name'])
if query['qs'] == [{'q': '_root_:*'}]:
return {'response': {'numFound': 0}}
filters = [q['q'] for q in query['qs'] if q['q']]
filters.extend(self._get_fq(dashboard, query, facet))
timeFilter = self._get_time_filter_query(dashboard, query)
if timeFilter:
filters.append(timeFilter)
if facet:
if facet['type'] == 'nested':
fields_dimensions = [self._get_dimension_field(f)['name'] for f in self._get_dimension_fields(facet)]
last_dimension_seen = False
fields = []
for f in reversed(facet['properties']['facets']):
if f['aggregate']['function'] == 'count':
if not last_dimension_seen:
fields.insert(0, 'COUNT(*) AS Count')
last_dimension_seen = True
fields.insert(0, self._get_dimension_field(f)['select'])
else:
if not last_dimension_seen:
fields.insert(0, self._get_aggregate_function(f))
if not last_dimension_seen:
fields.insert(0, 'COUNT(*) as Count')
fields.insert(0, self._get_dimension_field(facet)['select'])
sql = '''SELECT %(fields)s
FROM %(database)s.%(table)s
%(filters)s
GROUP BY %(fields_dimensions)s
ORDER BY %(order_by)s
LIMIT %(limit)s''' % {
'database': database,
'table': table,
'fields': ', '.join(fields),
'fields_dimensions': ', '.join(fields_dimensions),
'order_by': ', '.join([self._get_dimension_field(f)['order_by'] for f in self._get_dimension_fields(facet)]),
'filters': self._convert_filters_to_where(filters),
'limit': LIMIT
}
elif facet['type'] == 'function': # 1 dim only now
sql = '''SELECT %(fields)s
FROM %(database)s.%(table)s
%(filters)s''' % {
'database': database,
'table': table,
'fields': self._get_aggregate_function(facet),
'filters': self._convert_filters_to_where(filters),
}
else:
fields = Collection2.get_field_list(dashboard)
sql = "SELECT %(fields)s FROM `%(database)s`.`%(table)s`" % {
'database': database,
'table': table,
'fields': ', '.join(['`%s`' % f if f != '*' else '*' for f in fields])
}
if filters:
sql += ' ' + self._convert_filters_to_where(filters)
sql += ' LIMIT %s' % LIMIT
editor = make_notebook(
name='Execute and watch',
editor_type=dashboard['engine'],
statement=sql,
database=database,
status='ready-execute',
skip_historify=True
)
response = editor.execute(MockRequest(self.user))
if 'handle' in response and response['handle'].get('sync'):
response['result'] = self._convert_result(response['result'], dashboard, facet, query)
return response
def query(self, dashboard, query, facet=None):
database, table = self._get_database_table_names(dashboard['name'])
if query['qs'] == [{'q': '_root_:*'}]:
return {'response': {'numFound': 0}}
filters = [q['q'] for q in query['qs'] if q['q']]
filters.extend(self._get_fq(dashboard, query, facet))
timeFilter = self._get_time_filter_query(dashboard, query)
if timeFilter:
filters.append(timeFilter)
if facet and facet['properties']['facets']:
for i, _facet in enumerate(facet['properties']['facets']):
_facet['position'] = i
if facet['type'] == 'nested':
fields_dimensions = [
self._get_dimension_field(f)['name']
for f in self._get_dimension_fields(facet)
]
last_dimension_seen = False
fields = []
for f in reversed(facet['properties']['facets']):
if f['aggregate']['function'] == 'count':
if not last_dimension_seen:
fields.insert(
0, 'COUNT(*) AS %(field)s_%(position)s' % f)
last_dimension_seen = True
fields.insert(
0,
self._get_dimension_field(f)
['name' if last_dimension_seen else 'select'])
else:
if not last_dimension_seen:
fields.insert(
0,
self._get_aggregate_function(f) +
'AS %(field)s_%(position)s' % f)
if self._supports_count_over():
mincount_fields_name = []
mincount_fields_operation = []
mincount_where = []
for f in facet['properties']['facets']:
mincount_fields_name.append(f['field'])
mincount_field_name = '_'.join(
mincount_fields_name) + "_count"
mincount_fields_operation.append(
'count(*) over (partition by %s) as %s' %
(','.join(mincount_fields_name),
mincount_field_name))
mincount_where.append(
'%s >= %s' %
(mincount_field_name, str(f['mincount'])))
sql_mincount = '''(SELECT * FROM (SELECT *,%(fields)s
FROM %(database)s.%(table)s) default
WHERE %(where)s) default''' % {
'fields': ', '.join(mincount_fields_operation),
'database': database,
'table': table,
'where': ' and '.join(mincount_where)
}
else:
sql_mincount = '(%(database)s.%(table)s)' % {
'database': database,
'table': table
}
order_by = ', '.join([
self._get_dimension_field(f)['order_by']
for f in reversed(facet['properties']['facets'])
if f['sort'] != 'default'
])
sql = '''SELECT %(fields)s
FROM %(sql_mincount)s
%(filters)s
GROUP BY %(fields_dimensions)s
%(order_by)s
LIMIT %(limit)s''' % {
'sql_mincount': sql_mincount,
'database': database,
'table': table,
'fields': ', '.join(fields),
'fields_dimensions': ', '.join(fields_dimensions),
'order_by': 'ORDER BY %s' % order_by if order_by else '',
'filters': self._convert_filters_to_where(filters),
'limit': LIMIT
}
elif facet['type'] == 'function': # 1 dim only now
sql = '''SELECT %(fields)s
FROM %(database)s.%(table)s
%(filters)s''' % {
'database':
database,
'table':
table,
'fields':
self._get_aggregate_function(
facet['properties']['facets'][0]),
'filters':
self._convert_filters_to_where(filters),
}
else:
fields = Collection2.get_field_list(dashboard)
sql = "SELECT %(fields)s FROM `%(database)s`.`%(table)s`" % {
'database':
database,
'table':
table,
'fields':
', '.join(['`%s`' % f if f != '*' else '*' for f in fields])
}
if filters:
sql += ' ' + self._convert_filters_to_where(filters)
sql += ' LIMIT %s' % dashboard['template']['rows'] or LIMIT
editor = make_notebook(name='Execute and watch',
editor_type=dashboard['engine'],
statement=sql,
database=database,
status='ready-execute',
skip_historify=True)
response = editor.execute(MockRequest(self.user))
if 'handle' in response and response['handle'].get('sync'):
response['result'] = self._convert_result(response['result'],
dashboard, facet, query)
return response