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)