Example #1
0
    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)
Example #2
0
    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)))