def test_merge_extra_filters_ignores_nones(self):
form_data = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': '',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': None,
},
],
'extra_filters': [
{'col': 'B', 'op': '==', 'val': []},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': '',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': None,
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
def test_merge_extra_filters_ignores_empty_filters(self):
form_data = {'extra_filters': [
{'col': 'a', 'op': 'in', 'val': ''},
{'col': 'B', 'op': '==', 'val': []},
]}
expected = {'adhoc_filters': []}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
def test_merge_extra_filters_adds_unequal_lists(self):
form_data = {
'extra_filters': [
{'col': 'a', 'op': 'in', 'val': ['g1', 'g2', 'g3']},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2', 'c3']},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
{
'clause': 'WHERE',
'comparator': ['g1', 'g2', 'g3'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2', 'c3'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
def test_merge_extra_filters_with_unset_legacy_time_range(self):
"""
Make sure native filter is applied if filter box time range is unset.
"""
form_data = {
"time_range": "Last 10 days",
"extra_filters": [
{"col": "__time_range", "op": "==", "val": NO_TIME_RANGE},
],
"extra_form_data": {"time_range": "Last year"},
}
merge_extra_filters(form_data)
self.assertEqual(
form_data,
{
"time_range": "Last year",
"applied_time_extras": {},
"adhoc_filters": [],
},
)
def filter_values(column: str, default: Optional[str] = None) -> List[str]:
""" Gets a values for a particular filter as a list
This is useful if:
- you want to use a filter box to filter a query where the name of filter box
column doesn't match the one in the select statement
- you want to have the ability for filter inside the main query for speed
purposes
Usage example::
SELECT action, count(*) as times
FROM logs
WHERE action in ( {{ "'" + "','".join(filter_values('action_type')) + "'" }} )
GROUP BY action
:param column: column/filter name to lookup
:param default: default value to return if there's no matching columns
:return: returns a list of filter values
"""
form_data = json.loads(request.form.get("form_data", "{}"))
convert_legacy_filters_into_adhoc(form_data)
merge_extra_filters(form_data)
return_val = [
comparator for filter in form_data.get("adhoc_filters", [])
for comparator in (filter["comparator"] if isinstance(
filter["comparator"], list) else [filter["comparator"]])
if (filter.get("expressionType") == "SIMPLE"
and filter.get("clause") == "WHERE"
and filter.get("subject") == column and filter.get("comparator"))
]
if return_val:
return return_val
if default:
return [default]
return []
def test_merge_extra_filters_with_conflicting_time_ranges(self):
"""
Make sure filter box takes precedence if both native filter and filter box
time ranges are set.
"""
form_data = {
"time_range": "Last 10 days",
"extra_filters": [{"col": "__time_range", "op": "==", "val": "Last week"}],
"extra_form_data": {
"time_range": "Last year",
},
}
merge_extra_filters(form_data)
self.assertEqual(
form_data,
{
"time_range": "Last week",
"applied_time_extras": {"__time_range": "Last week"},
"adhoc_filters": [],
},
)
def test_merge_extra_filters_adds_unequal_lists(self):
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": ["g1", "g2", "g3"]
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2", "c3"]
},
],
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": ["g1", "g2", "g3"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2", "c3"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
def test_merge_extra_filters_ignores_equal_filters(self):
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": "someval"
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2"]
},
{
"col": "c",
"op": "in",
"val": ["c1", 1, None]
},
],
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": ["c1", 1, None],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "c",
},
],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": ["c1", 1, None],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "c",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
def test_merge_extra_filters(self):
# does nothing if no extra filters
form_data = {"A": 1, "B": 2, "c": "test"}
expected = {
**form_data, "adhoc_filters": [],
"applied_time_extras": {}
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# empty extra_filters
form_data = {"A": 1, "B": 2, "c": "test", "extra_filters": []}
expected = {
"A": 1,
"B": 2,
"c": "test",
"adhoc_filters": [],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# copy over extra filters into empty filters
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": "someval"
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2"]
},
]
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# adds extra filters to existing filters
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": "someval"
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2"]
},
],
"adhoc_filters": [{
"clause": "WHERE",
"comparator": ["G1", "g2"],
"expressionType": "SIMPLE",
"operator": "!=",
"subject": "D",
}],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["G1", "g2"],
"expressionType": "SIMPLE",
"operator": "!=",
"subject": "D",
},
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# adds extra filters to existing filters and sets time options
form_data = {
"extra_filters": [
{
"col": "__time_range",
"op": "in",
"val": "1 year ago :"
},
{
"col": "__time_col",
"op": "in",
"val": "birth_year"
},
{
"col": "__time_grain",
"op": "in",
"val": "years"
},
{
"col": "A",
"op": "like",
"val": "hello"
},
{
"col": "__time_origin",
"op": "in",
"val": "now"
},
{
"col": "__granularity",
"op": "in",
"val": "90 seconds"
},
]
}
expected = {
"adhoc_filters": [{
"clause": "WHERE",
"comparator": "hello",
"expressionType": "SIMPLE",
"operator": "like",
"subject": "A",
}],
"time_range":
"1 year ago :",
"granularity_sqla":
"birth_year",
"time_grain_sqla":
"years",
"granularity":
"90 seconds",
"druid_time_origin":
"now",
"applied_time_extras": {
"__time_range": "1 year ago :",
"__time_col": "birth_year",
"__time_grain": "years",
"__time_origin": "now",
"__granularity": "90 seconds",
},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
def test_merge_extra_filters_merges_different_val_types(self):
form_data = {
"extra_filters": [
{"col": "a", "op": "in", "val": ["g1", "g2"]},
{"col": "B", "op": "==", "val": ["c1", "c2"]},
],
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
]
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
form_data = {
"extra_filters": [
{"col": "a", "op": "in", "val": "someval"},
{"col": "B", "op": "==", "val": ["c1", "c2"]},
],
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
]
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
def test_merge_extra_filters_adds_unequal_lists(self):
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": ["g1", "g2", "g3"]
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2", "c3"]
},
],
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": ["g1", "g2", "g3"],
"expressionType": "SIMPLE",
"filterOptionName": "21cbb68af7b17e62b3b2f75e2190bfd7",
"isExtra": True,
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2", "c3"],
"expressionType": "SIMPLE",
"filterOptionName": "0a8dcb928f1f4bba97643c6e68d672f1",
"isExtra": True,
"operator": "==",
"subject": "B",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
def test_merge_extra_filters_merges_different_val_types(self):
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": ["g1", "g2"]
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2"]
},
],
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"filterOptionName": "c11969c994b40a83a4ae7d48ff1ea28e",
"isExtra": True,
"operator": "in",
"subject": "a",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": "someval"
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2"]
},
],
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["g1", "g2"],
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"operator": "==",
"subject": "B",
},
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"filterOptionName": "90cfb3c34852eb3bc741b0cc20053b46",
"isExtra": True,
"operator": "in",
"subject": "a",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
def test_merge_extra_filters(self):
# does nothing if no extra filters
form_data = {"A": 1, "B": 2, "c": "test"}
expected = {
**form_data, "adhoc_filters": [],
"applied_time_extras": {}
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# empty extra_filters
form_data = {"A": 1, "B": 2, "c": "test", "extra_filters": []}
expected = {
"A": 1,
"B": 2,
"c": "test",
"adhoc_filters": [],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# copy over extra filters into empty filters
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": "someval"
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2"]
},
]
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"filterOptionName": "90cfb3c34852eb3bc741b0cc20053b46",
"isExtra": True,
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"filterOptionName": "6c178d069965f1c02640661280415d96",
"isExtra": True,
"operator": "==",
"subject": "B",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# adds extra filters to existing filters
form_data = {
"extra_filters": [
{
"col": "a",
"op": "in",
"val": "someval"
},
{
"col": "B",
"op": "==",
"val": ["c1", "c2"]
},
],
"adhoc_filters": [{
"clause": "WHERE",
"comparator": ["G1", "g2"],
"expressionType": "SIMPLE",
"operator": "!=",
"subject": "D",
}],
}
expected = {
"adhoc_filters": [
{
"clause": "WHERE",
"comparator": ["G1", "g2"],
"expressionType": "SIMPLE",
"operator": "!=",
"subject": "D",
},
{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"filterOptionName": "90cfb3c34852eb3bc741b0cc20053b46",
"isExtra": True,
"operator": "in",
"subject": "a",
},
{
"clause": "WHERE",
"comparator": ["c1", "c2"],
"expressionType": "SIMPLE",
"filterOptionName": "6c178d069965f1c02640661280415d96",
"isExtra": True,
"operator": "==",
"subject": "B",
},
],
"applied_time_extras": {},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
# adds extra filters to existing filters and sets time options
form_data = {
"extra_filters": [
{
"col": "__time_range",
"op": "in",
"val": "1 year ago :"
},
{
"col": "__time_col",
"op": "in",
"val": "birth_year"
},
{
"col": "__time_grain",
"op": "in",
"val": "years"
},
{
"col": "A",
"op": "like",
"val": "hello"
},
{
"col": "__granularity",
"op": "in",
"val": "90 seconds"
},
]
}
expected = {
"adhoc_filters": [{
"clause": "WHERE",
"comparator": "hello",
"expressionType": "SIMPLE",
"filterOptionName": "e3cbdd92a2ae23ca92c6d7fca42e36a6",
"isExtra": True,
"operator": "like",
"subject": "A",
}],
"time_range":
"1 year ago :",
"granularity_sqla":
"birth_year",
"time_grain_sqla":
"years",
"granularity":
"90 seconds",
"applied_time_extras": {
"__time_range": "1 year ago :",
"__time_col": "birth_year",
"__time_grain": "years",
"__granularity": "90 seconds",
},
}
merge_extra_filters(form_data)
self.assertEqual(form_data, expected)
def test_merge_extra_filters_merges_different_val_types(self):
form_data = {
'extra_filters': [
{'col': 'a', 'op': 'in', 'val': ['g1', 'g2']},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
form_data = {
'extra_filters': [
{'col': 'a', 'op': 'in', 'val': 'someval'},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
def test_merge_extra_filters(self):
# does nothing if no extra filters
form_data = {'A': 1, 'B': 2, 'c': 'test'}
expected = {'A': 1, 'B': 2, 'c': 'test'}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# empty extra_filters
form_data = {'A': 1, 'B': 2, 'c': 'test', 'extra_filters': []}
expected = {'A': 1, 'B': 2, 'c': 'test', 'adhoc_filters': []}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# copy over extra filters into empty filters
form_data = {'extra_filters': [
{'col': 'a', 'op': 'in', 'val': 'someval'},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
]}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# adds extra filters to existing filters
form_data = {
'extra_filters': [
{'col': 'a', 'op': 'in', 'val': 'someval'},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['G1', 'g2'],
'expressionType': 'SIMPLE',
'operator': '!=',
'subject': 'D',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['G1', 'g2'],
'expressionType': 'SIMPLE',
'operator': '!=',
'subject': 'D',
},
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# adds extra filters to existing filters and sets time options
form_data = {'extra_filters': [
{'col': '__time_range', 'op': 'in', 'val': '1 year ago :'},
{'col': '__time_col', 'op': 'in', 'val': 'birth_year'},
{'col': '__time_grain', 'op': 'in', 'val': 'years'},
{'col': 'A', 'op': 'like', 'val': 'hello'},
{'col': '__time_origin', 'op': 'in', 'val': 'now'},
{'col': '__granularity', 'op': 'in', 'val': '90 seconds'},
]}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'hello',
'expressionType': 'SIMPLE',
'operator': 'like',
'subject': 'A',
},
],
'time_range': '1 year ago :',
'granularity_sqla': 'birth_year',
'time_grain_sqla': 'years',
'granularity': '90 seconds',
'druid_time_origin': 'now',
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
def test_merge_extra_filters_adds_unequal_lists(self):
form_data = {
'extra_filters': [
{
'col': 'a',
'op': 'in',
'val': ['g1', 'g2', 'g3']
},
{
'col': 'B',
'op': '==',
'val': ['c1', 'c2', 'c3']
},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
{
'clause': 'WHERE',
'comparator': ['g1', 'g2', 'g3'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2', 'c3'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
def get_filters(self,
column: str,
remove_filter: bool = False) -> List[Filter]:
"""Get the filters applied to the given column. In addition
to returning values like the filter_values function
the get_filters function returns the operator specified in the explorer UI.
This is useful if:
- you want to handle more than the IN operator in your SQL clause
- you want to handle generating custom SQL conditions for a filter
- you want to have the ability for filter inside the main query for speed
purposes
Usage example::
WITH RECURSIVE
superiors(employee_id, manager_id, full_name, level, lineage) AS (
SELECT
employee_id,
manager_id,
full_name,
1 as level,
employee_id as lineage
FROM
employees
WHERE
1=1
{# Render a blank line #}
{%- for filter in get_filters('full_name', remove_filter=True) -%}
{%- if filter.get('op') == 'IN' -%}
AND
full_name IN ( {{ "'" + "', '".join(filter.get('val')) + "'" }} )
{%- endif -%}
{%- if filter.get('op') == 'LIKE' -%}
AND
full_name LIKE {{ "'" + filter.get('val') + "'" }}
{%- endif -%}
{%- endfor -%}
UNION ALL
SELECT
e.employee_id,
e.manager_id,
e.full_name,
s.level + 1 as level,
s.lineage
FROM
employees e,
superiors s
WHERE s.manager_id = e.employee_id
)
SELECT
employee_id, manager_id, full_name, level, lineage
FROM
superiors
order by lineage, level
:param column: column/filter name to lookup
:param remove_filter: When set to true, mark the filter as processed,
removing it from the outer query. Useful when a filter should
only apply to the inner query
:return: returns a list of filters
"""
# pylint: disable=import-outside-toplevel
from superset.utils.core import FilterOperator
from superset.views.utils import get_form_data
form_data, _ = get_form_data()
convert_legacy_filters_into_adhoc(form_data)
merge_extra_filters(form_data)
filters: List[Filter] = []
for flt in form_data.get("adhoc_filters", []):
val: Union[Any, List[Any]] = flt.get("comparator")
op: str = flt["operator"].upper() if flt.get("operator") else None
# fltOpName: str = flt.get("filterOptionName")
if (flt.get("expressionType") == "SIMPLE"
and flt.get("clause") == "WHERE"
and flt.get("subject") == column and val):
if remove_filter:
if column not in self.removed_filters:
self.removed_filters.append(column)
if column not in self.applied_filters:
self.applied_filters.append(column)
if op in (
FilterOperator.IN.value,
FilterOperator.NOT_IN.value,
) and not isinstance(val, list):
val = [val]
filters.append({"op": op, "col": column, "val": val})
return filters
def run(self) -> Optional[Dict[str, Any]]:
initial_form_data = {}
if self._permalink_key is not None:
command = GetExplorePermalinkCommand(self._permalink_key)
permalink_value = command.run()
if not permalink_value:
raise ExplorePermalinkGetFailedError()
state = permalink_value["state"]
initial_form_data = state["formData"]
url_params = state.get("urlParams")
if url_params:
initial_form_data["url_params"] = dict(url_params)
elif self._form_data_key:
parameters = FormDataCommandParameters(key=self._form_data_key)
value = GetFormDataCommand(parameters).run()
initial_form_data = json.loads(value) if value else {}
message = None
if not initial_form_data:
if self._slice_id:
initial_form_data["slice_id"] = self._slice_id
if self._form_data_key:
message = _(
"Form data not found in cache, reverting to chart metadata."
)
elif self._dataset_id:
initial_form_data[
"datasource"
] = f"{self._dataset_id}__{self._dataset_type}"
if self._form_data_key:
message = _(
"Form data not found in cache, reverting to dataset metadata."
)
form_data, slc = get_form_data(
use_slice_data=True, initial_form_data=initial_form_data
)
try:
self._dataset_id, self._dataset_type = get_datasource_info(
self._dataset_id, self._dataset_type, form_data
)
except SupersetException:
self._dataset_id = None
# fallback unkonw datasource to table type
self._dataset_type = SqlaTable.type
dataset: Optional[BaseDatasource] = None
if self._dataset_id is not None:
try:
dataset = DatasourceDAO.get_datasource(
db.session, cast(str, self._dataset_type), self._dataset_id
)
except DatasetNotFoundError:
pass
dataset_name = dataset.name if dataset else _("[Missing Dataset]")
if dataset:
if app.config["ENABLE_ACCESS_REQUEST"] and (
not security_manager.can_access_datasource(dataset)
):
message = __(security_manager.get_datasource_access_error_msg(dataset))
raise DatasetAccessDeniedError(
message=message,
dataset_type=self._dataset_type,
dataset_id=self._dataset_id,
)
viz_type = form_data.get("viz_type")
if not viz_type and dataset and dataset.default_endpoint:
raise WrongEndpointError(redirect=dataset.default_endpoint)
form_data["datasource"] = (
str(self._dataset_id) + "__" + cast(str, self._dataset_type)
)
# On explore, merge legacy and extra filters into the form data
utils.convert_legacy_filters_into_adhoc(form_data)
utils.merge_extra_filters(form_data)
dummy_dataset_data: Dict[str, Any] = {
"type": self._dataset_type,
"name": dataset_name,
"columns": [],
"metrics": [],
"database": {"id": 0, "backend": ""},
}
try:
dataset_data = dataset.data if dataset else dummy_dataset_data
except (SupersetException, SQLAlchemyError):
dataset_data = dummy_dataset_data
return {
"dataset": sanitize_datasource_data(dataset_data),
"form_data": form_data,
"slice": slc.data if slc else None,
"message": message,
}
def test_merge_extra_filters(self):
# does nothing if no extra filters
form_data = {'A': 1, 'B': 2, 'c': 'test'}
expected = {'A': 1, 'B': 2, 'c': 'test'}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# empty extra_filters
form_data = {'A': 1, 'B': 2, 'c': 'test', 'extra_filters': []}
expected = {'A': 1, 'B': 2, 'c': 'test', 'adhoc_filters': []}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# copy over extra filters into empty filters
form_data = {'extra_filters': [
{'col': 'a', 'op': 'in', 'val': 'someval'},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
]}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# adds extra filters to existing filters
form_data = {
'extra_filters': [
{'col': 'a', 'op': 'in', 'val': 'someval'},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['G1', 'g2'],
'expressionType': 'SIMPLE',
'operator': '!=',
'subject': 'D',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['G1', 'g2'],
'expressionType': 'SIMPLE',
'operator': '!=',
'subject': 'D',
},
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
# adds extra filters to existing filters and sets time options
form_data = {'extra_filters': [
{'col': '__time_range', 'op': 'in', 'val': '1 year ago :'},
{'col': '__time_col', 'op': 'in', 'val': 'birth_year'},
{'col': '__time_grain', 'op': 'in', 'val': 'years'},
{'col': 'A', 'op': 'like', 'val': 'hello'},
{'col': '__time_origin', 'op': 'in', 'val': 'now'},
{'col': '__granularity', 'op': 'in', 'val': '90 seconds'},
]}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'hello',
'expressionType': 'SIMPLE',
'operator': 'like',
'subject': 'A',
},
],
'time_range': '1 year ago :',
'granularity_sqla': 'birth_year',
'time_grain_sqla': 'years',
'granularity': '90 seconds',
'druid_time_origin': 'now',
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
def test_merge_extra_filters_merges_different_val_types(self):
form_data = {
'extra_filters': [
{'col': 'a', 'op': 'in', 'val': ['g1', 'g2']},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
form_data = {
'extra_filters': [
{'col': 'a', 'op': 'in', 'val': 'someval'},
{'col': 'B', 'op': '==', 'val': ['c1', 'c2']},
],
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': ['g1', 'g2'],
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
{
'clause': 'WHERE',
'comparator': ['c1', 'c2'],
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'B',
},
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
],
}
merge_extra_filters(form_data)
self.assertEquals(form_data, expected)
