Subquery in FROM needs an alias in PostgreSQL

[tonycpsu]

I'd like to do a subquery in the FROM clause, as per 22ce07c . However, with the Postgres backend, these subqueries must have an alias, or you get peewee.ProgrammingError: subquery in FROM must have an alias.

Peewee doesn't seem to add one automatically, and when I try to add one with, e.g.
.select().from_(inner).alias("inner"), I still get the error indicating lack of an alias.

[coleifer]

Can you please share a minimal set of models I can use to reproduce the error?

[tonycpsu]

Sure, here's a simple test script I've cooked up: https://gist.github.com/tonycpsu/10737226

Run it with "sqlite" or "postgres" as arguments to test both backends (Postgres database must exist first.) I get different errors from both.

[coleifer]

It's a little tricky...there are a couple things going on.

• When peewee encounters a "bare" sub-select, it will only select the primary key. This is because commonly you use a sub-select to filter on the primary key. To work around this, you need to manually specify which columns to select in your subquery.
• When peewee builds the outer query, it uses the alias t1, so you need to specify that as the alias for the sub-select.

The following test-case is passing on both sqlite and postgres:

class FromMultiTableTestCase(ModelTestCase):
    requires = [Blog, Comment, User]

    def setUp(self):
        super(FromMultiTableTestCase, self).setUp()

        for u in range(2):
            user = User.create(username='u%s' % u)
            for i in range(3):
                b = Blog.create(user=user, title='b%s-%s' % (u, i))
                for j in range(i):
                    Comment.create(blog=b, comment='c%s-%s' % (i, j))

    def test_from_multi_table(self):
        q = (Blog
             .select(Blog, User)
             .from_(Blog, User)
             .where(
                 (Blog.user == User.id) &
                 (User.username == 'u0'))
             .order_by(Blog.pk)
             .naive())

        qc = len(self.queries())
        blogs = [b.title for b in q]
        self.assertEqual(blogs, ['b0-0', 'b0-1', 'b0-2'])

        usernames = [b.username for b in q]
        self.assertEqual(usernames, ['u0', 'u0', 'u0'])
        self.assertEqual(len(self.queries()) - qc, 1)

    def test_subselect(self):
        inner = User.select(User.username)
        self.assertEqual(
            [u.username for u in inner.order_by(User.username)], ['u0', 'u1'])

        # Have to manually specify the alias as "t1" because the outer query
        # will expect that.
        outer = (User
                 .select(User.username)
                 .from_(inner.alias('t1')))
        sql, params = compiler.generate_select(outer)
        self.assertEqual(sql, (
            'SELECT users."username" FROM '
            '(SELECT users."username" FROM "users" AS users) AS t1'))

        self.assertEqual(
            [u.username for u in outer.order_by(User.username)], ['u0', 'u1'])

[tonycpsu]

I see.... I had tried .from(inner).alias('t1'), but not .from_(inner.alias('t1')). If there's no easy way for the outer query to properly infer/add the alias, then I guess there's no bug here, and I'll just put "t1" or whatever in explicitly. Thanks.

[coleifer]

Yeah, I'm realizing I may need to figure out how to make the outer query aware of the inner query's alias, but that doesn't seem like it'll be all that easy. I definitely understand the approaches I've shown are kind of hacky.

I'll leave this open so I can think on it for a little and see if I can come up w/something better.

[tonycpsu]

OK, while you're at it, I'm noticing that if the "inner" query is of type peewee.CompoundSelect (rather than peewee.SelectQuery), it barfs, with:

peewee.OperationalError: near "SELECT": syntax error

The .sql() of the outer query in such a case yields:

('SELECT * FROM SELECT t2."name" FROM "person" AS t2 UNION SELECT t3."name" FROM "person" AS t3 AS t1', [])

(Note the absence of parens around the "inner" UNION query.)

[tonycpsu]

My test snippet for the above (using the same model as the gist I linked to previously) is:

inner = (Person
         .select(Person.name)
     )

inner2 = ( inner | inner )

print inner2.sql()
print list(inner2)

outer = (Person
         .select(SQL('*'))
         .from_(inner2.alias('t1'))
     )

print outer.sql()
print list(outer)

[coleifer]

Nice catch. Yes, I had to explicitly tell peewee not to put parentheses around CompoundSelect because SQLite wasn't handling them correctly.

[goir]

Using an alias works fine. But as you already stated, the outer query doesn't know the about the alias for the inner query so you can't use values from the inner query for a join. e.g.

subselect = TestModel.select(TestModel.id, TestModel.column1)
final = (TestModel2.select(TestModel.id, TestModel.column1).from_(subselect)
           .join(TestModel3, on=(subselect.id == TestModel3.id)))

problem is the on part which doesn't know about the inner query and its alias.

[coleifer]

This is now "fixed", or at least possible. Here is a test-case exemplifying the new behavior:

    def test_subselect_with_join(self):
        inner = User.select(User.id, User.username).alias('q1')
        outer = (Blog
                 .select(inner.c.id, inner.c.username)
                 .from_(inner)
                 .join(Comment, on=(inner.c.id == Comment.id)))
        sql, params = compiler.generate_select(outer)
        self.assertEqual(sql, (
            'SELECT "q1"."id", "q1"."username" FROM ('
            'SELECT users."id", users."username" FROM "users" AS users) AS q1 '
            'INNER JOIN "comment" AS comment ON ("q1"."id" = comment."id")'))