Issue: order_by with subqueries and UNION fails to fill model
Author: Karel-van-de-Plassche
State: closed
Created: 2017-04-06 11:51:13

I'm trying to find the closest point to a specific value. As per [stackoverflow](http://stackoverflow.com/questions/6102824/is-there-a-postgres-closest-operator) suggestion, I'm using subqueries and a UNION ALL to efficiently find the point. The following SQL works on my setup (PostgreSQL 3.6, adminer 4.2.5):

``` SQL
SELECT * FROM
(
  (SELECT id, binormal_wavevector FROM wavevector WHERE binormal_wavevector >= 0.3 ORDER BY binormal_wavevector LIMIT 1)
  UNION ALL
  (SELECT id, binormal_wavevector FROM wavevector WHERE binormal_wavevector < 0.3 ORDER BY binormal_wavevector DESC LIMIT 1)
) AS res
ORDER BY abs(0.3-binormal_wavevector) LIMIT 1
```

I use the following peewee (2.9.1) to re-create this statement:
```python
def get_closest(column, value):
    model_class = column.model_class
    low_query = (model_class
                 .select(model_class.id, column)
                 .where(column >= value)
                 .order_by(column)
                 .limit(1)
                 )
    high_query = (model_class
                 .select(model_class.id, column)
                 .where(column < value)
                 .order_by(column.desc())
                 .limit(1)
                 )
    join_clause = low_query.union_all(high_query).alias('res')
    query = (Wavevector
             .select(SQL('*'))
             .from_(join_clause)
             .order_by(fn.abs(value - SQL(column.name)))
             .limit(1)
             )
    return query
res = get_closest(Wavevector.binormal_wavevector, 0.3)
```
All seems well:
``` python
In [1]: res.count()
Out[1]: 2
In[2]: res.sql()
Out[2]: ('SELECT * FROM ((SELECT "t1"."id", "t1"."binormal_wavevector" FROM "develop"."wavevector" AS t1 WHERE ("t1"."binormal_wavevector" >= %s) ORDER BY "t1"."binormal_wavevector" LIMIT 1) UNION ALL (SELECT "t2"."id", "t2"."binormal_wavevector" FROM "develop"."wavevector" AS t2 WHERE ("t2"."binormal_wavevector" < %s) ORDER BY "t2"."binormal_wavevector" DESC LIMIT 1)) AS res ORDER BY abs(%s - binormal_wavevector) LIMIT 1',
 [0.3, 0.3, 0.3])
```
But the resulting model is (for lack of a better word) empty:
```python
In [1]: [x.binormal_wavevector for x in query]
Out[1]: [None]
```
The subqueries work:
```python
In [2]: [x.binormal_wavevector for x in high_query]
Out[2]: [0.251516]

In [3]: [x.binormal_wavevector for x in low_query]
Out[3]: [0.314465]


As always, found a solution in #1178 right after posting. Replacing
```
.select(SQL('*'))
```
by
```
.select(getattr(join_clause.c, column.name))
```
fixes this problem


For future dwellers, this works for tables with `ForeignKeyField` or `CompositeKey` as `PrimaryKeyField` too:

```python
def get_closest(column, value):
    model_class = column.model_class
    id = model_class.id.alias('id')
    if model_class._meta.primary_key.__class__ in [PrimaryKeyField, ForeignKeyField]:
        prim_key_names = [model_class._meta.primary_key.db_column]
    else:
        prim_key_names = [x + '_id' for x in model_class._meta.primary_key.field_names]
    prim_key_fields = [getattr(model_class, name) for name in prim_key_names]
    low_query = (model_class
                 .select(*prim_key_fields, column)
                 .where(column >= value)
                 .order_by(column)
                 .limit(1)
                 )
    high_query = (model_class
                 .select(*prim_key_fields, column)
                 .where(column < value)
                 .order_by(column.desc())
                 .limit(1)
                 )
    join_clause = low_query.union_all(high_query).alias('res')

    join_fields = [getattr(join_clause.c, name) for name in prim_key_names]
    query = (model_class
             .select(
                 *join_fields,
                     getattr(join_clause.c, column.name))
             .from_(join_clause)
             .order_by(fn.abs(value - SQL(column.name)))
             .limit(1)
             )
    query_res = query.get()
    res = model_class.select()
    for key in prim_key_fields:
        res = res.where(key == getattr(query_res, key.name))
    return res.get()
```