Anas Badran Posts - Answer Overflow

Anas Badran

Explore posts from servers

RReactiflux PPrisma DTDrizzle Team

Posts Comments

DTDrizzle Team

•Created by Anas Badran on 2/5/2025 in #help

Schema review

Is there anything that can be optimized about the schema below:

1 replies

DTDrizzle Team

•Created by Anas Badran on 2/5/2025 in #help

Search on user's fullname

I have a user's table that stores first, middle and last names, I want to create another field named search that can be automatically calculated from these three fields. I saw something on the docs about something called full-text search, but I don't think that is is needed for my case, I just to be able to find users based on any part of their name provided. I am not a postgres expert, so I will appreciate any advice if I am wrong.

5 replies

DTDrizzle Team

•Created by Anas Badran on 1/23/2025 in #help

Database design

I'm building an app for a center 'school' for Quran teaching and memorization. I want to track students daily progress and attendees, there is also can monthly tests, each class also can choose best student of the week each week. My question is: how do I link these data to a certain year? For example an admin visits the app dashboard and want to view a student's average attendance for a certain year, or maybe he wants to know how the had progressed in a certain year.

1 replies

PPrisma

•Created by Anas Badran on 1/22/2025 in #help-and-questions

Multiple roles for a user

I have a scenario where a user can have multiple roles at the same time. For example a user can be editor and translator. What is the recommended approach for this situation?

6 replies

DTDrizzle Team

•Created by Anas Badran on 12/14/2024 in #help

Build a complex query

the schema:

import {
  pgTable,
  text,
  varchar,
  jsonb,
  timestamp,
  integer,
  boolean,
  pgEnum,
  primaryKey,
  serial,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { postExtensionEnum } from './enums';
import { $collection } from './collection';

export const $post = pgTable('posts', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  title: text('title').notNull(),
  title_: jsonb('title_'),
  content: text('content').notNull(),
  content_: jsonb('content_'),
  collectionId: integer('collection_id')
    .notNull()
    .references(() => $collection.id, { onDelete: 'cascade' }),
  extension: postExtensionEnum('extension').default('BASE').notNull(),
  order: integer('order'),
  deletedAt: timestamp('deleted_at'),
});

export const $postExtraData = pgTable(
  'post_extra_data',
  {
    key: text('key').notNull(),
    value: text('value').notNull(),
    postId: integer('post_id')
      .notNull()
      .references(() => $post.id, { onDelete: 'cascade' }),
  },
  (table) => [
    {
      pk: primaryKey({
        name: 'post_data',
        columns: [table.key, table.value, table.postId],
      }),
    },
  ]
);

export const $postMetadata = pgTable('post_metadata', {
  id: serial('id').primaryKey(),
  postId: integer('post_id')
    .unique()
    .notNull()
    .references(() => $post.id, { onDelete: 'cascade' }),
  views: integer('views').default(0).notNull(),
  isFeatured: boolean('is_featured').default(false).notNull(),
  publishedAt: timestamp('published_at'),
  archivedAt: timestamp('archived_at'),
});

import {
  pgTable,
  text,
  varchar,
  jsonb,
  timestamp,
  integer,
  boolean,
  pgEnum,
  primaryKey,
  serial,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { postExtensionEnum } from './enums';
import { $collection } from './collection';

export const $post = pgTable('posts', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  title: text('title').notNull(),
  title_: jsonb('title_'),
  content: text('content').notNull(),
  content_: jsonb('content_'),
  collectionId: integer('collection_id')
    .notNull()
    .references(() => $collection.id, { onDelete: 'cascade' }),
  extension: postExtensionEnum('extension').default('BASE').notNull(),
  order: integer('order'),
  deletedAt: timestamp('deleted_at'),
});

export const $postExtraData = pgTable(
  'post_extra_data',
  {
    key: text('key').notNull(),
    value: text('value').notNull(),
    postId: integer('post_id')
      .notNull()
      .references(() => $post.id, { onDelete: 'cascade' }),
  },
  (table) => [
    {
      pk: primaryKey({
        name: 'post_data',
        columns: [table.key, table.value, table.postId],
      }),
    },
  ]
);

export const $postMetadata = pgTable('post_metadata', {
  id: serial('id').primaryKey(),
  postId: integer('post_id')
    .unique()
    .notNull()
    .references(() => $post.id, { onDelete: 'cascade' }),
  views: integer('views').default(0).notNull(),
  isFeatured: boolean('is_featured').default(false).notNull(),
  publishedAt: timestamp('published_at'),
  archivedAt: timestamp('archived_at'),
});

4 replies

PPrisma

•Created by Anas Badran on 12/11/2024 in #help-and-questions

Fix a query

