Issue: Cannot access subquery result through alias [2.10.x -> 3.5.x migration]
Author: rszalski
State: closed
Created: 2018-06-14 19:01:14

Hi,

we are making a heavy use of Peewee and subqueries in our project. Recently we started migrating from Peewee `2.10.0` to `3.5.0` and stumbled upon some subtle issues.

Below is a hopefully clear example of how we use subqieries. I left in all the aliases as well as the `fn`s as they might give you a hint as to what is wrong here.

    class Note():
        text = CharField(null=False)
        notebook = ForeignKeyField(
            Notebook,
            null=False,
            backref='notes',
            on_delete='CASCADE',
            on_update='CASCADE',
        )

    class Meta():
        database = db
        table_name = 'notes'

    class Notebook():
        title = CharField(null=False)

        class Meta():
            database = db
            table_name = 'notebooks'


    notes_subquery = Note.select(
            fn.CONCAT(
                '[',
                fn.GROUP_CONCAT(
                    fn.JSON_OBJECT(
                        'text',
                        Note.text,
                    )
                ),
                ']'
            ).alias('notes'),
        ).alias('notes')



    query = (
        Notebook
        .select(
            Notebook,
            notes_subquery.c.notes.alias('notes'),
        )
        .join(
            notes_subquery,
            JOIN.LEFT_OUTER,
            on=(Notebook.id == notes_subquery.c.notebook_id).alias('note')
        )
    )

    obj = query.get()

If we execute `obj.notes` we get a `<ModelSelect>` object, instead of the string that contains the concatenated JSON (`'[<obj>]'`). This `<ModelSelect>` probably corresponds to the `backref='notes'` argument, which shadows all other attributes on the resulting `obj`. After we remove the `backref` altogether, the resulting object has `notes_set` (probably an implicit backref) but still, _none of our aliases show up_. It's impossible to access contents of the subquery through the resulting `obj`. We tried changing the aliases and removing them.

This worked just fine on `2.10.0`.

We have other examples where previously we would have written something like `obj.jobs` but now have to go through the _lowercased related model name: `obj.job.jobs`. This correctly returns a similar JSON value. This doesn't work for example above.

I've got it working, I believe:

```python
notes_subq = (Note
              .select(Note.notebook, fn.GROUP_CONCAT(
                  fn.JSON_OBJECT('text', Note.text)).alias('notes'))
              .group_by(Note.notebook)
              .alias('subq'))

query = (Notebook
         .select(Notebook, notes_subq.c.notes)
         .join(notes_subq,
               on=(Notebook.id == notes_subq.c.notebook_id).alias('note')))

for obj in query:
    print(obj.title, obj.note.notes)
```

What's going on here? In Peewee 3.x, Peewee attempts to be smarter about putting together a model graph. When you put the "note" alias on the join predicate, that tells peewee where to stick the related `Note` object and it's associated data. So peewee is storing the notebook data on the notebook instance, sees you are joining on a query of Note objects and the join predicate is saying "alias me to note", so it sticks the aggregated json string onto the "notes" attribute of the joined `Note` instance.

If you want to avoid the extra instances and have everything patched onto the Notebook instance, you can modify the code I provided:

```python
for obj in query.objects():
    print(obj.title, obj.notes)
```

In this case the "notes" backref *is* shadowed by the json string.

@coleifer Thanks for the response, it gives valuable context!

So just to be clear in your example you:

1. Aliased the subquery under different name (to avoid confusion?)
1. changed `notes_subquery.c.notes.alias('notes')` to `notes_subquery.c.notes` 

Is this correct? I'm trying to get to the gist of the changes we need to make on our side.

Having done the above in our code I still can't get `obj.note` attribute to be created. It looks like for some weird reason the join alias is being ignored.

Do you know what might cause the attribute from join predicate's alias not to appear?

Or how would PeeWee 3.x behave if I were to omit the alias altogether, i.e. instead of 

    on=(Notebook.id == notes_subq.c.notebook_id).alias('note')

write 

    on=(Notebook.id == notes_subq.c.notebook_id)

Where would the related object be stored?

> Aliased the subquery under different name (to avoid confusion?)

Yeah, it made it less confusing having all these various "notes" aliases running around.

> changed notes_subquery.c.notes.alias('notes') to notes_subquery.c.notes

Yeah, the ".alias('notes')" isn't necessary.

> Having done the above in our code I still can't get obj.note attribute to be created. It looks like for some weird reason the join alias is being ignored.

Well, I used the code example you provided and the example I shared works correctly, so whatever the issue is would have to do with a mismatch between the example you provided and what your actual code is doing.

> Where would the related object be stored?

Without an explicit alias it will still be stored under the "note" (which is the lower-case name of the table being referenced):

For example, it's still available as "note.notes":

```python
query = (Notebook
         .select(Notebook, notes_subq.c.notes)
         .join(notes_subq,
               on=(Notebook.id == notes_subq.c.notebook_id)))

for obj in query:
    print(obj.title, obj.note, obj.note.notes)
```

@coleifer Thanks again for all the answers. You made me revisit the code multiple times and after I started removing joins from a ~500 line query I noticed we missed a `.switch(Notebook)` before the join...

This caused a join to a different table which, coincidently, also had a `Notebook` FK which made it _not_ fail. It, however, did not produce any results and this was probably why the `note` attribute was missing from the resulting object.

TBH I'm not sure how it worked on 2.10.0 but it did. Anyway, I'm sorry for the trouble, I'm closing this. At least I feel more confident writing Peewee queries now :)