def test_deeply_nested_dimension_filter_with_sets(self):
field_alias = 'text'
fltr = ds.fields.text.like(
fn.Concat(
fn.Upper(fn.Trim(fn.Concat('%ab', ds.fields.number))),
ds.fields.aggr_number,
fn.Concat(ds.fields.date.between('date1', 'date2'), 'c%'),
))
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).dimension(
ds.fields[field_alias]).filter(
f.ResultSet(fltr, set_label='set_A',
complement_label='set_B')).sql)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
f"CASE WHEN {fltr} THEN 'set_A' ELSE 'set_B' END \"${field_alias}\","
'SUM("number") "$aggr_number" '
'FROM "test" '
f"GROUP BY \"${field_alias}\" "
f"ORDER BY \"${field_alias}\" "
"LIMIT 200000",
str(queries[0]),
)
def test_deeply_nested_dimension_filter_with_sets_for_data_blending(self):
field_alias = 'state'
fltr = mock_dataset_blender.fields.state.like(
fn.Concat(
fn.Upper(fn.Trim(fn.Concat('%ab', mock_dataset_blender.fields['candidate-id']))),
mock_dataset_blender.fields.winner,
fn.Concat(mock_dataset_blender.fields.timestamp.between('date1', 'date2'), 'c%'),
)
)
queries = (
mock_dataset_blender.query.widget(f.Pandas(mock_dataset_blender.fields['candidate-spend']))
.dimension(mock_dataset_blender.fields[field_alias])
.filter(f.ResultSet(fltr, set_label='set_A', complement_label='set_B'))
.sql
)
self.assertEqual(len(queries), 1)
self.assertEqual(
"SELECT "
f'"sq0"."${field_alias}" "${field_alias}",'
'"sq0"."$candidate-spend" "$candidate-spend" '
'FROM ('
'SELECT '
f'CASE WHEN {fltr} THEN \'set_A\' ELSE \'set_B\' END "${field_alias}",'
'SUM("candidate_spend") "$candidate-spend" '
'FROM "politics"."politician_spend" '
f'GROUP BY "${field_alias}"'
f') "sq0" '
f"ORDER BY \"${field_alias}\" "
"LIMIT 200000",
str(queries[0]),
)
def mysql_insensitive_contains(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).like(
functions.Upper("%{}%".format(value)))
def insensitive_starts_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper("{}%".format(value)))
def mysql_insensitive_exact(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).eq(
functions.Upper("{}".format(value)))
def insensitive_ends_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper(f"%{value}")
)
def insensitive_starts_with(field, value):
return functions\
.Upper(functions.Cast(field, SqlTypes.VARCHAR))\
.like(functions.Upper(f"{value}%"))
def insensitive_contains(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper('%{}%'.format(value))
)
def mysql_insensitive_ends_with(field: Term, value: str) -> Criterion:
return Like(
functions.Upper(functions.Cast(field, SqlTypes.CHAR)),
functions.Upper(StrWrapper(f"%{escape_like(value)}")),
escape="",
)
def mysql_insensitive_exact(field: Term, value: str) -> Criterion:
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).eq(functions.Upper(str(value)))
def mysql_insensitive_ends_with(field: Field, value: str) -> Criterion:
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).like(
functions.Upper(f"%{value}"))
def mysql_insensitive_exact(field: Field, value: str) -> Criterion:
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).eq(
functions.Upper(f"{value}"))
def mysql_insensitive_starts_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).like(
functions.Upper(f"{value}%"))
def mysql_insensitive_starts_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).like(
functions.Upper('{}%'.format(value)))
def mysql_insensitive_ends_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).like(
functions.Upper("%{}".format(value)))
def insensitive_ends_with(field, param=None, value=None, **kwargs):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper(param))
def insensitive_exact(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).eq(functions.Upper(f"{value}"))
def insensitive_ends_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper('%{}'.format(value))
)
def insensitive_contains(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper(f"%{value}%")
)