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))