Open
Description
With select().insert(), it's not possible to use subqueries:
await db.user.select("id", { posts: q => q.posts.select("id", "text") }).insert({ name: "Bob" })
// INSERT INTO "user"("name") VALUES ($1) RETURNING "user"."id", COALESCE("posts".r, '[]') "posts" ['Bob']
// Error: missing FROM-clause entry for table "posts"I'm not sure if this is a known limitation or something that could be fixed?
Full reproduction
import process from "node:process"
import { createBaseTable, orchidORM, testTransaction } from "orchid-orm"
const BaseTable = createBaseTable({ snakeCase: true })
class UserTable extends BaseTable {
override readonly table = "user"
override columns = this.setColumns(t => ({
id: t.serial().primaryKey(),
name: t.varchar(),
}))
relations = {
posts: this.hasMany(() => PostTable, {
columns: ["id"],
references: ["userId"],
}),
}
}
class PostTable extends BaseTable {
override readonly table = "post"
override columns = this.setColumns(t => ({
id: t.serial().primaryKey(),
userId: t.integer().foreignKey("user", "id"),
text: t.text(),
}))
}
const db = orchidORM(
{ databaseURL: process.env.DATABASE_URL, log: true },
{
user: UserTable,
post: PostTable,
},
)
await testTransaction.start(db)
await db.$query`
create table "user" (
id serial primary key,
name varchar not null
);
create table "post" (
id serial primary key,
user_id integer not null references "user" (id),
text text not null
);`
// works
await db.user.select("id").insert({ name: "Alice" })
// works
await db.user.select("id", { posts: q => q.posts.select("id", "text") })
// crashes
await db.user.select("id", { posts: q => q.posts.select("id", "text") }).insert({ name: "Bob" })
await testTransaction.close(db)