Issue: Subquery (SELECT) in join condition doesn't produce valid SQL
Author: Karel-van-de-Plassche
State: closed
Created: 2018-02-27 09:02:33

I'm  using 96cbe09. In a complicated query I need to join on a subquery result. The smallest reproducible case that gives the same error/SQL problems is:

``` python
query = (Network
         .select()
         .join(PureNetworkParams,
               on=(PureNetworkParams.network_id ==
                   Network.select(Network.id)))
         )   
query.sql()

('SELECT "t1"."id", "t1"."feature_names", "t1"."target_names", "t1"."recipe", "t1"."networks" FROM "develop"."network" AS "t1" INNER JOIN "develop"."purenetworkparams" AS "t2" ON ("t2"."network_id" = (SELECT "t1"."id" FROM "develop"."network" AS "t1") AS "t3")',
 [])

query.get()
ProgrammingError: syntax error at or near "AS"
LINE 1: ...ELECT "t1"."id" FROM "develop"."network" AS "t1") AS "t3") L...
```

When I removed the `AS "T3"` and try in my PostgreSQL GUI, it works perfectly.

PS. There might be an SQL workaround, this is just the query I got working first in raw SQL.
PPS. Might be able to rephrase this with a subquery/alias, but can't get the complicated query to work (yet)

The query example you provided makes no sense to me. Why are you joining `PureNetworkParams` on a sub-select when you are already selecting from the `Network` model? I'd think this is more correct:

```python
query = (Network
         .select()
         .join(PureNetworkParams, on=(PureNetworkParams.network == Network.id)))
```

The reason I'm confused is because in your example, the right-hand-side of your join expression is a subquery that is in no way correlated.

As far as joining on subqueries, there are a handful of tests and I've even added another. Hopefully this clarifies the issue for you:

```python

# Let's get the tweets written by "huey" or "zaizee".

# First let's make a query on the "users" table:
users = (User
         .select(User.id, User.username)
         .where(User.username.in_(['huey', 'zaizee'])))

# Now select from tweet and join the tweet's user_id on the user from the sub-select:
query = (Tweet
         .select(Tweet.content, users.c.username.alias('username'))
         .join(users, on=(Tweet.user == users.c.id)))
```

The above query produces the following SQL:

```sql
SELECT "t1"."content", "t2"."username" AS "username"
FROM "tweet" AS "t1"
INNER JOIN (
  SELECT "t3"."id", "t3"."username"
  FROM "users" AS "t3"
  WHERE ("t3"."username" IN ('huey', 'zaizee'))
) AS "t2" ON ("t1"."user_id" = "t2"."id")
```

I am using the subquery to join a CTE, because I join on the value of an ArrayField. I'm aware that this is an SQL antipattern, and an edge case I'm not sure you'd want to support with Peewee. Let me try your suggestions, and if I fail report back with a better test case showing what I want to do.

A better example would certainly clarify the issue for me. Keep me posted.

Going to close this for now. If you want to share more examples, please leave a comment and I'll re-open the issue.

I noticed the SQL query I was trying to recreate in peewee also didn't give me the correct result. I've since then fixed the issue on my side. Let me use this opportunity to thank you for your amazing package and near-instant support!

For anyone foolish enough to do a recursive self-join on an array field, I've included my solution for future reference:

