Issue: When joining subquery, the .get() model instance result doesn't contain that attribute.
Author: eric-spitfire
State: closed
Created: 2019-04-01 06:04:30

When joining a subquery into a Model query, I can only retrieve the subquery column via `.dicts` but not `.get`.

I'd like to be able to get the value from the model instance `.get` so as to not flatten the result / foreign key objects that might also be retrieved in the same query into a single dictionary. 

Does this make sense?

Here's an example. (I removed the foreign key joins for brevity.)

```
def select_instances():
    summed_value = (
        B.select(
            B.a_id, fn.SUM(D.value).alias("summed_value")
        )
        .join(D, on=(B.id == D.order_id))
        .group_by(B.a_id)
    ).alias("summed_value")

    return (
        A.select(
            A,
            fn.SUM(C.value).alias("value"),
            summed_value.c.summed_value.alias("summed_value"),
        )
        .join(
            C,
            JOIN.LEFT_OUTER,
            on=(A.id == C.a_id),
        )
        .join(
            summed_value,
            JOIN.LEFT_OUTER,
            on=(A.id == summed_value.c.a_id),
        )
        .group_by(
            A,
            summed_value.c.summed_value,
            summed_value.c.a_id,
        )
    )


instance = select_instances().where(A.id == 1).get()
print (instance.summed_value) # AttributeError

instances = select_instances().where(A.id == 1).dicts()
print(instances) # Can see summed_value

```

Uhh, this example is damn near inscrutable. Can you make another minimal example? Like wtf is A, B, C and D, and how are they related? It seems like C and B have an "a_id", and then D has a "order_id"?

OK, I think I've made an example that is clear.

```python
class User(Base):
    username = TextField()

class Game(Base):
    user = ForeignKeyField(User)
    name = TextField()

class Score(Base):
    game = ForeignKeyField(Game)
    points = IntegerField()
```

To get a list of users and the sum of their points across all games they've played:

```python
# Subquery calculates just the user id and the sum of points.
subq = (Game
        .select(Game.user, fn.SUM(Score.points).alias('total_points'))
        .join(Score, JOIN.LEFT_OUTER)
        .group_by(Game.user))

# Query all users and get their total points.
query = (User
         .select(User.username, subq.c.total_points)
         .join(subq, on=(User.id == subq.c.user_id)))
```

Peewee sees that you're joining on a subquery whose primary model is `Game`. Thus, Peewee assumes we're looking at attributes and values related to the game table. Peewee attempts to reconstruct the model graph when iterating the results, so you would write:

```python
for user in query:
    print(user.username, user.game.total_points)
```

If you don't want Peewee to try and do the model-graph construction, you can simply patch all joined attributes onto the User instance via the "objects()" method:

```python
for user in query.objects():
    print(user.username, user.total_points)
```

Thanks for your time, sorry for the unclear example previously. I've investigated further and discovered if there are two joins, only the model graph for the first join is constructed. See below.

```
class User(Base):
    username = TextField()

class Game(Base):
    user = ForeignKeyField(User)
    name = TextField()

class Score(Base):
    game = ForeignKeyField(Game)
    points = IntegerField()

class ProfilePicture(Base):
    url = TextField()
    user = ForeignKeyField(User)
    
subq = (Game
        .select(Game.user, fn.SUM(Score.points).alias('total_points'))
        .join(Score, JOIN.LEFT_OUTER)
        .group_by(Game.user))

# Query all users and get their total points, and their pictures.
# .profile_picture is present, but .game is not.
query = (User
         .select(User.username, subq.c.total_points)
         .join(ProfilePicture, on=(User.id == ProfilePicture.user_id), attr="profile_picture")
         .join(subq, on=(User.id == subq.c.user_id)))


# Query all users and get their total points, and their pictures.
# .game is present, but .profile_picture is not.
query = (User
        .select(User.username, subq.c.total_points)
        .join(subq, on=(User.id == subq.c.user_id))
        .join(ProfilePicture, on=(User.id == ProfilePicture.user_id), attr="profile_picture"))
```

I'm not sure this is quite right:

```python
# Query all users and get their total points, and their pictures.
# .profile_picture is present, but .game is not.
query = (User
         .select(User.username, subq.c.total_points)
         .join(ProfilePicture, on=(User.id == ProfilePicture.user_id), attr="profile_picture")
         .join(subq, on=(User.id == subq.c.user_id)))
```

You're not actually selecting any columns from the `ProfilePicture` model...

Additionally, Peewee uses join context which has to do with the last-model being joined. You probably want to modify your queries:

```python
query = (User
        .select(User.username, subq.c.total_points)
        .join(subq, on=(User.id == subq.c.user_id))
        .switch(User)
        .join(ProfilePicture, on=(User.id == ProfilePicture.user_id), attr="profile_picture"))
```

Or you could just:

```python
query = (User
        .select(User.username, subq.c.total_points)
        .join_from(User, subq, on=(User.id == subq.c.user_id))
        .join_from(User, ProfilePicture, attr="profile_picture"))
```

I think I'll give the last two approaches a try...