Florian Posts - Answer Overflow

Florian

•Created by Florian on 12/22/2024 in #help-and-questions

Use foreign key as primary key

I've implemented multi-table inheritance following your guide: https://www.prisma.io/docs/orm/prisma-schema/data-model/table-inheritance#multi-table-inheritance-mti My question: Is it possible to merge the foreign key and primary key on my child table? It's easier to query if the parent table (User) and child table (Admin) have the same id. But duplicating the ID is not great because it introduces room for mistakes. I tried it earlier and got an error.

model User {
    id String @id // Clerk ID

    [... common user data]

    userType UserType?

    admin   Admin?
    teacher Teacher?
    student Student?
    parent  Parent?

    [...]
}

enum UserType {
    Admin
    Teacher
    Student
    Parent
}

model Admin {
    id String @id // Same Clerk ID as User -> easier to query

    // I would like to use the userId as the primary key and get rid of the duplicate Clerk ID.
    userId String @unique
    user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
    id String @id // Clerk ID

    [... common user data]

    userType UserType?

    admin   Admin?
    teacher Teacher?
    student Student?
    parent  Parent?

    [...]
}

enum UserType {
    Admin
    Teacher
    Student
    Parent
}

model Admin {
    id String @id // Same Clerk ID as User -> easier to query

    // I would like to use the userId as the primary key and get rid of the duplicate Clerk ID.
    userId String @unique
    user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

5 replies

PPrisma

•Created by Florian on 6/29/2024 in #help-and-questions

How do you organize your Prisma.validator schemas?

I have a bunch of predefined select and include schemas. Where do I put them in my project? I need some inspiration.

export function getUserDataSelect(loggedInUserId: string) {
  return Prisma.validator<Prisma.UserSelect>()({
    ...getFollowerInfoSelect(loggedInUserId),
    id: true,
    username: true,
    displayName: true,
    avatarUrl: true,
    bio: true,
    createdAt: true,
    _count: {
      select: {
        posts: true,
        followers: true,
      },
    },
  });
}

export type UserData = Prisma.UserGetPayload<{
  select: ReturnType<typeof getUserDataSelect>;
}>;

export function getUserDataSelect(loggedInUserId: string) {
  return Prisma.validator<Prisma.UserSelect>()({
    ...getFollowerInfoSelect(loggedInUserId),
    id: true,
    username: true,
    displayName: true,
    avatarUrl: true,
    bio: true,
    createdAt: true,
    _count: {
      select: {
        posts: true,
        followers: true,
      },
    },
  });
}

export type UserData = Prisma.UserGetPayload<{
  select: ReturnType<typeof getUserDataSelect>;
}>;

3 replies

PPrisma

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

Tips on designing this recursive database model

18 replies

PPrisma

•Created by Florian on 6/4/2024 in #help-and-questions

Type-safety for dates when returning DB entries as JSON

I used to use Prisma in server components where I don't need to do serialization. Now I need to return DB entries from an API route handler in JSON form. This means, the Date type of the Prisma model is not correct on the client anymore. JSONification turns the Date into a string. What's the correct way to handle this on the client so I have type-safety?

const posts = await prisma.post.findMany({
  include: getPostWithReplyToInclude(user?.id),
  orderBy: {
    createdAt: "desc",
  },
  take: pageSize + 1,
  cursor: cursor ? { id: cursor } : undefined,
});

const nextCursor = posts.length > pageSize ? posts[pageSize].id : null;

const responseBody: PostsPage = {
  posts: posts.slice(0, pageSize),
  nextCursor,
};

return Response.json(responseBody);

const posts = await prisma.post.findMany({
  include: getPostWithReplyToInclude(user?.id),
  orderBy: {
    createdAt: "desc",
  },
  take: pageSize + 1,
  cursor: cursor ? { id: cursor } : undefined,
});

const nextCursor = posts.length > pageSize ? posts[pageSize].id : null;

const responseBody: PostsPage = {
  posts: posts.slice(0, pageSize),
  nextCursor,
};

return Response.json(responseBody);

11 replies

PPrisma

•Created by Florian on 5/24/2024 in #help-and-questions

No index found for fulltext search over relation (Planetscale)

I'm trying to find "favorited companions" combined with the ability to search through them. As you can see in my code, I search inside a relation query. But I get an error that no index could be found. Is this not possible with Planetscale? Searching throug companions directly works. The search index on name + description exists. But I don't know what index I could add to the favorites model to make this work.

const results = await prismadb.favorite.findMany({
  where: {
    userId: user.id,
    ...(query && {
      OR: [
        {
          companion: {
            name: {
              search: `${query}*`,
            },
          },
        },
        {
          companion: {
            description: {
              search: `${query}*`,
            },
          },
        },
      ],
    }),
  },
  include: {
    companion: {
      include: {
        favorites: true,
      },
    },
  },
});

const results = await prismadb.favorite.findMany({
  where: {
    userId: user.id,
    ...(query && {
      OR: [
        {
          companion: {
            name: {
              search: `${query}*`,
            },
          },
        },
        {
          companion: {
            description: {
              search: `${query}*`,
            },
          },
        },
      ],
    }),
  },
  include: {
    companion: {
      include: {
        favorites: true,
      },
    },
  },
});

4 replies

PPrisma

•Created by Florian on 5/19/2024 in #help-and-questions

Prisma.validator with arguments

Is this the correct way to use Prisma validator (to reuse parts of a query) if the query depends on dynamic data?

export function getPostDataInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    user: {
      select: {
        username: true,
        displayName: true,
        avatarUrl: true,
        pinnedPostId: true,
      },
    },
    likes: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    _count: {
      select: {
        likes: true,
      },
    },
  });
}

export type PostData = Prisma.PostGetPayload<{
  include: ReturnType<typeof getPostDataInclude>;
}>;

export function getPostDataInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    user: {
      select: {
        username: true,
        displayName: true,
        avatarUrl: true,
        pinnedPostId: true,
      },
    },
    likes: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    _count: {
      select: {
        likes: true,
      },
    },
  });
}

export type PostData = Prisma.PostGetPayload<{
  include: ReturnType<typeof getPostDataInclude>;
}>;

2 replies

PPrisma

•Created by Florian on 5/14/2024 in #help-and-questions

Counting hashtags in all posts

I want to build a Twitter-link "trending" feature for which I need to find and count the hashtags inside user posts. What Prisma/Postgres feature would I use for this? Full-text search?

2 replies

PPrisma

•Created by Florian on 5/12/2024 in #help-and-questions

Additional processing after fetching data

I want to fetch posts from my database but additional scrape the opengraph info from the first link in the post. What would be a good abstraction or approach to do this for every request on this model?

2 replies

PPrisma

•Created by Florian on 5/11/2024 in #help-and-questions

Extra query into object (GetPayload)

5 replies

Gaming

Programming