is there something wrong with this query, it isn't working?

  await prisma.collection.findFirst({
      where: {
        id: collectionId,
      },
      include: {
        posts: {
          take: consts.ITEMS_PER_PAGE,
          skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
          include: {
            collection: true,
          },
          where: {
            AND: [
              ...(surah
                ? [
                    {
                      extraData: {
                        path: ['surah'],
                        equals: surah,
                      },
                    },
                  ]
                : []),
              ...(verse
                ? [
                    {
                      extraData: {
                        path: ['verse'],
                        equals: verse,
                      },
                    },
                  ]
                : []),
            ],
          },
        },
      },
    })

  await prisma.collection.findFirst({
      where: {
        id: collectionId,
      },
      include: {
        posts: {
          take: consts.ITEMS_PER_PAGE,
          skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
          include: {
            collection: true,
          },
          where: {
            AND: [
              ...(surah
                ? [
                    {
                      extraData: {
                        path: ['surah'],
                        equals: surah,
                      },
                    },
                  ]
                : []),
              ...(verse
                ? [
                    {
                      extraData: {
                        path: ['verse'],
                        equals: verse,
                      },
                    },
                  ]
                : []),
            ],
          },
        },
      },
    })

26 replies

PPrisma

•Created by Anas Badran on 12/6/2024 in #help-and-questions

Enforce uniqueness on Json fields

How can I enforce uniqueness on the post model based on the values in the json fields. here is the schema:

model Post {
  id           String        @id @default(cuid())
  title        String
  content      String
  collectionId String
  collection   Collection    @relation(fields: [collectionId], references: [id])
  metadata     PostMetadata? @relation(name: "PostMetadata")
  extraData    Json?         @db.JsonB
  tags         Tag[]         @relation(name: "PostTags")
}

model Post {
  id           String        @id @default(cuid())
  title        String
  content      String
  collectionId String
  collection   Collection    @relation(fields: [collectionId], references: [id])
  metadata     PostMetadata? @relation(name: "PostMetadata")
  extraData    Json?         @db.JsonB
  tags         Tag[]         @relation(name: "PostTags")
}

the extraData fields json will look like:

{
surah: string:
verse: number
}

{
surah: string:
verse: number
}

I don't want two records with same values to exist, like if there is record with these in the json field surah: "24", verse: 30, I don't want another to be created with same values

2 replies

PPrisma

•Created by Anas Badran on 11/23/2024 in #help-and-questions

Fix schema error

I have this schema:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Collection {
  id          String       @id @default(cuid())
  title       String
  parentId    String
  parent      Collection   @relation(fields: [parentId], references: [id], name: "SubSeries")
  children    Collection[] @relation(name: "SubSeries")
  description String
  posts       Post[]
  comments    Comment[]
  tags        Tag[]        @relation(name: "CollectionTags")

  @@map("Series")
}

model Post {
  id             String       @id @default(cuid())
  title          String
  Content        String
  seriesId       String
  series         Collection   @relation(fields: [seriesId], references: [id])
  metadata       PostMetadata @relation(name: "PostMetadata")
  extraData      Json         @db.JsonB
  tags           Tag[]        @relation(name: "PostTags")
}

model Tag {
  id          String       @id @default(cuid())
  name        String       @unique @db.VarChar(100)
  posts       Post[]       @relation(name: "PostTags")
  collections Collection[] @relation(name: "CollectionTags")
}


model PostMetadata {
  id          String   @id @default(cuid())
  post        Post     @relation(name: "PostMetadata",fields: [postId], references: [id])
  postId      String   @unique
  views       Int      @default(0)
  isFeatured  Boolean  @default(false)
  publishedAt DateTime?
  archivedAt  DateTime?
}

model Comment {
  id       String     @id @default(cuid())
  content  String
  seriesId String
  series   Collection @relation(fields: [seriesId], references: [id])
}

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Collection {
  id          String       @id @default(cuid())
  title       String
  parentId    String
  parent      Collection   @relation(fields: [parentId], references: [id], name: "SubSeries")
  children    Collection[] @relation(name: "SubSeries")
  description String
  posts       Post[]
  comments    Comment[]
  tags        Tag[]        @relation(name: "CollectionTags")

  @@map("Series")
}

model Post {
  id             String       @id @default(cuid())
  title          String
  Content        String
  seriesId       String
  series         Collection   @relation(fields: [seriesId], references: [id])
  metadata       PostMetadata @relation(name: "PostMetadata")
  extraData      Json         @db.JsonB
  tags           Tag[]        @relation(name: "PostTags")
}

model Tag {
  id          String       @id @default(cuid())
  name        String       @unique @db.VarChar(100)
  posts       Post[]       @relation(name: "PostTags")
  collections Collection[] @relation(name: "CollectionTags")
}


model PostMetadata {
  id          String   @id @default(cuid())
  post        Post     @relation(name: "PostMetadata",fields: [postId], references: [id])
  postId      String   @unique
  views       Int      @default(0)
  isFeatured  Boolean  @default(false)
  publishedAt DateTime?
  archivedAt  DateTime?
}

model Comment {
  id       String     @id @default(cuid())
  content  String
  seriesId String
  series   Collection @relation(fields: [seriesId], references: [id])
}

7 replies

DTDrizzle Team

