I am implementing when2meet with typescript and sql. And here is how it should look like:
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!