schema:
``` python
from peewee import *
from playhouse.postgres_ext import *

db = PostgresqlExtDatabase(database='qlknn_test', register_hstore=True)
class BaseModel(Model):
    class Meta:
        database = db

class Network(BaseModel):
    networks = ArrayField(IntegerField, null=True)

    def get_parameter(self):
        name_list = []
        if self.networks is not None:
            for net_id in self.networks:
                name = Network.get_by_id(net_id).get_parameter()
                name_list.append(name)
        else:
            pparam = PureParameters.select(PureParameters.name).join(Network).where(Network.id == self.id).get()
            name_list.append(pparam.name)
        return name_list

class PureParameters(BaseModel):
    network = ForeignKeyField(Network)
    name = TextField()
```
Initialize tables:
``` python
purenet1 = Network.create()
pparam1 = PureParameters.create(network=purenet1, name='alice')
purenet2 = Network.create()
pparam2 = PureParameters.create(network=purenet2, name='bob')
purenet3 = Network.create()
pparam3 = PureParameters.create(network=purenet3, name='eve')

combonet = Network.create(networks=[purenet1.id, purenet2.id])
multinet = Network.create(networks=[combonet.id, purenet3.id])
```
Recursive function solution:
``` python
In [1]: purenet1.get_parameter()
Out[1]: ['alice']

In [2]: combonet.get_parameter()
Out[2]: [['alice'], ['bob']]

In [3]: multinet.get_parameter()
Out[3]: [[['alice'], ['bob']], ['eve']]
```
Flat peewee SQL query solution:
``` python
x = Network.alias()
y = Network.alias()
subx = x.select(x.id, x.networks)
suby = y.select(y.id)

query = (Network.select(Network.id, fn.array_agg(PureParameters.name, coerce=False))
         .join(subx, JOIN.LEFT_OUTER, on=subx.c.id == fn.ANY(Network.networks))
         .join(suby, JOIN.LEFT_OUTER, on=suby.c.id == fn.ANY(subx.c.networks))
         .join(PureParameters, on=((PureParameters.network_id == Network.id) |
                                                   (PureParameters.network_id == subx.c.id) |
                                                   (PureParameters.network_id == suby.c.id)))
         .group_by(Network.id)
         )
```
Scaling is not great, so you should probably think about re-designing part of the database (this one is on production, so one extra join not in the example):
``` SQL
GroupAggregate  (cost=282391.93..283171.43 rows=533 width=36)
  Group Key: network.id
  ->  Sort  (cost=282391.93..282649.54 rows=103045 width=8)
        Sort Key: network.id
        ->  Hash Join  (cost=21.19..273811.93 rows=103045 width=8)
              Hash Cond: (purenetworkparams.id = hyperparameters.pure_network_params_id)
              ->  Nested Loop  (cost=1.32..272403.53 rows=100984 width=8)
                    ->  Nested Loop Left Join  (cost=0.55..18246.05 rows=52406 width=12)
                          ->  Nested Loop Left Join  (cost=0.28..1703.66 rows=5285 width=39)
                                ->  Seq Scan on network  (cost=0.00..19.33 rows=533 width=35)
                                ->  Index Scan using network_pkey on network t2  (cost=0.28..3.06 rows=10 width=35)
                                      Index Cond: (id = ANY (network.networks))
                          ->  Index Only Scan using network_pkey on network t3  (cost=0.28..3.03 rows=10 width=4)
                                Index Cond: (id = ANY (t2.networks))
                    ->  Bitmap Heap Scan on purenetworkparams  (cost=0.77..4.82 rows=3 width=8)
                          Recheck Cond: ((network_id = network.id) OR (network_id = t2.id) OR (network_id = t3.id))
                          ->  BitmapOr  (cost=0.77..0.77 rows=3 width=0)
                                ->  Bitmap Index Scan on purenetworkparams_network_id  (cost=0.00..0.17 rows=1 width=0)
                                      Index Cond: (network_id = network.id)
                                ->  Bitmap Index Scan on purenetworkparams_network_id  (cost=0.00..0.17 rows=1 width=0)
                                      Index Cond: (network_id = t2.id)
                                ->  Bitmap Index Scan on purenetworkparams_network_id  (cost=0.00..0.17 rows=1 width=0)
                                      Index Cond: (network_id = t3.id)
              ->  Hash  (cost=15.50..15.50 rows=350 width=8)
                    ->  Seq Scan on hyperparameters  (cost=0.00..15.50 rows=350 width=8)
```