class TestQueryBuilderAggregation(TestQueryBuilderBase): def setUp(self): self.builder = QueryBuilder() self.builder.add_table('table_a', 'a') self.builder.add_field('sum(a.f1)', 'fa') self.builder.add_field('a.f2', 'ga') self.builder.add_table('table_b', 'b') self.builder.add_field('count(b.f)', 'fb') self.builder.link_tables('a.id = b.id') self.builder.add_field('sum(a.f1) / count(b.f)', 'fab') self.builder.add_field('concat(a.f2, b.f)', 'cab') self.builder.add_field('0', 'c') def testSelectSumA(self): query = self.builder.select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a''') def testSelectCountB(self): query = self.builder.select(['fb']) self.assertQuery(query, ''' select count(b.f) fb from table_b b''') def testSelectAvgAB(self): query = self.builder.select(['fab']) self.assertQuery(query, ''' select sum(a.f1) / count(b.f) fab from table_a a, table_b b where a.id = b.id''') def testGroupBy(self): query = self.builder.group_by(['ga']).select(['ga', 'fa']) self.assertQuery(query, ''' select a.f2 ga, sum(a.f1) fa from table_a a group by a.f2''') query = self.builder.group_by(['ga']).select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a group by a.f2''') def testGroupByFormula(self): query = self.builder.group_by(['cab']).select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a, table_b b where a.id = b.id group by concat(a.f2, b.f)''') query = self.builder.group_by(['ga', 'cab']).select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a, table_b b where a.id = b.id group by a.f2, concat(a.f2, b.f)''') def testHaving(self): query = self.builder.having('fa > 1').select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a having fa > 1''') query = self.builder.group_by(['ga']).having('fa > 1').select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a group by a.f2 having fa > 1''') def testHavingContainUnselectedAlias(self): with self.assertRaisesRegexp(Exception, "alias 'cab' needs to be selected"): self.builder.having('cab = "abc"').select(['fa']) def testSelectConstant(self): query = self.builder.select(['c']) self.assertQuery(query, 'select 0 c') query = self.builder.select(['fa', 'c']) self.assertQuery(query, ''' select sum(a.f1) fa, 0 c from table_a a''')
class TestQueryBuilder(TestQueryBuilderBase): def setUp(self): # table relationships self.builder = QueryBuilder() self.builder.add_table('table_a', 'a') self.builder.add_field('a.f', 'fa') self.builder.add_table('table_b', 'b') self.builder.link_tables('a.id = b.id') self.builder.add_field('b.f', 'fb') self.builder.add_table('table_c', 'c') self.builder.link_tables('a.id = c.id') self.builder.add_field('c.f', 'fc') self.builder.add_table('table_d', 'd') self.builder.link_tables('c.id = d.id') self.builder.add_field('d.f', 'fd') def testSelectA(self): query = self.builder.select(['fa']) self.assertQuery(query, 'select a.f fa from table_a a') def testSelectB(self): query = self.builder.select(['fb']) self.assertQuery(query, 'select b.f fb from table_b b') def testSelectC(self): query = self.builder.select(['fc']) self.assertQuery(query, 'select c.f fc from table_c c') def testSelectD(self): query = self.builder.select(['fd']) self.assertQuery(query, 'select d.f fd from table_d d') def testSelectAB(self): query = self.builder.select(['fa', 'fb']) self.assertQuery(query, ''' select a.f fa, b.f fb from table_a a, table_b b where a.id = b.id''') def testSelectAC(self): query = self.builder.select(['fa', 'fc']) self.assertQuery(query, ''' select a.f fa, c.f fc from table_a a, table_c c where a.id = c.id''') def testSelectAD(self): query = self.builder.select(['fa', 'fd']) self.assertQuery(query, ''' select a.f fa, d.f fd from table_a a, table_c c, table_d d where a.id = c.id and c.id = d.id''') def testSelectBC(self): query = self.builder.select(['fb', 'fc']) self.assertQuery(query, ''' select b.f fb, c.f fc from table_a a, table_b b, table_c c where a.id = b.id and a.id = c.id''') def testSelectBD(self): query = self.builder.select(['fb', 'fd']) self.assertQuery(query, ''' select b.f fb, d.f fd from table_a a, table_b b, table_c c, table_d d where a.id = b.id and a.id = c.id and c.id = d.id''') def testSelectCD(self): query = self.builder.select(['fc', 'fd']) self.assertQuery(query, ''' select c.f fc, d.f fd from table_c c, table_d d where c.id = d.id''') def testSelectABC(self): query = self.builder.select(['fa', 'fb', 'fc']) self.assertQuery(query, ''' select a.f fa, b.f fb, c.f fc from table_a a, table_b b, table_c c where a.id = b.id and a.id = c.id''') def testSelectABCD(self): query = self.builder.select(['fa', 'fb', 'fc', 'fd']) self.assertQuery(query, ''' select a.f fa, b.f fb, c.f fc, d.f fd from table_a a, table_b b, table_c c, table_d d where a.id = b.id and a.id = c.id and c.id = d.id''') def testSelectAWhereA(self): query = self.builder.where('fa = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = 1''') query = self.builder.where('fa = "a"').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = "a"''') query = self.builder.where('fa = \'a\'').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = \'a\'''') def testSelectAWhereB(self): query = self.builder.where('fb = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a, table_b b where a.id = b.id and b.f = 1''') def testWhereChainable(self): query = self.builder.where('fa = 1').where('fb = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a, table_b b where a.id = b.id and a.f = 1 and b.f = 1''') def testWhereReturnNewInstance(self): self.builder.where('fb = 1').select(['fa']) query = self.builder.where('fa = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = 1''')