def test_latesttitle(self): user = d.table(BlogUser) post1 = d.table(BlogPost) post2 = d.table(BlogPost) # An interesting query that's difficult in the ORM q = (user .leftjoin(post1, post1.user == user.id) .leftjoin(post2, (post2.user == user.id) & (post1.id < post2.id)) .where(post2.user.is_null) .project(user.username, post1.title)) latest = dict(q.all()) # u0 has no posts, so no latest post self.assertEqual(None, latest["u0"]) for i in range(1, 10): self.assertEqual("p%d" % (i - 1), latest["u%d" % i]) # The subquery way of doing the same thing q1 = (post1 .group(post1.user) .project(post1.user, d.max(post1.id).label("post_id")) .subquery) q2 = (user .leftjoin(q1, user.id == q1.user) .leftjoin(post1, post1.id == q1.post_id) .project(user.username, post1.title)) latest2 = dict(q2.all()) self.assertEqual(latest, latest2)
def test_cross(self): t1 = d.table(TestModel1) t2 = d.table(TestModel2) results = t1.crossjoin(t2).project(t1.a, t1.b, t2.c).all() def _count(x): return x.objects.all().count() self.assertEqual( _count(TestModel1) * _count(TestModel2), len(list(results)))
def test_postcount(self): user = d.table(BlogUser) post = d.table(BlogPost) q = (user .leftjoin(post, post.user_id == user.id) .group(user.username) .project(user.username, d.count(post.id).label("posts"))) counts = dict(q.all()) for i in range(10): self.assertEqual(i, counts["u%d" % i])
def test_count(self): t1 = d.table(TestModel1) t2 = d.table(TestModel2) a = t1.project(d.count().label("total")).one() self.assertEqual(2, a.total) b = (t1 .leftjoin(t2, t2.m1_id == t1.id) .group(t1.a) .project(t1.a, d.count().label("count"))) for i in b.all(): self.assertEqual(3, i.count)
def test_m2m(self): t2 = d.table(TestModel2) tm = d.table(TestM2M) tmjoin = d.table(TestM2M.m2s) a = (tm .project(tm.a, t2.c) .join(tmjoin, tmjoin.testm2m == tm.id) .join(t2, tmjoin.testmodel2 == t2.id) .order(t2.c)) l = list(a.all()) self.assertEqual(2, len(l)) self.assertEqual(5, l[0].c) self.assertEqual(6, l[1].c)
def test_simple(self): t2 = d.table(TestModel2) r = t2.where(t2.c > d.const(3)).project(t2.c).all() self.assertEqual(3, len(list(r))) r = t2.where(t2.c > d.const(3) + d.const(1)).project(t2.c).all() self.assertEqual(2, len(list(r)))
def test_order(self): t2 = d.table(TestModel2) r = list(t2.project(t2.c).order(t2.c).all()) self.assertEqual(1, r[0].c) r = list(t2.project(t2.c).order(t2.c.desc).all()) self.assertEqual(6, r[0].c)
def test_derived_table(self): t1 = d.table(TestModel1) t2 = d.table(TestModel2) a = (t2 .project(t2.m1, d.min(t2.c).label("m")) .group(t2.m1) .subquery) b = (t1 .project(t1.a, a.m) .join(a, a.m1 == t1.id) .order(a.m)) r = list(b.all()) self.assertEqual(2, len(r)) self.assertEqual(1, r[0].m) self.assertEqual(4, r[1].m)
def test_agg(self): t1 = d.table(TestModel1) t2 = d.table(TestModel2) total = t2.project(d.sum(t2.c).label("total")).one() self.assertEqual(6 + 5 + 4 + 3 + 2 + 1, total.total) grouped = list( t2 .leftjoin(t1, t1.id == t2.m1) .group(t1.a) .project(t1.a, d.sum(t2.c).label("total")) .order(d.label("total").desc) .all()) self.assertEqual("y", grouped[0].a) self.assertEqual(6 + 5 + 4, grouped[0].total) self.assertEqual("x", grouped[1].a) self.assertEqual(3 + 2 + 1, grouped[1].total)
def test_offset(self): t2 = d.table(TestModel2) test2 = list(t2.order(t2.c).limit(1).offset(1).project(t2.c).all()) self.assertEqual(1, len(test2)) self.assertEqual(2, test2[0].c)
def test_limit(self): t2 = d.table(TestModel2) self.assertEqual(1, len(list(t2.project(t2.c).limit(1).all()))) self.assertEqual(2, len(list(t2.project(t2.c).limit(2).all())))
def test_select_expr(self): t2 = d.table(TestModel2) a = t2.project((d.max(t2.c) - d.const(2)).label("total")).subquery r = t2.project(t2.c).where(t2.c > a).all() self.assertEqual(2, len(list(r)))