Issue: Accessing column values using magic .c lookup with Subqueries
Author: oneleaftea
State: closed
Created: 2014-09-01 10:03:39

I ran into a behavior with peewee where it seems the .c lookup for a subquery only works for the **on clause**, but not for the **select()** and **order_by** clauses, as well as subsequent access in a **print** command.

For instance, this works

``` python
joinquery = Salaries.select().order_by(Salaries.salary.desc()).limit('4')
joinquery = joinquery.alias('jq')
query = Employees.select().join(joinquery, on=(Employees.emp_no == joinquery.c.emp_no))
```

But let's say I want access to not only the **emp_no** column in the joinquery, but also a **salary** column, so that I can **order by salary**.

I cannot do:

``` python
**same joinquery and joinquery alias as above***
query = Employees.select(Employees, joinquery.c.salary).join(joinquery, on=\
       (Employees.emp_no == joinquery.c.emp_no)).order_by(joinquery.c.salary.desc()).naive()
```

It returns an error: (1054, "Unknown column 'jq.salary' in 'field list'")
This makes it unavailable for ordering as well as unavailable for passing on the salary column in a print command such as:

``` python
for empvar in query:
    print('    '+empvar.first_name, empvar.last_name, 'making $'+str(empvar.salary))
```

I have tested the above syntax for a situation without a subquery and it worked. But for performance reasons, I wanted to try doing the subquery first, and then joining to make it more flexible to get around MySQL's default query plan which is often slow. But I ran into issues accessing the salary column. Is there something I am missing with the syntax? Thanks so much!


Can you share the generated SQL for the query that is failing?


Hello,
I think I figured out what the issue was:

Here is the python code as originally written:

``` python
joinquery = Salaries.select().order_by(Salaries.salary.desc()).limit(howmany)
joinquery = joinquery.alias('jq')
query = Employees.select(Employees, joinquery.c.salary).join(joinquery, \
        on=(Employees.emp_no == joinquery.c.emp_no)).order_by(joinquery.c.salary.desc()).naive()
```

which generates this sql code:

``` sql
SELECT `t1`.`emp_no`, `t1`.`birth_date`, `t1`.`first_name`, 
   `t1`.`gender`, `t1`.`hire_date`, `t1`.`last_name`, `jq`.`salary` 
FROM `employees` AS t1 
INNER JOIN (
   SELECT `t3`.`emp_no` 
   FROM `salaries` AS t3 
   ORDER BY `t3`.`salary` DESC LIMIT 20
) AS jq ON (`t1`.`emp_no` = `jq`.`emp_no`) 
ORDER BY `jq`.`salary` DESC []
```

From the sql code, it is shown that joinquery only makes t3.emp_no available, despite calling select() when assigning joinquery, which I thought would make all columns available.

In fact, if you do print(joinquery), it shows that all columns of the Salaries model/table selected:

``` sql
SELECT `t1`.`emp_no`, `t1`.`from_date`, `t1`.`salary`, `t1`.`to_date` 
FROM `salaries` AS t1 
ORDER BY `t1`.`salary` DESC LIMIT 20 []
```

But when joining it later on, the only column that is available is the one used by the join condition, which is t1.emp_no.

A solution:

By explicitly doing this:

``` python
joinquery = Salaries.select(Salaries.emp_no, Salaries.salary).order_by(Salaries.salary.desc()).limit(howmany)
```

The needed columns are thus available:

``` sql
SELECT `t1`.`emp_no`, `t1`.`birth_date`, `t1`.`first_name`, 
   `t1`.`gender`, `t1`.`hire_date`, `t1`.`last_name`, `jq`.`salary` 
FROM `employees` AS t1 
INNER JOIN (
   SELECT `t3`.`emp_no`, `t3`.`salary` 
   FROM `salaries` AS t3 
   ORDER BY `t3`.`salary` DESC LIMIT 20
) AS jq ON (`t1`.`emp_no` = `jq`.`emp_no`) 
ORDER BY `jq`.`salary` DESC []
```

This then makes ordering by salary possible as well as making possible subsequent print command of:

``` python
for empvar in query:
    print('    '+empvar.first_name, empvar.last_name, 'making $'+str(empvar.salary))
```

Not sure if this is per design? It seems the original subquery should stay intact when used in the join, to better represent what one would expect when doing raw SQL. That said, being more explicit is never a bad thing and I consider the solution to be an adequate one. 

Thanks!


> Not sure if this is per design? It seems the original subquery should stay intact when used in the join, to better represent what one would expect when doing raw SQL.

Yes, if you are performing a subquery with no explicit selection, peewee will default to only grabbing the primary key.


Ahhh ok, that makes sense. Thanks for the response!