pudgereyem Posts - Answer Overflow

pudgereyem

•Created by pudgereyem on 1/31/2024 in #help

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

3 replies

Gaming

Programming