子查询

// ① 模型（书/作者 m2m，示例数据含 author 表，完整可运行版见工作区）
import { model, prop, dto, dsl } from '@ts-grm/core'
import { sqlClient } from './infra/sql-client'

const Author = model("Author", "id", class {
    id = prop.i64()
    name = prop.embedded({
        firstName: prop.str(50),
        lastName: prop.str(50),
    })
    books = prop.m2m(Book).mappedBy("authors")
})

const Book = model("Book", "id", class {
    id = prop.i64()
    name = prop.str(50)
    edition = prop.i32()
    price = prop.num(10, 2)
    authors = prop.m2m(Author).joinTable({
        name: "book_author_mapping",
        joinThisColumns: ["book_id"],
        joinTargetColumns: ["author_id"],
    })
})

const BookView = dto.view(Book, c => [c.id, c.name])

// ② 标量 IN：作者 Ada 写的书
const rows = await sqlClient.createQuery(Book, (q, book) => {
    q.where(book.id.inSubQuery(
        dsl.subQuery(Book, (q2, b2) => {
            // 从书-作者映射里挑出属于 Ada 的书——SQL 里是 IN (select ...)
            return q2.select(dsl.associationModel(Book, "authors").sourceId)
        })
    ))
    return q.select(book.fetch(BookView))
}).fetchList()

console.log(rows)

q.where(
    dsl.tuple(order.id.x, order.id.y.a).inSubQuery(
        dsl.subQuery(ORDER_ITEM, (q, item) => {
            return q.select(item.orderIdX, item.orderIdYA)   // 列数必须与元组一致
        })
    )
)

// ③ 关联模型子查询 + notExists：没有作者的书（反例：把 = 111 的行代入）
const lonely = await sqlClient.createQuery(Book, (q, book) => {
    q.where(
        dsl.notExists(
            dsl.subQuery(dsl.associationModel(Book, "authors"), (q2, asso) => {
                q2.where(asso.sourceId.eq(book.id))          // 关联回当前书
            })
        )
    )
    return q.select(book.fetch(BookView))
}).fetchList()

console.log(lonely)   // 种子数据：3 本书都有作者 → []