Drizzle Team•10mo ago

Is there a way I can create a temporary table with custom data which I then can reference?

I'm building a chat application where I have a model threads and activities. I'm also keeping track of what the latest activity a specific user has read. This allows me to calculate the "number of unread messages". I would like to query the number of unread messages per thread in one database query. I'm using Postgres. Below is my code, please note that the .with() method is broken. But I wanted to paint a picture of what I wanted to do.

const threadDatesArray = sortedThreads.map((threadOnUser) => {
  return {
    threadId: threadOnUser.thread.id,
    date:
      threadOnUser.thread.activities[0]?.createdAt ??
      new Date("1990-01-01"),
  };
});

const data = await ctx.db
  .with(
    "thread_dates",
    ctx.db.values(threadDatesArray).as("threadId", "date"),
  )
  .select({
    threads: {
      id: schema.threads.id,
      title: schema.threads.title,
    },
    activities: {
      id: schema.activities.id,
      createdAt: schema.activities.createdAt,
    },
  })
  .from(schema.threads)
  .leftJoin(
    schema.activities,
    and(
      eq(schema.threads.id, schema.activities.threadId),
      gt(
        schema.activities.createdAt,
        sql`(SELECT date FROM thread_dates WHERE threadId = threads.id)`,
      ),
    ),
  )
  .where(
    and(
      inArray(schema.threads.id, threadIds), // Filter by threadIds
    ),
  );

const threadDatesArray = sortedThreads.map((threadOnUser) => {
  return {
    threadId: threadOnUser.thread.id,
    date:
      threadOnUser.thread.activities[0]?.createdAt ??
      new Date("1990-01-01"),
  };
});

const data = await ctx.db
  .with(
    "thread_dates",
    ctx.db.values(threadDatesArray).as("threadId", "date"),
  )
  .select({
    threads: {
      id: schema.threads.id,
      title: schema.threads.title,
    },
    activities: {
      id: schema.activities.id,
      createdAt: schema.activities.createdAt,
    },
  })
  .from(schema.threads)
  .leftJoin(
    schema.activities,
    and(
      eq(schema.threads.id, schema.activities.threadId),
      gt(
        schema.activities.createdAt,
        sql`(SELECT date FROM thread_dates WHERE threadId = threads.id)`,
      ),
    ),
  )
  .where(
    and(
      inArray(schema.threads.id, threadIds), // Filter by threadIds
    ),
  );

Thanks in advance, Victor

2 Replies

Angelelz•10mo ago

You're looking for db.$with

Angelelz•10mo ago

https://orm.drizzle.team/docs/select#with-clause

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

Is there a way I can create a temporary table with custom data which I then can reference?