Subquery crash in select().insert() #383

IlyaSemenov · 2024-08-27T06:52:51Z

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)

The text was updated successfully, but these errors were encountered:

IlyaSemenov · 2024-09-02T12:03:25Z

This is also not possible for UPDATE type queries.

romeerez · 2024-09-29T18:02:41Z

Yes, it's a known limitation, this is to be done in the future.

No type error because it's meant to be done in the future, and adding type errors in this case won't be simple.

docs

Cannot select relations in create/update/delete returning clause.

It is bypassable by a separate query:

const id = await db.user.get("id").insert({ name: "Bob" })

const userData = await db.user.find(id).select('id', { posts: q => q.posts.select("id", "text") })

IlyaSemenov · 2024-09-30T03:23:45Z

For context, my use case is using the shared helper for create and select. For example, the chat app uses:

export const selectViewChatMessage = db.chatMessage.makeHelper(q =>
  q.select("id", "content", {
    time: "createdAt",
    // TODO replace raw SQL with ORM once https://github.com/romeerez/orchid-orm/issues/383 is fixed.
    username: () => sql<string>`(select username from "user" where id=user_id)`,
    // username: t => t.user.get("username"),
  }),
)

// add message
const newMessage = await selectViewChatMessage(db.chatMessage).create({ chatId, userId, text, content })

// list messages
const lastMessages = await selectViewChatMessage(db.chatMessage).order({ id: "DESC" }).limit(100)

IlyaSemenov mentioned this issue Aug 27, 2024

q => q.get().transform() doesn't actually run transform #384

Closed

romeerez added the enhancement New feature or request label Sep 29, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

Subquery crash in select().insert() #383

Subquery crash in select().insert() #383

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Subquery crash in select().insert() #383

Subquery crash in select().insert() #383

Comments

Uh oh!

Uh oh!

Uh oh!

Uh oh!