How to use a subquery in where clause in peewee?

Question

I want to translate this to peewee:

SELECT *
FROM posts
WHERE user_id IN
    (SELECT id
     FROM users
     WHERE country_id == 15
     ORDER BY created_at DESC
     LIMIT 20)

My code is:

subquery = User.select(User.id)\
               .where(User.country_id == 15)\
               .order_by(User.created_at.desc())\
               .limit(20)
record = await manager.execute(
Post.select(Post).where(Post.user_id.in_(subquery.c.id))
)

I'm getting error: peewee.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't2.id)' at line 1")

Peewee output sql like:

SELECT `t1`.`id`,
       ...
       `t1`.`uuid`
FROM `posts` AS `t1`
WHERE (`t1`.`user_id` IN `t2`.`id`)

Answer 1

So close!

Post.select(Post).where(Post.user_id.in_(subquery))

You don't use subquery.c.id, unless you want to refer directly to the ID represented in the subquery.

Documented in many places, but here is an example that almost exactly matches yours:

http://docs.peewee-orm.com/en/latest/peewee/example.html#performing-subqueries

Read the docs, in other words.