Subqueries and in Clause

You can reference subqueries within a Djaq query.

v_sub = DQ(Book, "id", name="v_sub").where("name == 'B*'")  # noqa: F841
DQ(Book, "name, price").where("id in '@v_sub'").go()

This evaluates to:

SELECT "books_book"."name", "books_book"."price"
   FROM books_book WHERE "books_book"."id"
   IN (SELECT "books_book"."id" FROM books_book WHERE "books_book"."name" LIKE \'B%\')

Note that user of single quotes and prepending the sub query name with @: '@queryname'

Make sure your subquery returns a single output column.

DQ("Book", "id").where("name == 'B*'", name='dq_sub')
dq = DQ("Book", "name, price").where("id in '@dq_sub'")

As with QuerySets it is nearly always faster to generate a sub query than use an itemised list.

If your subquery has parameters, these need to be supplied to the using query:

DQ("Book", "id", name="dq_sub").where("ilike(name, {spec})")
DQ("Book", "name, price").where("id in '@dq_sub'").context({"spec": "B%"})