•Created by Anas Badran on 11/21/2024 in #help

Schema building

I want to archive the content for a scholar who writes a series of posts related to a single subject, this series will have different shape for it's posts than other serieses. for example a series of posts have a verseId, chapterId, point_1, point_2, point_3. another series posts may look like this: characterName, content. And there will be a serieses created in the future I need some help and ideas on how to achieve that.

2 replies

PPrisma

•Created by Anas Badran on 11/21/2024 in #help-and-questions

Building the schema

8 replies

PPrisma

•Created by Anas Badran on 11/15/2024 in #help-and-questions

help with relations

I want the Meal model to kepp track of the absent familyMembers without having to store the data on the familyMember model.

3 replies

DTDrizzle Team

•Created by Anas Badran on 10/7/2024 in #help

I need some help for solving help post posted in another help post.

here it is: https://discord.com/channels/1043890932593987624/1292422444002443314

1 replies

DTDrizzle Team

•Created by Anas Badran on 10/6/2024 in #help

find chat based on users IDs

I want help writing the query to find the chat between two users knowing their IDs. the schema:

export const directChats = pgTable('direct_chats', {
  id: uuid('id').primaryKey().defaultRandom(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  lastMessageAt: timestamp('last_message_at', { withTimezone: true }),
});

export const chatMembers = pgTable('chat_members', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  chatID: uuid('chat_id')
    .references(() => chats.id, { onDelete: 'cascade' })
    .notNull(),
  joinedAt: timestamp('joined_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  leftAt: timestamp('left_at', { withTimezone: true }),
  userID: integer('user_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  role: ChatMemberRole('role').default('member').notNull(),
  nickname: varchar('nickname', { length: 50 }),
  isBanned: boolean('is_banned').default(false).notNull(),
  isMuted: boolean('is_muted').default(false).notNull(),
});

export const directChatMembers = pgTable(
  'direct_chat_members',
  {
    chatID: uuid('chat_id')
      .references(() => directChats.id)
      .notNull(),

    userID: integer('user_id')
      .references(() => users.id, { onDelete: 'cascade' })
      .notNull(),

    createdAt: timestamp('created_at', { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.chatID, t.userID] }),
  })
);

export const directChats = pgTable('direct_chats', {
  id: uuid('id').primaryKey().defaultRandom(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  lastMessageAt: timestamp('last_message_at', { withTimezone: true }),
});

export const chatMembers = pgTable('chat_members', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  chatID: uuid('chat_id')
    .references(() => chats.id, { onDelete: 'cascade' })
    .notNull(),
  joinedAt: timestamp('joined_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  leftAt: timestamp('left_at', { withTimezone: true }),
  userID: integer('user_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  role: ChatMemberRole('role').default('member').notNull(),
  nickname: varchar('nickname', { length: 50 }),
  isBanned: boolean('is_banned').default(false).notNull(),
  isMuted: boolean('is_muted').default(false).notNull(),
});

export const directChatMembers = pgTable(
  'direct_chat_members',
  {
    chatID: uuid('chat_id')
      .references(() => directChats.id)
      .notNull(),

    userID: integer('user_id')
      .references(() => users.id, { onDelete: 'cascade' })
      .notNull(),

    createdAt: timestamp('created_at', { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.chatID, t.userID] }),
  })
);

2 replies

DTDrizzle Team

•Created by Anas Badran on 9/20/2024 in #help

query based on deep relations

I need help modifying the query code to properly query the chat where it has the the two members.

export const findOrCreateChat = async (fromID: number, toID: number) => {
  const existingchat = await db.query.chats.findFirst({
    with: { members: true },
     where(fields, { and, eq }) {
      // the line below needs fix.
      return and(eq(chatMembers.userID, fromID), eq(chatMembers.userID, toID));
     },

  });
  if (existingchat) {
    return existingchat;
  }

  const newChat = await db
    .insert(chats)
    .values({
      name: `new chat`,
    })
    .returning();

  await db.insert(chatMembers).values([
    { chatID: newChat[0].id, userID: fromID },
    { chatID: newChat[0].id, userID: toID },
  ]);

  return newChat[0];
};

export const findOrCreateChat = async (fromID: number, toID: number) => {
  const existingchat = await db.query.chats.findFirst({
    with: { members: true },
     where(fields, { and, eq }) {
      // the line below needs fix.
      return and(eq(chatMembers.userID, fromID), eq(chatMembers.userID, toID));
     },

  });
  if (existingchat) {
    return existingchat;
  }

  const newChat = await db
    .insert(chats)
    .values({
      name: `new chat`,
    })
    .returning();

  await db.insert(chatMembers).values([
    { chatID: newChat[0].id, userID: fromID },
    { chatID: newChat[0].id, userID: toID },
  ]);

  return newChat[0];
};

3 replies

DTDrizzle Team

•Created by Anas Badran on 9/19/2024 in #help

Need help building up a query based on nested fields.

I need some help to query the chat that has only the two members only private chat based on the schema below.

8 replies

Gaming

Programming