P
Prisma•8mo ago
Florian

Tips on designing this recursive database model

In my Twitter clone, a post sometimes needs to be displayed by itself, and sometimes together with its parent post. I do this by including the replyToPost in the query. But every post can also be a repost of another post, so both the post and the ´replyToPost need to include a potential repostOfPost. And each post can also contain a quote of another post. So there are 3 of these relationships in my model. To get the data I want, I needed to build this very confusing includes` setup. Is there a better approach than this?
No description
7 Replies
Florian
FlorianOP•8mo ago
function getPostInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    user: {
      select: getUserProfileSelect(loggedInUserId),
    },
    reposts: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    likes: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    bookmarks: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    attachments: true,
    _count: {
      select: {
        likes: true,
        replies: true,
        reposts: true,
        views: true,
      },
    },
  });
}

function getPostWithQuoteInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    ...getPostInclude(loggedInUserId),
    quoteOfPost: { include: getPostInclude(loggedInUserId) },
  });
}

export function getPostWithRepostInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    ...getPostWithQuoteInclude(loggedInUserId),
    repostOfPost: { include: getPostWithQuoteInclude(loggedInUserId) },
  });
}

export function getPostWithReplyToInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    ...getPostWithRepostInclude(loggedInUserId),
    replyToPost: { include: getPostWithRepostInclude(loggedInUserId) },
  });
}
function getPostInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    user: {
      select: getUserProfileSelect(loggedInUserId),
    },
    reposts: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    likes: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    bookmarks: {
      where: {
        userId: loggedInUserId,
      },
      select: {
        userId: true,
      },
    },
    attachments: true,
    _count: {
      select: {
        likes: true,
        replies: true,
        reposts: true,
        views: true,
      },
    },
  });
}

function getPostWithQuoteInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    ...getPostInclude(loggedInUserId),
    quoteOfPost: { include: getPostInclude(loggedInUserId) },
  });
}

export function getPostWithRepostInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    ...getPostWithQuoteInclude(loggedInUserId),
    repostOfPost: { include: getPostWithQuoteInclude(loggedInUserId) },
  });
}

export function getPostWithReplyToInclude(loggedInUserId: string | undefined) {
  return Prisma.validator<Prisma.PostInclude>()({
    ...getPostWithRepostInclude(loggedInUserId),
    replyToPost: { include: getPostWithRepostInclude(loggedInUserId) },
  });
}
Some feeds render parent posts if one exists: 
{posts.map((post) => (
  <Fragment key={post.id}>
    {post.replyToPost && (
      <Post post={post.replyToPost} showReplyIndicatorBottom />
    )}
    <Post post={post} showReplyIndicatorTop={!!post.replyToPost} />
    <hr />
  </Fragment>
))}
{posts.map((post) => (
  <Fragment key={post.id}>
    {post.replyToPost && (
      <Post post={post.replyToPost} showReplyIndicatorBottom />
    )}
    <Post post={post} showReplyIndicatorTop={!!post.replyToPost} />
    <hr />
  </Fragment>
))}
If the post is a repost of another post, the Post.tsx component detects this and decides which post to show: 
const postToDisplay = post.repostOfPost ?? post;
const postToDisplay = post.repostOfPost ?? post;
This all works but the types are really messy
jonfanz
jonfanz•8mo ago
It’s a bit late in my day, but I think one change might make your structure and queries a lot nicer. Posts can be replies to other posts. That you can model by setting a “parent” or “replyTo” on a post. Then, you have to handle normal posts, reposts, and quote posts. In my opinion, it would be a lot easier to model all of these as the same thing. A normal post is a post with text, attachments, etc. A repost is a post with no text attachments, etc, but with a foreign key “repost” pointing to another post. A quote post is both. A post with text, attachments, etc that also references another post via a “repost” field. Does that make sense?
Florian
FlorianOP•8mo ago
Yes but this is almost what I have, isn't it? I have a repostOfId FK. I could get rid of the quoteOfPostId with your suggestions, but would that make the structure a lot easier? In my JS code, the additional distinction helps
jonfanz
jonfanz•8mo ago
If the distinction helps you then I would keep it 🙂 My suggestion was if you’re looking to simplify the queries, then reducing the number of joins you have to do would be the first thing I would look at
Florian
FlorianOP•8mo ago
This will become even more complex if I ever need to add more FKs and query them. I was wondering if my whole DB structure is wrong. It already feels impossible to work with in its current form I'm not a database expert I'll try your suggestion and remove the separation between quotes and reposts. It will only get rid of one FK but maybe that's enough. A related question: Is it correct to create functions like getPostInclude around Prisma.validator? I do that because I need the authenticated user's ID in the query.
jonfanz
jonfanz•8mo ago
If you're using TypeScript, the satisfies operator might be better: https://www.prisma.io/blog/satisfies-operator-ur8ys8ccq7zb But, yes, those functions look right to me
Prisma
How TypeScript 4.9 satisfies Your Prisma Workflows
Learn how TypeScript 4.9''s new satisfies operator can help you write type-safe code with Prisma
Florian
FlorianOP•8mo ago
Thank you for the hint

Did you find this page helpful?