DT
Drizzle Team•12mo ago
pudgereyem

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
Angelelz•12mo ago
You're looking for db.$with
Angelelz
Angelelz•12mo 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.

Did you find this page helpful?