Issue: Does "Model.ForeignKey.id IN subquery" work?
Author: PicoSushi
State: closed
Created: 2019-03-23 12:52:04

I wrote code as below...

```python
import datetime
from peewee import SqliteDatabase, Model, TextField, DateTimeField, ForeignKeyField, IntegerField, JOIN

DATABASE = "example.db"

db = SqliteDatabase(DATABASE)


class BaseModel(Model):
    class Meta:
        legacy_table_names = False
        database = db


class Student(BaseModel):
    name = TextField(index=True, unique=True)


class Lecture(BaseModel):
    name = TextField(index=True, unique=True)
    created = DateTimeField(default=datetime.datetime.now)


class Attend(BaseModel):
    student = ForeignKeyField(Student)
    lecture = ForeignKeyField(Lecture)


def attendances():
    query = Attend.select(
    ).where(Attend.lecture.id << (Lecture.select(Lecture.id).order_by(Lecture.created.desc()).limit(5)))
    print(query.sql())
    return query


def init_db():
    db.connect()
    db.create_tables([Student, Lecture, Attend])


def main():
    init_db()
    for attend in attendances():
        print(attend)


if __name__ == '__main__':
    main()
```

And got output and error as below.
```
('SELECT "t1"."id", "t1"."student_id", "t1"."lecture_id" FROM "attend" AS "t1" WHERE ("t2"."id" IN (SELECT "t2"."id" FROM "lecture" AS "t2" ORDER BY "t2"."created" DESC LIMIT ?))', [5])
Traceback (most recent call last):
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 2714, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: no such column: t2.id

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "m2.py", line 48, in <module>
    main()
  File "m2.py", line 43, in main
    for attend in attendances():
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 6032, in __iter__
    self.execute()
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 1625, in inner
    return method(self, database, *args, **kwargs)
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 1696, in execute
    return self._execute(database)
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 1847, in _execute
    cursor = database.execute(self)
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 2727, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 2721, in execute_sql
    self.commit()
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 2512, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/picosushi/pyenv/py3/lib/python3.7/site-packages/peewee.py", line 2714, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: no such column: t2.id
```

As I think, there's my mistake for my peewee querying. Any help is welcome.

You want 

```python
# Moved to its own line for clarity.
lectures = Lecture.select(Lecture.id).order_by(Lecture.created.desc()).limit(5)
query = (Attend
         .select()
         .where(Attend.lecture.in_(lectures)))
```

If you plan on accessing attributes on either the `Lecture` or the `Student`, you can select them and then you will not incur an additional query to resolve the foreign-key lookup:

```python
lectures = Lecture.select(Lecture.id).order_by(Lecture.created.desc()).limit(5)
query = (Attend
         .select(Attend, Lecture, Student)
         .join_from(Attend, Lecture)
         .join_from(Attend, Student)
         .where(Attend.lecture.in_(lectures)))
```

Since you're joining, you could also just do the join on the subquery!

```python
lectures = Lecture.select(Lecture.id Lecture.name).order_by(Lecture.created.desc()).limit(5)
query = (Attend
         .select(Attend, Student, lectures.c.name)
         .join_from(Attend, Student)
         .join_from(Attend, lectures, on=(Attend.lecture == lecture.c.id))

# This will only execute one query -- the foreign-keys are already loaded.
for attend in query:
    print(attend.student.name, attend.lecture.name)
```

It worked! Thanks for quick and clear answer!