Issue: Subquery with joins on complex queries
Author: fbenderick
State: closed
Created: 2018-07-27 07:53:08

Hello, I'm trying to figure out, how to use subquery with joins. Here's data shape I'm trying to get:

```python
# Issue object
{
    "id": 1,
    "magazine": { "id": 1, ... },
    "posts": [ # <= should be sorted by post.stage.sort
        {
            "id": 1, 
            "user": { "id": 1, ... },
            "stage": { "id": 1, "sort" 10, ... }
        },
        ...
    ]
}
```

Here's how I'm trying to do this [according to docs](http://docs.peewee-orm.com/en/latest/peewee/relationships.html#subqueries):

```python
Posts = Post.alias('posts')
        posts = (Posts
                 .select(Post.issue_id, User, Stage)
                 .join(User)
                 .join_from(Posts, Stage)
                 .order_by(+Stage.sort)
                 .alias('posts'))

issue = (Issue
         .select(Issue, Magazine, Post)
         .join(Magazine)
         .join_from(Issue, posts, on=(Issue.id == posts.c.issue_id))
         .where(Issue.id == where['id'])
         .get())
```

And I'm getting invalid SQL:

```
peewee.ProgrammingError: missing FROM-clause entry for table "t3"
LINE 1: ...t1"."magazine_id" = "t2"."id") INNER JOIN (SELECT "t3"."issu...
```
**Another example:** I need to select all issues which have related posts with Post.Stage.slug not in ['completed']

```python
Incomplete = Post.alias()
incomplete = (Incomplete
              .select(Incomplete, Stage)
              .join(Stage)
              .where(Stage.slug.not_in(['completed']))
              .alias('incomplete'))
opened_issues = (Issue
                 .select(Issue, Post)
                 .join(incomplete, on=(Issue.id == incomplete.c.issue_id)))
```

And I'm also getting

```
peewee.ProgrammingError: missing FROM-clause entry for table "t2"
LINE 1: ...."title", "t1"."description", "t1"."magazine_id", "t2"."id",...
```

How should I perform the query to make peewee specify join table properly? It seems I'm missing something obvious but can't figure it out.

```
# pip freeze | grep pee
peewee==3.6.4
```

Thank you.

Is there a reason you're using a subquery? It seems to me that this is totally unnecessary. You can just write this, which is equivalent to what you have:

```python
query = (Issue
        .select(Issue, Magazine, Post, User, Stage)
        .join_from(Issue, Magazine)
        .join_from(Issue, Post, JOIN.LEFT_OUTER)
        .join_from(Post, User)
        .join_from(Post, Stage)
        .where(Issue.id == where['id']))
        .order_by(Stage.sort)
```

Also note that I'm not calling ".get()" with the above, because it will return multiple rows (one for each post associated w/the issue).

Regarding your example, I think the immediate problem may be that you are selecting all the columns of the `Post` model, but that is actually a separate reference from the columns that will be projected from your subquery. Additionally you are referencing "Post.issue_id" but your alias is "Posts":

```python
        posts = (Posts
                 .select(Post.issue_id, User, Stage)
        # Should be:
        posts = (Posts
                 .select(Posts.issue_id, User, Stage)
```

The other issue is you have a call to `.get()`. This will only retrieve a single row of data, so you will not get a list of posts but only a single one.

The thing to remember is if you're trying to fetch multiple posts for a single issue, you need to either do it in 2 queries, or accept that the issue/magazine data will be duplicated for each post. Executing the queries in the db shell may make this more clear / easier to see.

For example, this is the proper way to reference a subquery's columns in the outer query:

```python

# Because you are putting this in a subquery, you need to give all the columns
# you are selecting unique names (to differentiate user "id" from stage "id").
posts = (Post
         .select(Post.issue_id, User.id.alias('user_id'), Stage.id.alias('stage_id'))
         .join(User)
         .join_from(Post, Stage)
         .alias('posts'))

# This query will contain all columns from issue and magazine, plus some of the columns
# that are selected as part of the sub-query. Although you're only requesting a single Issue,
# there will be as many rows as there are posts, which means you need to deduplicate the data.
query = (Issue
         .select(Issue, Magazine, posts.c.issue_id, posts.c.user_id, posts.c.stage_id)
         .join(Magazine)
         .join_from(Issue, posts, on=(Issue.id == posts.c.issue_id))
         .where(Issue.id == where['id']))
```

As a last alternative, I think what you really want is "prefetch()". Check out [the docs on the n+1 problem](http://docs.peewee-orm.com/en/latest/peewee/relationships.html#avoiding-the-n-1-problem).