Issue: Self-referencing subquery not possible?
Author: shai126
State: closed
Created: 2013-12-03 21:52:54

_(I posted this earlier today as a comment on #156, then realised that as #156 is closed I don't know if that was the right place to post or not. Please feel free to delete whichever one is in the wrong place)_

65118b2c7aa4b6ed671de9d8b951f409cf657d18 works as a fix for the example given in #156, but it seems that this fix is limited to ONLY working if the subquery is selecting from a different table than the outer/main query?

In other words, is there any way to do:

```
SELECT
    g1.*,
    (
        SELECT SUM(g2.something) 
        FROM games AS g2
        WHERE g2.some_field = g1.another_field
    ) AS xxx
FROM games AS g1
```

i.e. subqueries that select from one alias of `games` whilst also referencing the outer (main) alias of `games`. Seems impossible using the subquery support in 65118b2c7aa4b6ed671de9d8b951f409cf657d18 – 

maybe I'm just missing something but it seems no amount or permutation of `.alias()`ing seems to solve this one...

Happy to supply more info if you need.

**Update:** have carried on investigating. Looks like at any given time, an instance of `alias_map` can only hold one of each model class as its keys, which is causing the problem. Perhaps if the `alias_map` could take `ModelAlias` instances as keys as well as `Model`s we could get around this somehow? Or, again, am I just missing something obvious that's already implemented...


If you're referencing the same table multiple times in the query you need to call `Model.alias()`.

``` python
class Game(Model):
    some_fields

G2 = Game.alias()
Game.select(
    Game, 
    G2.select(fn.Sum(G2.something)).where(G2.some_field == Game.another_field).alias('xxx'))
```

I haven't tried the above, so not 100% sure.


I'm pretty sure this is one of the things I tried which didn't work, but
I'm on the road just now so will take another look tomorrow and let you
know for sure - either that it does in fact work or exactly how it fails.
Cheers
On 4 Dec 2013 23:43, "Charles Leifer" notifications@github.com wrote:

> If you're referencing the same table multiple times in the query you need
> to call Model.alias().
> 
> class Game(Model):
>     some_fields
> G2 = Game.alias()Game.select(
>     Game,
>     G2.select(fn.Sum(G2.something)).where(G2.some_field == Game.another_field).alias('xxx'))
> 
> I haven't tried the above, so not 100% sure.
> 
> —
> Reply to this email directly or view it on GitHubhttps://github.com/coleifer/peewee/issues/267#issuecomment-29858012
> .


One quirk of the implementation is that calling `G2.select()` doesn't work as you'd expect. With a bit of experimentation, I came up with this (and added a unit test to document it):

``` python
        UA = User.alias()
        inner = SelectQuery(UA, fn.Sum(UA.id)).where(UA.id == User.id)
        query = User.select(User, inner.alias('xxx'))
        sql, params = query.sql()
        self.assertEqual(
            sql,
            'SELECT t1."id", t1."username", (SELECT Sum(t2."id") FROM "users" AS t2 WHERE (t2."id" = t1."id")) AS xxx FROM "users" AS t1')
```

My previous example should be correct except that instead of `G2.select(...)` you should call `SelectQuery(G2, ...)`.


All should be fixed in c530906878b45d7d2eede616a3cc10238a9118a1

Makes it possible to write:

``` python
UA = User.alias()
inner = UA.select(fn.Sum(UA.id)).where(UA.id == User.id)
query = User.select(User, inner.alias('xxx'))
```