def __init__(self, **kw):
super().__init__(**kw)
a = self.select_table
isNumeric = cfn('ISNUMERIC', ['val'])
left = cfn('LEFT', ['val', 'num'])
c, d = pk.Tables('UnitSMR', 'EquipType')
days = fn.DateDiff(PseudoColumn('day'), a.DeliveryDate, fn.CurTimestamp())
remaining = Case().when(days <= 365, 365 - days).else_(0).as_('Remaining')
remaining2 = Case().when(days <= 365 * 2, 365 * 2 - days).else_(0)
ge_remaining = Case().when(isNumeric(left(a.Model, 1)) == 1, remaining2).else_(None).as_('GE_Remaining')
b = c.select(c.Unit, fn.Max(c.SMR).as_('CurrentSMR'), fn.Max(c.DateSMR).as_('DateSMR')).groupby(c.Unit).as_('b')
cols = [a.MineSite, a.Customer, d.EquipClass, a.Model, a.Serial, a.Unit,
b.CurrentSMR, b.DateSMR, a.DeliveryDate, remaining, ge_remaining]
q = Query.from_(a) \
.left_join(b).on_field('Unit') \
.left_join(d).on_field('Model') \
.orderby(a.MineSite, a.Model, a.Unit)
f.set_self(vars())
# NOTE lots of duplication with this pattern btwn avail/ac inspect/units/comp co
# can't remember how everything works and don't want to dig into it
self.stylemap_cols |= {'Model': dict(
cols=['Model'],
func=st.pipe_highlight_alternating,
da=dict(
subset=['Model'],
color='maroon',
theme=self.theme))}
def test_groupby_with_case_uses_the_alias(self):
q = (
Query.from_(self.t)
.select(
fn.Sum(self.t.foo).as_("bar"),
Case()
.when(self.t.fname == "Tom", "It was Tom")
.else_("It was someone else.")
.as_("who_was_it"),
)
.groupby(
Case()
.when(self.t.fname == "Tom", "It was Tom")
.else_("It was someone else.")
.as_("who_was_it")
)
)
self.assertEqual(
'SELECT SUM("foo") "bar",'
"CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
"ELSE 'It was someone else.' END \"who_was_it\" "
'FROM "abc" '
'GROUP BY "who_was_it"',
str(q),
)
def computed_case_function(self) -> Tuple[Case, ParameterValueType]:
parameters = self.param.parameters
case_ = Case()
value_type = None
for param in parameters:
if param.on:
param_joint_parser = ConsoleParameterJointParser(
param.on, self.topic_space_filter)
condition_ = param_joint_parser.parse_parameter_joint()
if condition_:
param_parser = ConsoleParameterParser(
param, self.topic_space_filter,
self.dataset_query_alias, self.dataset_columns)
param_parser_result = param_parser.parse_parameter()
if param_parser_result.result:
then_ = param_parser_result.result
case_ = case_.when(condition_, then_)
value_type = param_parser_result.value_type
else:
param_parser = ConsoleParameterParser(param,
self.topic_space_filter,
self.dataset_query_alias,
self.dataset_columns)
param_parser_result = param_parser.parse_parameter()
if param_parser_result.result:
default_ = param_parser_result.result
case_ = case_.else_(default_)
value_type = param_parser_result.value_type
return case_, value_type
def test_complicated(self):
t = Table("abc")
is_placebo = t.campaign_extra_info == "placebo"
pixel_mobile_search = Case().when(
is_placebo, t.action_fb_pixel_search + t.action_fb_mobile_search)
unique_impressions = Case().when(is_placebo, t.unique_impressions)
v = fn.Sum(pixel_mobile_search) / fn.Sum(
unique_impressions) - 1.96 * fn.Sqrt(
1 / fn.Sum(unique_impressions) * fn.Sum(pixel_mobile_search) /
fn.Sum(unique_impressions) *
(1 - fn.Sum(pixel_mobile_search) / fn.Sum(unique_impressions)))
self.assertTrue(v.is_aggregate)
def test_groupby_with_case_uses_the_alias(self):
q = Query.from_(self.t).select(
fn.Sum(self.t.foo).as_('bar'),
Case().when(self.t.fname == "Tom",
"It was Tom").else_("It was someone else.").
as_('who_was_it')).groupby(Case().when(
self.t.fname == "Tom",
"It was Tom").else_("It was someone else.").as_('who_was_it'))
self.assertEqual(
"SELECT SUM(\"foo\") \"bar\","
"CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
"ELSE 'It was someone else.' END \"who_was_it\" "
"FROM \"abc\" "
"GROUP BY \"who_was_it\"", str(q))
def __init__(self, da=None, **kw):
super().__init__(da=da, **kw)
self.formats.update({
'% Complete': '{:.0%}',
'Hrs': '{:.0f}'})
a, b, c, d = self.a, self.b, self.c, self.d
# rename Complete True = Y, scheduled = S, else N
case_scheduled = Case().when(a.Scheduled == 1, 'S').else_('N')
complete = Case().when(a.Complete == 1, 'Y').else_(case_scheduled).as_('Complete')
cols = [d.MineSite, a.Unit, a.FCNumber, a.Subject, b.Classification, c.Resp, b.Hours,
b.PartNumber, c.PartAvailability, c.Comments, b.ReleaseDate, b.ExpiryDate, complete]
f.set_self(vars())
def test__case__field(self):
q = Q.from_("abc").select(Case().when(F("foo") == 1,
F("bar")).else_(F("buz")))
self.assertEqual(
'SELECT CASE WHEN "foo"=1 THEN "bar" ELSE "buz" END FROM "abc"',
str(q))
def test__case_all_constants_is_aggregate_none(self):
v = Case() \
.when(True, 1) \
.when(False, 2) \
.else_(3)
self.assertIsNone(v.is_aggregate)
def select_metabolites_by_inchi(inchi, filters=None):
"""
Returns a SQL statement that selects metabolites based on their InChI depiction.
Parameters
----------
inchi : str
InChI depiction of a chemical compound.
filters : list of pypika.terms.Criterion
Criteria that the metabolites should also satisfy. Feeds the WHERE statement.
Returns
-------
str
SQL SELECT statement.
"""
if filters is None:
filters = []
# Join 'metabolites' to 'thesaurus' on 'thesaurus.synonym' to account for internal synonymous IDs
# Select 'thesaurus.id' if exists, otherwise 'metabolites.metabolite_id' (meaning no synonym was found)
return (Query.from_(metabolites).left_join(thesaurus).on(
metabolites.metabolite_id == thesaurus.synonym).select(Case().when(
thesaurus.id.notnull(),
thesaurus.id).else_(metabolites.metabolite_id).as_(
"metabolite_id")).distinct().where(
Criterion.all([metabolites.inchi == inchi,
*filters])).get_sql())
def test__case_mixed_constant_is_not_aggregate(self):
v = Case() \
.when(Field('foo') == 1, fn.Sum(Field('bar'))) \
.when(Field('foo') == 2, fn.Sum(Field('fiz'))) \
.else_(1)
self.assertTrue(v.is_aggregate)
def test_case_using_constructor_param(self):
q = Query.from_(self.t).select(
Case(alias='bar').when(self.t.foo == 1, 'a').else_('b'))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END \"bar\" FROM \"abc\"",
str(q))
def __init__(self, da=None, **kw):
super().__init__(da=da, **kw)
a = self.a
date_col = 'ShiftDate'
ct_allopen = a.CategoryAssigned.isnull() | a.SMS.isnull(
) | a.Suncor.isnull()
assigned = Case().when(ct_allopen, 0).else_(1).as_('Assigned')
self.cols.append(assigned)
f.set_self(vars())
# set cols, func, and da for stylemap functions
cols_gradient = ['Total', 'SMS', 'Suncor']
self.stylemap_cols |= {
col: dict(cols=cols_gradient, func=self.background_gradient)
for col in cols_gradient
}
# reapplied to 'Unit' when column filtered with 'filter_assigned'. could also do sorting?
self.stylemap_cols |= {
'Unit':
dict(cols=['Unit'],
func=st.pipe_highlight_alternating,
da=dict(subset=['Unit'], color='maroon', theme=self.theme))
}
def test__agg_case_is_aggregate(self):
v = Case() \
.when(Field('foo') == 1, fn.Sum(Field('bar'))) \
.when(Field('foo') == 2, fn.Sum(Field('fiz'))) \
.else_(fn.Sum(Field('fiz')))
self.assertTrue(v.is_aggregate)
def test__case_with_single_aggregate_field_is_not_aggregate(self):
v = Case() \
.when(Field('foo') == 1, 1) \
.when(fn.Sum(Field('foo')) == 2, 2) \
.else_(3)
self.assertFalse(v.is_aggregate)
def test_ignored_in_field_inside_case(self):
q = Query.from_(self.t).select(Case().when(self.t.foo == 1, "a").else_(
self.t.bar.as_('"buz"')))
self.assertEqual(
'SELECT CASE WHEN "foo"=1 THEN \'a\' ELSE "bar" END FROM "abc"',
str(q))
def test_case_using_constructor_param(self):
q = Query.from_(self.t).select(Case(alias="bar").when(self.t.foo == 1, "a").else_("b"))
self.assertEqual(
'SELECT CASE WHEN "foo"=1 THEN \'a\' ELSE \'b\' END "bar" FROM "abc"',
str(q),
)
def test__case__field(self):
q = Q.from_('abc').select(Case().when(F('foo') == 1,
F('bar')).else_(F('buz')))
self.assertEqual(
'SELECT CASE WHEN \"foo\"=1 THEN \"bar\" ELSE \"buz\" END FROM \"abc\"',
str(q))
def test__case__multi(self):
q = Q.from_('abc').select(Case().when(F('foo') > 0, F('fiz')).when(
F('bar') <= 0, F('buz')).else_(1))
self.assertEqual(
'SELECT CASE WHEN \"foo\">0 THEN \"fiz\" WHEN \"bar\"<=0 THEN \"buz\" ELSE 1 END FROM \"abc\"',
str(q))
def test_ignored_in_field_inside_case(self):
q = Query.from_(self.t).select(Case().when(self.t.foo == 1, 'a').else_(
self.t.bar.as_('"buz"')))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE \"bar\" END FROM \"abc\"",
str(q))
def test__case_all_constants_is_neither_aggr_or_not(self):
v = Case() \
.when(Field('foo') == 1, 1) \
.when(Field('foo') == 2, 2) \
.else_(3)
self.assertIsNone(v.is_aggregate)
def test__case__multi(self):
q = Q.from_("abc").select(Case().when(F("foo") > 0, F("fiz")).when(
F("bar") <= 0, F("buz")).else_(1))
self.assertEqual(
'SELECT CASE WHEN "foo">0 THEN "fiz" WHEN "bar"<=0 THEN "buz" ELSE 1 END FROM "abc"',
str(q),
)
def test_where_with_multiple_wheres_using_or_case(self):
case_stmt = Case().when(self.t.foo == 'bar', 1).else_(0)
query = Query.from_(self.t).select(case_stmt).where(case_stmt | self.t.blah.isin(['test']))
self.assertEqual(
'SELECT CASE WHEN "foo"=\'bar\' THEN 1 ELSE 0 END FROM "abc" WHERE CASE WHEN "foo"=\'bar\' THEN 1 ELSE 0 '
'END OR "blah" IN (\'test\')',
str(query),
)
def _resolve_field_for_model(self, model: "Type[Model]", table: Table, field: str) -> dict:
ret = super()._resolve_field_for_model(model, table, field)
if self.filter:
modifier = QueryModifier()
modifier &= self.filter.resolve(model, {}, {}, model._meta.basetable)
where_criterion, joins, having_criterion = modifier.get_query_modifiers()
ret["field"] = Case().when(where_criterion, ret["field"]).else_(None)
return ret
def set_sql_table_type(self):
self.sql = self.input.replace(self.placeholders.outer[0],
self.table._table_name)
(table, field) = self.sql.split(".")
if not self.alias:
self.alias = self.sql
if self.type == "yesno":
field = Field(field, table=self.table)
self.sql = Case(alias=self.sql).when(field, "yes").else_("no")
else:
self.sql = Field(field, table=self.table, alias=self.sql)
def test_replace_case_table(self):
query = Query \
.from_(self.table_a) \
.select(Case()
.when(self.table_a.fname == "Tom", "It was Tom")
.when(self.table_a.fname == "John", "It was John")
.else_("It was someone else.")
.as_('who_was_it'))
query = query.replace_table(self.table_a, self.table_b)
self.assertEqual('SELECT CASE '
'WHEN "fname"=\'Tom\' THEN \'It was Tom\' '
'WHEN "fname"=\'John\' THEN \'It was John\' '
'ELSE \'It was someone else.\' END "who_was_it" '
'FROM "b"', str(query))
def test_replace_case_table(self):
query = Query.from_(self.table_a).select(Case().when(
self.table_a.fname == "Tom", "It was Tom").when(
self.table_a.fname == "John",
"It was John").else_("It was someone else.").as_("who_was_it"))
query = query.replace_table(self.table_a, self.table_b)
self.assertEqual(
"SELECT CASE "
"WHEN \"fname\"='Tom' THEN 'It was Tom' "
"WHEN \"fname\"='John' THEN 'It was John' "
"ELSE 'It was someone else.' END \"who_was_it\" "
'FROM "b"',
str(query),
)
def test_on_conflict_from_subquery(self):
table_bcd = Table('bcd')
query = (PostgreSQLQuery.into(self.table_abc).insert(
self.table_abc.fname, self.table_abc.lname).select(
table_bcd.fname, table_bcd.lname).from_(table_bcd).on_conflict(
self.table_abc.id, self.table_abc.sub_id).do_update(
self.table_abc.fname, 1).do_update(
self.table_abc.lname, table_bcd.lname).do_update(
self.table_abc.cname,
Case().when(self.table_abc.cname.eq('cname'),
'new_name').else_(
self.table_abc.cname)))
self.assertEqual(
'INSERT INTO "abc" VALUES ("fname","lname") '
'ON CONFLICT ("id", "sub_id") '
'DO UPDATE SET "fname"=1,"lname"="bcd"."lname",'
'"cname"=CASE WHEN "abc"."cname"=\'cname\' THEN \'new_name\' ELSE "abc"."cname" END',
str(query))
def __init__(self, da=None, **kw):
super().__init__(da=da, **kw)
a, b, c, d, e = self.a, self.b, T('ComponentType'), self.d, T(
'ComponentBench')
life_achieved = Case().when(
a.SMR == 0,
None).else_(a.ComponentSMR - e.bench_smr).as_('Life Achieved')
q = self.q \
.inner_join(c).on_field('Floc') \
.left_join(e).on(
(a.Floc == e.floc) &
((b.Model == e.model) | (d.ModelBase == e.model_base) | (d.EquipClass == e.equip_class))) \
.orderby(a.Unit, a.DateAdded, c.Modifier, a.GroupCO)
f.set_self(vars())
self.default_dtypes |= f.dtypes_dict(
'Int64', ['Bench SMR', 'Life Achieved', 'Install SMR'])
def test__case__no_cases(self):
with self.assertRaises(CaseException):
q = Q.from_('abc').select(Case())
str(q)
def test__case__else(self):
q = Q.from_('abc').select(Case().when(F('foo') == 1, 'a').else_('b'))
self.assertEqual(
"SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END FROM \"abc\"",
str(q))
