How can I save a table of data into sql(pgTable)?

99 views Asked by At

I am implementing when2meet with typescript and sql. And here is how it should look like: enter image description here

It allows every user to click on the timetable to edit their available time.

In my approach, there should be a 2d array that is saved in the sql database that stores every user's available time for every user. But I have no idea how to save it. Here's my code:

import { sql } from "drizzle-orm";
import {
  index,
  integer,
  pgTable,
  serial,
  timestamp,
  unique,
  varchar,
  pgEnum,
} from "drizzle-orm/pg-core";
export const availableTimeTable = pgTable(
  "available_time",
  {
    id: serial("id").primaryKey(),
    userHandle: varchar("user_handle", { length: 50 })
      .notNull()
      .references(() => usersTable.handle, { onDelete: "cascade" }),
    tweetId: integer("tweet_id")
      .notNull()
      .references(() => tweetsTable.id, { onDelete: "cascade" }),
  },
  (table) => ({
    tweetIdIndex: index("tweet_id_index").on(table.tweetId),
    userHandleIndex: index("user_handle_index").on(table.userHandle),
    uniqCombination: unique().on(table.userHandle, table.tweetId),
  }),
);

And here's how I get the data from database:

const availableSubquery = db.$with("available_time_count").as(
    db
      .select({
        tweetId: availableTimeTable.tweetId,
        //here is where I stuck
        availableTime: sql<number[] | null>`count(*)`.mapWith(Number[]).as("available_time"),

      })
      .from(availableTimeTable)
      .where(eq(availableTimeTable.userHandle, handle ?? "")),
  );

Also, I want to default the 2d array into a array I wrote.

How can I do this? I am very new to sql database.

Thanks!

0

There are 0 answers