Prisma•3mo ago

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?

7 Replies

Florian•3mo 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•3mo 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•3mo 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•3mo 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•3mo 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•3mo 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•3mo ago

Thank you for the hint

Gaming

Programming

Tips on designing this recursive database model