nestedSelect

Before reaching for a raw Kysely subquery, check whether nestedSelect covers it. It lets a Dream query stand in as a subquery while keeping that query's default scopes — the in-Dream alternative to a hand-rolled Kysely subquery over the raw table.

const userRatingsForRecipesInMenu = user
  .associationQuery('userRecipeRatings', {
    and: { recipeId: menu.associationQuery('recipes').nestedSelect('id') },
  })
  .all()

// Places that have at least one booking. The subquery is a Dream query, so
// Booking's default scopes still apply — a soft-deleted Booking won't surface
// its Place. A hand-rolled Kysely subquery over the bookings table has no such
// filter and would resurface Places whose only booking was soft-deleted.
const bookedPlaces = await Place.where({
  id: Booking.query().nestedSelect('placeId'),
}).all()

Limits

nestedSelect projects a single column and cannot correlate to the outer query. That puts two shapes out of its reach:

a correlated subquery (one that references an outer row)
an aggregate subquery (count/sum/… as the projected value)

For those, drop to toKysely — but keep sourcing the inner table from a scoped Dream query (AssocModel.query().toKysely('select')) rather than a raw db().selectFrom('table'), so the association's own default scopes stay in the compiled SQL.

Limits​

Limits