Issue: Subquery referencing outer table query 
Author: jberkel
State: closed
Created: 2016-08-19 16:10:42

Not sure if this is possible with peewee, but how would I construct a query like the following with peewee:

``` sql
SELECT outer_table.id, outer_table.bar, (SELECT .... FROM inner_table
     JOIN ... = outer_table.id
) FROM outer_table 
```

All the examples I've found use subqueries which are joined to the outer query. I want to to it the other way around.


What have you tried? Can you share a bit of Python code to give me a better idea what you're trying to do?


Alright, while retrying the whole thing I actually got it to work as expected, nevermind! 
Not sure what I did differently this time.

This is the model:

``` python
class Foo(Model):

class Bar(Model):
    foo = ForeignKeyField(Foo)

class Category(Model):
    text = TextField()

class BarCategoryLink(Model): 
    bar = ForeignKeyField(Bar)
    category = ForeignKeyField(Category)
```

Now I want to enumerate all foos together with a count of specific category links all their (child) bars have:

The raw query I want to generate looks like this:

``` sql
 SELECT foo.id, (SELECT count(bar_category_links.id) 
                   FROM bar_category_links
                   INNER JOIN bar ON bar.foo_id = foo.id
                                 AND bar.id = bar_category_links.bar_id
                   INNER JOIN categories ON
                         categories.id = bar_category_links.category_id
                         AND categories.text = ?)
```

Notice that the subquery refers to foo.id, defined in the outer query.

I tried the following:

``` python
sub_query = lambda foo: BarCategoryLink.select(fn.Count(BarCategoryLink.id))
     .join(Bar, on= (BarCategoryLink.bar == Bar.id) & (Bar.foo == foo))
     .join(Category, on= (BarCategoryLink.category == Category.id) & (Category.text == ?))

query = Foo.select(Foo.id, sub_query(Foo.id).alias('count'))
```

My problem previously was that the `Bar.foo == foo` condition in the subquery had the wrong table alias and therefore did not execute. I think I assigned the subquery to a variable first, like this:

``` python
sub_q = sub_query(Foo.id)
query = Foo.select(Foo.id, sub_q.alias('count'))
```

Looks like aliases are assigned in order of definition / execution?


I had a feeling that if you tried it you'd notice that it _just worked_.

I'd like to point out that you do not need to make a lambda to simply pass in "Foo.id". Just use `Foo.id` directly in the subquery:

``` python
subq = (BarCategory
        .select(fn.COUNT(BarCategory.id))
        .join(Bar, on=(
            (Bar.foo == Foo.id) & 
            (Bar.id == BarCategory.bar)))
        .switch(BarCategory)
        .join(Category, on=(
            (Category.id == BarCategory.category) &
            (Category.text == 'whatever'))))
query = Foo.select(Foo.id, subq.alias('ct'))
```

The generated SQL appears about right:

``` sql
SELECT "t1"."id", (
    SELECT COUNT("t2"."id") 
    FROM "barcategory" AS t2 
    INNER JOIN "bar" AS t3 ON (("t3"."foo_id" = "t1"."id") AND ("t3"."id" = "t2"."bar_id")) 
    INNER JOIN "category" AS t4 ON (("t4"."id" = "t2"."category_id") AND ("t4"."text" = ?))
) AS ct FROM "foo" AS t1
```

The aliases are defined in the order the Python interpreter parses the expressions.


I might be totally missing something important here, but could you write this query as:

``` python
(Foo
 .select(Foo.id, fn.COUNT(BarCategory.id))
 .join(Bar, JOIN.LEFT_OUTER)
 .join(BarCategory)
 .join(Category)
 .where(Category.text == 'whatever')
 .group_by(Foo.id))
```

Similarly, to break down the counts of Bars-per-Category for each Foo, you can write:

``` python
(Foo
 .select(Foo.id, Category.text, fn.COUNT(BarCategory.id))
 .join(Bar, JOIN.LEFT_OUTER)
 .join(BarCategory)
 .join(Category)
 .where(Category.text == 'whatever')
 .group_by(Foo.id, Category.text))
```


Thanks for your helpful explanations! Yes, I had initially started with  a similar `LEFT JOIN` + `GROUP BY` but I actually need to count more than just one category, and the query got complicated very quickly (several joins + aliases), that's why I decided to move it into a subquery. 

I'll try your suggestion though. Maybe a simple `.where(Category.text << ['category_a', 'category_b' ])` would just work.