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