Issue: ORDER BY a json field fails in CTE select_from
Author: tuukkamustonen
State: closed
Created: 2018-08-02 06:24:45

```python
class Foo(Model):
    data = BinaryJSONField()

# OK: Produces SELECT ... ORDER BY ("foo"."data"->>'x');
Foo.select().order_by(Foo.data['x'])

# NOK: Produces WITH foos AS (...) SELECT ... ORDER BY ("foos"."data" = 'x');
cte = Foo.select().cte('foos')
query = cte.select_from().order_by(cte.c.data['x'])
```

Ouch...that's going to be rather difficult to fix, unfortunately, perhaps not possible. I'd suggest in the meantime trying something like:

```python
.order_by(Expression(cte.c.data, '->>', SQL("'x'")))
```

I'll try that, but I now realize that `select_from()` actually produces `SELECT  FROM` rather than `SELECT * FROM`. If I explicitly specify columns, as in `select_from(cte.c.id, cte.c.data)` then peewee produces `SELECT "foos"."id", "foos"."data" FROM` as it should. Is it requirement for `select_from()` to always explicitly specify columns?

Docs do state that:

> One or more columns to select from the CTE

Would making `.select_from()` to grab all (available) columns possible (so behavior would be similar to your standard `.select()`)?

Seems like subqueries are a bit special, too:

```python
subq = Foo.select()
query = Foo.select().from_(subq)
query.sql()  # SELECT "t1"."id", "t1"."data" FROM (SELECT "t1"."id" FROM "node" AS "t1") AS "t2";
```

So without columns specified:

- Subquery only selects pk (`"t1"."id"`), and
- Without columns explicitly specified, outer query tries to grab subquery's fields (`"t1"."id", "t1"."data"`) instead of its' own (`"t2"`)

If I explicitly specify columns in both subquery and outer query, then it works.

Also, `order_by()` is similarly broken with subquery (like with CTE) in that it produces non-JSON field compatible SQL. I believe subqueries and CTEs share the same code...(?)

> Is it requirement for select_from() to always explicitly specify columns?

At present, yes, since CTEs may be declared with or without columns.

Same problem and same limitations apply to referencing columns from a subselect in the outer-select, that is: peewee does not do introspection and so you need to use the ".c" helper to reference them in both cases.

As to the first bullet-point, subqueries will only select the primary key, unless you explicitly declare the selection. This is because the most common use-case for subqueries is "where(id IN ...)".

Unfortunately, I think this is gonna have to be "wontfix" for the foreseeable future. When referencing columns on subqueries/ctes, peewee just doesn't have deep enough magic to resolve references to whatever field/expression they correspond to. Feel free to take a look into the code, especially the `ColumnFactory`/`_DynamicColumn` classes, which provide the implementation for the ".c" helper.

Ok, it's a bit more work but not a blocker.

How about making the API fail/warn/hint when user tries to do something bad, though? E.g.:

> - Without columns explicitly specified [using CTE], outer query tries to grab subquery's fields (`"t1"."id", "t1"."data"`) instead of its' own (`"t2"`)

> [CTE] `select_from()` actually produces `SELECT FROM` rather than `SELECT * FROM` [have to explicitly specify all fields]

Also, maybe add "Subqueries" section to http://docs.peewee-orm.com/en/latest/peewee/querying.html and mention that by default (when you don't specify anything) they only select primary key?

Finally, maybe http://docs.peewee-orm.com/en/latest/peewee/querying.html?highlight=subquery#common-table-expressions could mention that peewee doesn't _have deep enough magic to resolve references to whatever field/expression they correspond to_ so you have to be explicit?

Good suggestions, thank you.