Issue: [QueryBuilder] Missing alias when filtering on a nested subquery
Author: kozlek
State: closed
Created: 2021-07-01 09:08:27

Hi !
I'm using peewee's QueryBuilder API these days and I'm enjoying its powerful capabilities 😃

However, I have a complex use case where I need to produce a `WHERE IN <subquery>` condition ; where `subquery` is itself a subquery (nested subquery).

## Issue

I've tried to recreate a minimal example.

Let's say we have two tables:
- product(product_id, title)
- sales(product_id, price)

Now let's request the title of the 10 best-seller products.
We expect to have the following SQL:

```sql
SELECT "t1"."product_id" AS "product_id", "t1"."title" AS "title"
FROM "product" AS "t1"
WHERE ("t1"."product_id" IN (
    SELECT "t2"."product_id" AS "product_id", "t2"."price__sum" AS "price__sum"
    FROM (
        SELECT "t3"."product_id" AS "product_id", sum("t3"."price") AS "price__sum"
        FROM "sales" AS "t3"
        GROUP BY "t3"."product_id"
    ) AS "t2"
    ORDER BY "t2"."price__sum" DESC
    LIMIT 10
    )
)
```

I'm aware we can write the following query in a totally different way, but I'm working with a complex system that needs to have the query written this way (that's why I'm using the QueryBuilder rather than the ORM).

Using peewee's QueryBuilder API, we'll do something like this:

```python
import peewee

# let's define 2 tables
t_product = peewee.Table("product")
t_sales = peewee.Table("sales")

# create a first subquery that computes the sum of the sales
subquery_best_products = t_sales.select(
    t_sales.c.product_id.alias("product_id"), peewee.fn.sum(t_sales.c.price).alias("price__sum")
).group_by(t_sales.c.product_id)
# subquery from this previous subquery to order_by and limit 10
nested_subquery_best_products = (
    subquery_best_products.select_from(
        subquery_best_products.c.product_id.alias("product_id"),
        subquery_best_products.c.price__sum.alias("price__sum"),
    )
    .order_by(subquery_best_products.c.price__sum.desc())
    .limit(10)
)

# select the title of the 10 best products
query = t_product.select(
    t_product.c.product_id.alias("product_id"), t_product.c.title.alias("title")
).where(t_product.c.product_id.in_(nested_subquery_best_products))
```

This issue is that `query` will compile into the following:

```sql
SELECT "t1"."product_id" AS "product_id", "t1"."title" AS "title"
FROM "product" AS "t1"
WHERE ("t1"."product_id" IN (
    SELECT "t2"."product_id" AS "product_id", "t2"."price__sum" AS "price__sum"
    FROM (
        SELECT "t3"."product_id" AS "product_id", sum("t3"."price") AS "price__sum"
        FROM "sales" AS "t3"
        GROUP BY "t3"."product_id"
    )
    ORDER BY "t2"."price__sum" DESC
    LIMIT 10
    )
)
```

We miss the `t2` alias definition, while it is used in the returning section of the generated SQL !

To ensure that our `nested_subquery_best_products` is correct, we can compile it standalone:

```sql
SELECT "t1"."product_id" AS "product_id", "t1"."price__sum" AS "price__sum" 
FROM (
    SELECT "t2"."product_id" AS "product_id", sum("t2"."price") AS "price__sum" 
    FROM "sales" AS "t2" GROUP BY "t2"."product_id"
) AS "t1" 
ORDER BY "t1"."price__sum" DESC 
LIMIT 10
```

The aliases are a bit different (that's normal), but they are correctly defined.
So the issue might be scoped to the usage of a nested subquery into a where expression.

## Solution (WIP)

I'm digging in peewee's internals and I figured out aliases are applied conditionally depending of the scope of the current context. I guess that when the nested subquery is run in standalone mode, the scope is correctly set to SCOPE_SOURCE, while it is set to SCOPE_NORMAL when compiling where expression.  

I'll be happy to provide a MR if I manage to find a working patch 👌

Thanks in advance for your help 🙏 

Have you tried attaching an alias to your subquery? Here's my code and the output, which looks OK to me:

```python
# Get product IDs of best-selling products.
bp = (Sales
      .select(Sales.c.product_id, fn.SUM(Sales.c.price).alias('price__sum'))
      .group_by(Sales.c.product_id)
      .alias('bp'))  # NB

# Select 10 best-selling products.
top10 = (Select([bp], [bp.c.product_id, bp.c.price__sum])
         .order_by(bp.c.price__sum.desc())
         .limit(10))

prod = (Product
        .select(Product.c.product_id, Product.c.title)
        .where(Product.c.product_id.in_(top10)))

print(prod.sql())
```

Output:

```sql
SELECT "t1"."product_id", "t1"."title" 
FROM "product" AS "t1"
WHERE ("t1"."product_id" IN (
  SELECT "bp"."product_id", "bp"."price__sum" 
  FROM (
    SELECT "t2"."product_id", SUM("t2"."price") AS "price__sum" 
    FROM "sales" AS "t2" 
    GROUP BY "t2"."product_id"
  ) AS "bp" 
  ORDER BY "bp"."price__sum" DESC 
  LIMIT ?)
)
```

Another thing you might try, which seems a bit more idiomatic, would be to use a common table expression for the top 10 products:

```python
top10 = (Sales
         .select(Sales.c.product_id, fn.SUM(Sales.c.price))
         .group_by(Sales.c.product_id)
         .order_by(fn.SUM(Sales.c.price).desc())
         .limit(10)
         .cte('top10', columns=('product_id', 'price__sum')))

query = (Product
         .select(Product.c.id, Product.c.title)
         .join(top10, on=(Product.c.id == top10.c.product_id))
         .with_cte(top10))
```

SQL:

```sql

WITH "top10" ("product_id", "price__sum") AS (
  SELECT "t1"."product_id", SUM("t1"."price") 
  FROM "sales" AS "t1" 
  GROUP BY "t1"."product_id" 
  ORDER BY SUM("t1"."price") DESC 
  LIMIT ?) 
SELECT "t2"."id", "t2"."title" FROM "products" AS "t2" 
INNER JOIN "top10" ON ("t2"."id" = "top10"."product_id")
```

Thanks for your answers 🙂

Attaching aliases seems to work on my simple examples, I'll try to implement that on the complex ones now and I will stick 
with that if it works well 👌
If I manage to find patch, I will still try to fix auto alias attachement as it is very practical in some cases 🙂

I would love to use CTE like I'm used to with Postgres, but here I'm working with a Clickhouse OLAP data warehouse which has a limited support for CTE...
At the time I'm writing these lines, there is no suitable query builder on the python market that supports Clickhouse specificity out of the box. Starting with Peewee's query builder and code some extensions to support Clickhouse flavoured operations (like JOIN USING) was the easiest solution ; way better than restarting a whole query builder from scratch.

Thanks again for your work on Peewee 👏